Looping in Datastage

[srinagesh]

Hi,

My Datamodel is as follows

fact: SourceLocationID, DestLocationId,MonthID
TimeDim : MonthID

I need to do the following

1. select sourcelocationId, destLocationId,min(month) from fact group by sourceLocationId,DestLocationID ==> Call it A

2. Select A.SourceLocationId,A.DestLocationId, B.Month
from A, Month B
where A.MonthId <= B.MonthID ==> Call it C

3. Truncate Target Table; Insert into Target Table as select * from C


Could you please suggest a datastage solution for this.


Regards
Nagesh

[ray.wurlod]

DataStage implicitly has the looping construct.
Do your select from A, perform a range lookup against B, and use a constraint expression to determine whether row was returned from B (and therefore whether to send on to target). Table C is not required. If you need a multi-row return from B, you will need to use an ODBC stage for the lookup, since the OCI stage only ever returns one row.