One Input file and multiple Output

kcbland · Post by **kcbland** » Wed Sep 24, 2003 8:59 pm

Why can't you pivot the 1 row into the 12, creating a stream of 12X as many source rows and then do the lookup once?

You have not stated your release of DataStage, but if you have 6+ you can use a transformer with 12 output links feeding a link collector, then stream that to another transformer with the reference lookup. If you're on 5- then you can use an OCI --> XFM+ --x12--> InputSideSeqFileOutputSide --> XFM+reference --> OCI. You'll have to put an after-transform concatente command (NT use copy, Unix use cat) to coalesce the 12 temporary sequential files into a single sequential file to pickup and continue processing. Do not make the mistake of using append to existing file.

Kenneth Bland

ray.wurlod · Post by **ray.wurlod** » Wed Sep 24, 2003 9:02 pm

Use a Pivot stage to generate one row for each course (and key column), then you need only define one lookup (given that it's the same course table). You now have the (up to) 12 records that you want to load into Oracle.

Ray Wurlod
Education and Consulting Services
ABN 57 092 448 518

Baldo · Post by **Baldo** » Wed Sep 24, 2003 9:38 pm

Hi Louise,

There is one other solution to get it to work. You can use an OCI stage -> Tx -> Sequential File -> OCI. In the transform, for the column output, use the derivation - col1 : Char(10) : col2 : Char(10) : ... : col12. Remember to leave the last Char(10) off the end.

For this solution, it is assumed that you will use Unix sequential files, but if you want to use a windows format, you can add the carriage return char to the derivation - col1 : Char(13) : Char(10) : col2, etc.

Then change the delimiter on the input and output link to be 000 (null). If you need to use delimiters because of bad chars in the data (commas, etc.), the above derivation should be changed to put them in - '"' : col1 : '"' : Char(10), etc.

This will turn one column in to 12 columns out (by putting the line terminators as part of the column derivation). With a little more work you can make this work with multiple columns per row.

Regards,

Chris.

Optico · Post by **Optico** » Thu Sep 25, 2003 2:31 am

Hi Chris,

I've just tried your solution and I cannot get it to work.

I've created a seq-file with 7 names in one row. Put it it through a transformer using concatenation and CrLF and write it to a new seq-file. Put it through another transformer (having changed both in- and output links to have delimiter = 000) and writing back to a third seq-file.

My input file looks like this :
Allan,Bill,Carsten,Drude,Esther,Flemming,Georgina

The intermediate file looks like this :
"Allan
Bill
Carsten
Drude
Esther
Flemming
Georgina"

and the job aborts with the following message :
Project:Test (test)
Job name:Testjob2
Event #:34
Timestamp:9/25/2003 10:24:24 AM
Event type:Fatal
User:test
Message:
Testjob2..CSeqFileStage2.DSLink7: read_delimited() - invalid quotes, row 1 column F1 = "Allan"

Is it me, or do you need to get back to the drawingboard?

B. Sorensen,
Optico IT ApS

ray.wurlod · Post by **ray.wurlod** » Thu Sep 25, 2003 4:24 pm

Ensure that the quote character is 000 in all SequentialFile stages.

Ray Wurlod
Education and Consulting Services
ABN 57 092 448 518

danjm · Post by **danjm** » Wed Oct 01, 2003 10:32 am

I just wanted to thank Louise for asking the question (I think I saw another similar one earlier but can't find the reference) because a co-worker of mine was trying to deal with the same situation.
Thanks so much to all of you for your responses. With your direction, I was able to implement the solution using the Pivot Stage suggestion. I've passed that technique on and everyone is happy now.
Have a great day everyone.

Dan Marshall
Alberta Learning
Edmonton, AB