Non Equi joins

suman27 · Post by **suman27** » Fri Aug 19, 2011 6:47 pm

Hi
Is there any way to do non equi join in data stage parallel job?

Regards,
Suman.

ray.wurlod · Post by **ray.wurlod** » Fri Aug 19, 2011 8:52 pm

Not using a Join stage.

If the two sources are in the same database, then you can have DataStage push the join query into that database.

Some solution, but not a general solution, might be possible via the range lookup mechanism.

soumya5891 · Post by **soumya5891** » Sat Aug 20, 2011 11:38 am

Let say two record set A and B
A: ID VAL IDENTITY(Nullable)
1 20 1
2 90 1
3 30 1

B: ID NME
1 AA
3 MM
Now left outer join A with B A as left link.If IDENTITY is NOT NULL means match found and if IDENTITY IS NULL means match found

ray.wurlod · Post by **ray.wurlod** » Sat Aug 20, 2011 4:18 pm

No it doesn't. Because there is also the possibility that a match was found but IDENTITY is null in data set A. The proper test for not found is that B.ID is null. However, none of that has anything at all to do with non equi joins.

priyadarshikunal · Post by **priyadarshikunal** » Sun Aug 21, 2011 7:02 am

What about dumping the matched records and capturing the rejects for further processing in Lookup or Merge stage??

soumya5891 · Post by **soumya5891** » Sun Aug 21, 2011 11:36 am

In this case IDENTITY is user defined column can be created in a transformer stage.So in that case for every incoming record assign it's value to 1.Then in spite of being a not null column this field will not contain any null value.