Look up on 6 fields

[pavan_test]

Hi All,

I have 2 input streams coming into my job. Both of them have different meta data.

File1 has the following meta data:
(All the 6 fields are integers)
col1
col2
col3
col4
col5
col6

File2 has the following meta data:
( both the fields are integers )
ABC
TXT

now i have to perform a look up ( or merge or join ) where the condition is:

if col1 ( from File1) equals to ABC ( from File2) then output TXT.
( if the lookup matches then I have to output TXT as col1_TXT. just to differentiate with other outputs )

if col2 ( from File1) equals to ABC ( from File2) then output TXT.
( this I have to output as col2_TXT)

if col3 ( from File1) equals to ABC ( from File2) then output TXT.
( this I have to output as col3_TXT)

if col4 ( from File1) equals to ABC ( from File2) then output TXT.
( this I have to output as col4_TXT)

if col5 ( from File1) equals to ABC ( from File2) then output TXT.
( this I have to output as col5_TXT)

if col6 ( from File1) equals to ABC ( from File2) then output TXT.
( this I have to output as col6_TXT)

can anyone please suggest me how do I do it in data stage.

Regards
Pa

[us1aslam1us]

You need to use six different Lookup stages. Run the reject links from each of them through a Funnel stage to accumulate the rejected rows. Be careful with the NULL values in both source and reference.Or you can create two staging temp tables and perform some joins using SQL.

[pavan_test]

can you please tell me why should I Run the reject links from each of them through a Funnel stage to accumulate the rejected rows.

[ray.wurlod]

Because there's no other way to accumulate them all. Of course, if you want to report separately from each failed lookup, then you would not require the Funnel stage.