DSXchange

Posted: **Wed Mar 08, 2006 9:49 am**

How can you do the following:

A file coming in through DataStage has one record coming in. The reference file has two records, which are stored in a hash file. We would like to have in the output two records. A left or right join in a query would work, but how can you do this in DataStage?
Example:
The file has:
zip_code, comp_cd, resp_cd, zipcount
00959, A, E, 1

The hash file has:
Zip_code, comp_cd, resp_cd, zipcount, RSID, ZIP_SPLIT_QTY, Perzipcount
00959, A, E, 1, 3G6D, 90, 0.9,
00959, A, E, 1, 3G6M, 10, 0.1,

Thanks!
Rodre

Posted: **Wed Mar 08, 2006 12:42 pm**

Hi Rodre,

If you have the reference file in a sequential file rather than a hashed file. Then you can try using the 'Merge' Stage. You can perform the following joins using the 'Merge' Stage:

Code: Select all


Pure Inner Join

Complete Set

Right and Left Only

Right Outer Join

Left Outer Join

Right Only

Left Only

HTH,

Naveen.

Posted: **Wed Mar 08, 2006 1:14 pm**

naveen is right. use the merge stage to achieve your goal. if you use a hash file, depending on your key, the duplicates will be removed. check and make sure you have those two records in the hash file which i highly doubt, as your key would be one of the first four columns, which are identical. hash file will skip the first record and store the second record only.

Posted: **Wed Mar 08, 2006 4:48 pm**

Hi Rodre

Another way to think is to swap your Sequential file to reference and hash file as your main file. Treat your hash file as source and convert your input Sequential file to hash file. This is just another way to do

DSXchange

Output two records

Output two records