DSXchange

I have a requirement for which i have to pass a reporting date, one of the field in the source file and i have to pull all the matched records from the reference table for which reporting date is in between start date and end date of the reference table .
Please find the query below

select sk,id, r_d, c_c
from a, reference_table b
where
a.c_c = b.code
and a.r_d between b.start_date and nvl(b.end_date,sysdate)

Please let me know if some has implemented similar logic

Thanks

hi,

If you were using Datastage Ver8.0.1 then you could have made use of "Range Lookup" functionality. Otherwise, you need to do this conventional way. i.e. in the Transformer stage using stage variables you need to loop through and find whether "Reporting Date" falls between "Reference Start Date" and "Reference End Date".

Thanks & regards
Sudhindra P S

Thank you very much Sudhindra
We are using DS 7.5 and i have checked it is not possible to use the Lookup stage directly. Appreciate greatly if you could explain little more on the conventional way using stage variables

Sudhindra_ps wrote:hi,

If you were using Datastage Ver8.0.1 then you could have made use of "Range Lookup" functionality. Otherwise, you need to do this conventional way. i.e. in the Transformer stage using stage variables you need to loop through and find whether "Reporting Date" falls between "Reference Start Date" and "Reference End Date".

Thanks & regards
Sudhindra P S

Range lookup option is not available in the Data stage prior to V 8.0. In the prior version you can add a transformer after the lookup stage and in the transformer you can verify the condition to check the condition that reference date is in between start date and end date. Dont forget to get the start date and end date extracted from the reference table while doing the lookup.

I am not sure but you can try with the filter stage also after the lookup stage instead of transformer.

Thanks,
Birendra

Range lookup option is not available in the Data stage prior to V 8.0. In the prior version you can add a transformer after the lookup stage and in the transformer you can verify the condition to check the condition that reference date is in between start date and end date. Dont forget to get the start date and end date extracted from the reference table while doing the lookup.

I am not sure but you can try with the filter stage also after the lookup stage instead of transformer.

Thanks,
Birendra

In versions prior to 8.0 Range lookup is possible using a sparse lookup only.

hi divine,

Design your ETL job in the following way.

Source System --> Join Stage -->TransformerStage -->FilterStage -->Target System.

(1) Perform "Innerjoin/LeftOuter Join(As per your need)" on "Source input stream" and "reference stream" using join stage based on your key column(a.c_c = b.code) .
(2) Now, on the Transformer stage input link you would get all rows from source system appended with b.start_date and b.end_date. In the Transformer stage perfrom inner sort on Key column(a.c_c). And create set of stage variables to loop through each record and check if a.r_d(Source Date) falls between b.start_date and b.end_date. Based on this condition you could create a flag(NewColumn value) as either "True/False".
(3) In the filter stage you can filter the records based on "flag(True/False)" to load data into your Target system.

Thanks & regards
Sudhindra P S

Thank you very much.
We have used the same design except for slight change

Source System --> Join Stage -->FilterStage -->Target System.

In FilterStage we put the condition Reporting Date >= START_DATE and R_D <= END_DATE

Sudhindra_ps wrote:hi divine,

Design your ETL job in the following way.

Source System --> Join Stage -->TransformerStage -->FilterStage -->Target System.

(1) Perform "Innerjoin/LeftOuter Join(As per your need)" on "Source input stream" and "reference stream" using join stage based on your key column(a.c_c = b.code) .
(2) Now, on the Transformer stage input link you would get all rows from source system appended with b.start_date and b.end_date. In the Transformer stage perfrom inner sort on Key column(a.c_c). And create set of stage variables to loop through each record and check if a.r_d(Source Date) falls between b.start_date and b.end_date. Based on this condition you could create a flag(NewColumn value) as either "True/False".
(3) In the filter stage you can filter the records based on "flag(True/False)" to load data into your Target system.

Thanks & regards
Sudhindra P S