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
adding condition in Lookup Stage
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 149
- Joined: Fri Oct 15, 2004 12:13 am
- Location: Toronto,divine_auro@yahoo.co.in
- Contact:
adding condition in Lookup Stage
With Regards
Biswajit
Biswajit
-
- Participant
- Posts: 45
- Joined: Thu Aug 31, 2006 3:13 am
- Location: Bangalore
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
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
-
- Premium Member
- Posts: 149
- Joined: Fri Oct 15, 2004 12:13 am
- Location: Toronto,divine_auro@yahoo.co.in
- Contact:
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
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
With Regards
Biswajit
Biswajit
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
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
I am not sure but you can try with the filter stage also after the lookup stage instead of transformer.
Thanks,
Birendra
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 45
- Joined: Thu Aug 31, 2006 3:13 am
- Location: Bangalore
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
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
-
- Premium Member
- Posts: 149
- Joined: Fri Oct 15, 2004 12:13 am
- Location: Toronto,divine_auro@yahoo.co.in
- Contact:
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
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
With Regards
Biswajit
Biswajit