Datastage Job Design

[adityavarma]

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?

[priyadarshikunal]

why not join/Lookup with filter?

edit :join/Lookup instead of join filter

[venkatdev]

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 ..

[adityavarma]

why not join/filter with filter?

Thanks for your quick reply Kunal. It worked for me.

[adityavarma]

Thanks for your reply Venkat. How can find the MAX(REF_DT) before join?

[nitkuar]

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

[ray.wurlod]

U can find the max(REF_DT) befor joing based on the key column ..

U may not wish to do so.

The second person personal pronoun in English is spelled "you". U is one of our posters (it's a fairly common Burmese name).