Looking Up Multiple Tables

[devaraj4u]

Dear All,

I have a Table Which contains Reference to Other Tables.I need to populate a New Table Based on This main table and the Reference Tables.

Eg..

Main Table 1 Ref Table2 REf Table 3

COL1 COL1 COL1
COL2 COL2 COL2
COL3 COL3 COL3
COL4 COL4 COL4
COL5 COL5 COL5
REST OF COLUMNS

In The Above Strture Assume COL2(TABLE1) REFER TO COL1(TABLE2) AND COL3(TABLE1) IS REFER TO COL1(TABLE3). No I need to Populate a New Table with the following NEW TABLE(

TABLE1(ALL COLUMNS) + TABLE2(COL2,COL3) + TABLE3(COL2,COL3,COL4) .

HOW WILL DO THIS IN DATA STAGE.Please explain me the Steps how to do this.

Note: I used ODBC Stage to connect to Oracle.

Thanks & Regards,
K.S.Rajan.

[ray.wurlod]

Probably the easiest way is to set up user-defined SQL in your ODBC stage to perform the join while extracting the data from Oracle. This is also the preferred way, because less work is ultimately performed by DataStage (thus its performance is optimized, at least from the data volume point of view).

Otherwise you create a job that selects rows (required columns only) from TABLE1 and has two reference input links, one to TABLE2 and one to TABLE3. In the reference key expression for TABLE2 you specify LINK1.COL1 and in the reference key expression for TABLE3 you specify LINK1.COL1.

If Oracle is on a separate machine from DataStage server, prefer pre-loading local hashed files from TABLE2 and TABLE3, and performing the reference inputs from these.


Ray Wurlod
Education and Consulting Services
ABN 57 092 448 518