Performing corelated subquery in datastage

[sjordery]

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?

[ray.wurlod]

The same rules apply in DataStage as in SQL. Can you effect the correlated subquery in your extraction SQL, thereby saving your brain the grief of reinventing a wheel that already exists?

[Nisusmage]

If you are using ODBC, one thing to note is that ODBC doesn't like grouping and intense SQL statements. The only way to find out if it works, obviously is to test it. If it's a small enough set of data, I say do it.