sparse lookup-join

dnat · Post by **dnat** » Fri Jun 17, 2011 2:09 am

Hi,

We have used sparse lookup in a job where the condition in the oracle stage is select value from table where lowsc<=orchestrate.sc and highsc>=orchestrate.sc.
So, i pass the field "value" to the output, based on the field sc from input .

Now, the job is failing with heap allocation error, and the ulimit is set to unlimited.

So, the only way is to change this condition to join.

Can anyone suggest, how we can do this in join, as this concept is conditional lookup.

Thanks!

chulett · Post by **chulett** » Fri Jun 17, 2011 7:37 am

It's not a conditional lookup, it's a range lookup. Have you tried using that specific option?

dnat · Post by **dnat** » Mon Jun 20, 2011 12:32 am

I dont think we have range lookup in 7.5.1?

ray.wurlod · Post by **ray.wurlod** » Mon Jun 20, 2011 1:49 am

You don't (have range lookup in version 7.5). Craig's not working on DataStage at the moment.

Join is only ever equijoin, so you won't achieve your requirement that way.

Would you consider using a server job, where you can use user-defined SQL in the database stage that services the reference input to a Transformer stage?

chulett · Post by **chulett** » Mon Jun 20, 2011 7:31 am

D'oh! Where's a [facepalm] icon when you need one?

Note to self - need to stop assuming everyone has made it to some flavor of 8 by now.