DataStage coulmn merge

marcogonnelli · Post by **marcogonnelli** » Thu Jun 16, 2005 7:54 am

Hi all anyone can help me with this ?
I have 2 sequential files (csv) with this structure...

1) File structure : key_col,colA,colB,colC
2) File structure : key_col,colD,colE

I need to import the records in these files in an Informix table with this structure merging the records with the same key_col values

3) Informix table structure :

key_col,colA,colB,colC,colD,colE

Exmaple :

1) 10,vala,valb,valc
20,vala1,valb1,valc1

2) 10,vald,vale

3) 10,vala,valb,valc,vald,vale
20,vala1,valb1,valc1,<null>,<null>

Is it possible doing that with merge stage or link collector stage ?
ThanX in advance Marco

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Thu Jun 16, 2005 8:19 am

You can do that will a lookup itself.

marcogonnelli · Post by **marcogonnelli** » Thu Jun 16, 2005 8:26 am

How can I do that ?

Thnxx Marco

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Thu Jun 16, 2005 8:38 am

The details of lookup is provided in the docs that come with the software. Try it and if you have any doubts or problems, do let me know.

pnchowdary · Post by **pnchowdary** » Thu Jun 16, 2005 8:56 am

Hi Marco,

You can use File structure1 as input and the File structure2 as lookup (using hash file)

Read the File structure2 from the source (.csv) file into a hash file
and then use this hash file as a lookup for File structure1 (Input) and if y ou find a match on the Key_col, then transfer all the required columns to the target.

I hope this will get you started.

marcogonnelli · Post by **marcogonnelli** » Thu Jun 16, 2005 8:57 am

oh thnxx I'm going to read it carefully in the docs

But I have documentation about Lookup stage on help
..but I don't have lookup stage on palette ?!?!? Is it bossible I don't have it installed ???? My DataStage Designer version is 6.0.0.21...
How is it bossible ?

pnchowdary · Post by **pnchowdary** » Thu Jun 16, 2005 9:10 am

If you are using Server edition of Datastage, then you wouldnt see a lookup stage. It is only present in the Enterprise/PX edition. When I meant lookup, I meant the hash file stage, which can be used for a lookup in server jobs.

ray.wurlod · Post by **ray.wurlod** » Thu Jun 16, 2005 9:33 pm

The server job Merge stage performs exactly the function you specified, which is an inner join. Read about it in its own manual (Merge.pdf).

marcogonnelli · Post by **marcogonnelli** » Fri Jun 17, 2005 1:00 am

...but inner join extratcs only rows matching the key (as SQL inner join does)........ I need to have all rows in file A and all rows in file B with columns values merged for same key values as I explained in the example.....
I've seen I don't have Lookup Stage in my version

I'll try to handle it with HashFile as pnchowdary suggested...
any other suggestion will be appreciated... ThnXX MarK

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Fri Jun 17, 2005 2:39 am

You can do an outer join user a merge.

But you need both source for merge in form of a seq file.