LookUp Logic

[ankursaxena.2003]

Hi,

I need help with getting some logic. Below are my Source and LookUp tables. I want to do a look up on Col1, Col2 and for Col3 I want to get the max value but less than source.

For the below example I should get Col4=102.

Source
Col1 | Col2 | Col3
101 | 1 | 5

LookUp
Col1 | Col2 | Col3 | Col4
101 | 1 | 3 | 101
101 | 1 | 4 | 102
101 | 1 | 6 | 103

Any help is appreciated.

[ray.wurlod]

Perhaps a range lookup where the range is between 0 and source value?

[ankursaxena.2003]

Ray,

I am doing the same thing but the issue is that I have a look up. It picks up any value randomly even though the LookUp data is sorted. Some times it takes Col4=101 and sometime Col4=102 as Lookup uses entire partioning.

But, I want to get next max number i.e. Col4=102.

[chulett]

Is the job running on more than 1 node?

[ankursaxena.2003]

No. I am running it on one node.

[priyadarshikunal]

you may need to do some pre-processing on lookup data to get the next value in another column. May be you can use the stage variable to derive the column while the data is sorted in descending order.

[soumya5891]

If both source records and lookup records are coming from tables then you can perform the whole operation in the query level while extracting from the source.

[ankursaxena.2003]

Craig - I think that since Look Up uses Entire Partitioning. It takes any record even though the job runs on single node and the look up data is sorted.

Kunal - I didn't try your suggestion but I think it looks a lot of work.

Soumya - Both the source and lookup record are in different databases. So I cannot do it at query level.

Thanks guys for your help.
I finally did it in Server Job using Oracle Enterprise and a transformer.

But, still I am curious how it can be done in Parallel job. So let me know if anyone of you have solution for this.