Hi,
I need to design a datastage job for the below mentioned SQL.
Select col1, col2
From dm_LIM
Where (code, REF_DT) in (Select code, max(REF_DT)
From dm_LIM LIM, dm_syu
Where LIM.code= SYU.code And LIM.REF_DT <= SYU.REF_DT)
I am not sure which Stage I should use to implement the logic for (Where LIM.code= SYU.code And LIM.REF_DT <= SYU.REF_DT)
Can you please help me with this?
Datastage Job Design
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 104
- Joined: Thu Jul 12, 2007 11:32 pm
- Location: Canada
-
- Premium Member
- Posts: 1735
- Joined: Thu Mar 01, 2007 5:44 am
- Location: Troy, MI
why not join/Lookup with filter?
edit :join/Lookup instead of join filter
edit :join/Lookup instead of join filter
Last edited by priyadarshikunal on Tue Aug 10, 2010 5:15 am, edited 1 time in total.
Priyadarshi Kunal
Genius may have its limitations, but stupidity is not thus handicapped.
Genius may have its limitations, but stupidity is not thus handicapped.
Select col1, col2
From dm_LIM
Where (code, REF_DT) in (Select code, max(REF_DT)
From dm_LIM LIM, dm_syu
Where LIM.code= SYU.code And LIM.REF_DT <= SYU.REF_DT)
I am not sure which Stage I should use to implement the logic for (Where LIM.code= SYU.code And LIM.REF_DT <= SYU.REF_DT)
Here dm_LIM is ur driving table and dm_syu is ur reference table ..Join both these tables based on the LIM.code= SYU.code and extract the required columns , then in transformer stage u file with LIM.REF_DT <= SYU.REF_DT.
Note: U can find the max(REF_DT) befor joing based on the key column ..
From dm_LIM
Where (code, REF_DT) in (Select code, max(REF_DT)
From dm_LIM LIM, dm_syu
Where LIM.code= SYU.code And LIM.REF_DT <= SYU.REF_DT)
I am not sure which Stage I should use to implement the logic for (Where LIM.code= SYU.code And LIM.REF_DT <= SYU.REF_DT)
Here dm_LIM is ur driving table and dm_syu is ur reference table ..Join both these tables based on the LIM.code= SYU.code and extract the required columns , then in transformer stage u file with LIM.REF_DT <= SYU.REF_DT.
Note: U can find the max(REF_DT) befor joing based on the key column ..
Venkat
-
- Premium Member
- Posts: 104
- Joined: Thu Jul 12, 2007 11:32 pm
- Location: Canada
-
- Premium Member
- Posts: 104
- Joined: Thu Jul 12, 2007 11:32 pm
- Location: Canada
Hi Aditya,
I guess, you are trying to find the max value of date column after the join operation. This is what, being depicted by SQL query as well.
Use Rdup stage after filter stage with data sorted on Date column key as secondory key(business key being primary, previous join key in your case).
Regards
Nitin
I guess, you are trying to find the max value of date column after the join operation. This is what, being depicted by SQL query as well.
Use Rdup stage after filter stage with data sorted on Date column key as secondory key(business key being primary, previous join key in your case).
Regards
Nitin
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
U may not wish to do so.venkatdev wrote:U can find the max(REF_DT) befor joing based on the key column ..
The second person personal pronoun in English is spelled "you". U is one of our posters (it's a fairly common Burmese name).
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.