Performing corelated subquery in datastage
Posted: Tue May 08, 2007 11:41 pm
Hi All,
I have the source data like this
sid,sname,recv_date,no_of_records,ind
----------------------------------------------
1, A, 09-may-2007,800, 1
2, B, 08-may-2007,700, 1
3, A, 08-may-2007,900, 1
4, A, 07-may-2007,600, 0
5, B, 07-may-2007,400, 0
------------------------------------------------
I need to select those records from the source having ind=1
and
an additional column requeried that is no of previous days record(if no record avaliable in previous day check for recent previous)record for same supplier.
my target should be like this
sid,sname,recv_date,no_of_records,ind,prev_day_record
------------------------------------------------------------------
1, A, 09-may-2007, 800, 1, 700
2, B, 08-may-2007, 700, 1, 400
3, A, 08-may-2007, 900, 1, 600
-----------------------------------------------------------------
I may need to process each record from the outer query in the subquery which will compare the date(inner.date<oiter.date) and select the previous day record.
Can anyhbody suggest me how to acheive this in datastage?
I have the source data like this
sid,sname,recv_date,no_of_records,ind
----------------------------------------------
1, A, 09-may-2007,800, 1
2, B, 08-may-2007,700, 1
3, A, 08-may-2007,900, 1
4, A, 07-may-2007,600, 0
5, B, 07-may-2007,400, 0
------------------------------------------------
I need to select those records from the source having ind=1
and
an additional column requeried that is no of previous days record(if no record avaliable in previous day check for recent previous)record for same supplier.
my target should be like this
sid,sname,recv_date,no_of_records,ind,prev_day_record
------------------------------------------------------------------
1, A, 09-may-2007, 800, 1, 700
2, B, 08-may-2007, 700, 1, 400
3, A, 08-may-2007, 900, 1, 600
-----------------------------------------------------------------
I may need to process each record from the outer query in the subquery which will compare the date(inner.date<oiter.date) and select the previous day record.
Can anyhbody suggest me how to acheive this in datastage?