LookUp Logic
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 96
- Joined: Mon May 14, 2012 1:30 pm
LookUp Logic
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.
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.
Thanks,
Ankur Saxena
Ankur Saxena
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 96
- Joined: Mon May 14, 2012 1:30 pm
-
- Participant
- Posts: 96
- Joined: Mon May 14, 2012 1:30 pm
-
- Premium Member
- Posts: 1735
- Joined: Thu Mar 01, 2007 5:44 am
- Location: Troy, MI
-
- Participant
- Posts: 152
- Joined: Mon Mar 07, 2011 6:16 am
-
- Participant
- Posts: 96
- Joined: Mon May 14, 2012 1:30 pm
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.
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.
Thanks,
Ankur Saxena
Ankur Saxena