Writing into a sequential file in a specific format....

RakshaPai · Post by **RakshaPai** » Wed Jun 27, 2007 10:37 pm

hello all,
faced with a new prob

my requirement:
SELECT A,B,C from aaa.

For each row selected, three records are written to the output file. Each one is identical except for the first field in the record. the first field has to be XXX,YYY, ZZZ. my output file is going to look something like this:
for the first record selected,
XXX-11~abc-ddd
YYY-11~abc-ddd
ZZZ-11~abc-ddd

similarly for 2nd, 3ed etc etc..... can i achieve this in DS... ? if not what else should be the aproach?

Thanks in advance!

chulett · Post by **chulett** » Wed Jun 27, 2007 10:40 pm

Check this FAQ post for one approach.

JoshGeorge · Post by **JoshGeorge** » Wed Jun 27, 2007 10:56 pm

Use a stage variable, say

Code: Select all

svInput --> '-':Input.A:'~':Input.B:'-':Input.C

and assign 3 output columns

Code: Select all

Col1 --> 'XXX': svInput:char(10)
Col2 --> 'YYY': svInput:char(10)
Col3 --> 'ZZZ': svInput

kumar_s · Post by **kumar_s** » Wed Jun 27, 2007 11:04 pm

Anoter option with the above mentioned approach would be, write into three file and concatinate all three.

file1 --> 'XXX': svInput
file2 --> 'YYY': svInput
file3 --> 'ZZZ': svInput

Just to give the available opitons. This may not be exactly effecient.

RakshaPai · Post by **RakshaPai** » Wed Jun 27, 2007 11:20 pm

@chulett
Thanks a lot.. answered most of my questions

will try it out right away...
@JoshGeorge
looks quite straight... one thing i have a doubt about,,,
11~abc-ddd is entire row from a select. can i just use it as an input to the stage variable??
@kumar_s
like this i will 3 different files right.... i need to have all of them in one file... is there anyway i can first write into 3 diffrent files and then someway have the outputs from 3 files in one single file to satisfy my format.. i.e
'XXX': svInput -- first row from file 1
'YYY': svInput --second row from file 2
'ZZZ': svInput -- third row from file 3....

RakshaPai · Post by **RakshaPai** » Thu Jun 28, 2007 12:30 am

i have never used satge variables before...

i don know where to start.. literally learnign DS from scratch... any pointers/ examples of declaring and using them... more so in the context of what i am trying to do

RakshaPai · Post by **RakshaPai** » Thu Jun 28, 2007 1:02 am

i m am declaring 2 stage variables in transformer stage.
SvLineSeperator char(10)
SvFieldDelimiter char(1), initial value ","

following the link that chulett pointed to,
Code:

-------------------------------
|Char(10) | SvLineSeperator |
|-------------------------------|
|"," | SvFieldDelimiter |
-------------------------------

i did not get where do i code this,
Code:

----------------------------------------------------------
|Link.col1 |Col1 |
|----------------------------------------------------------
|Link.col2 : SvLineSeperator : |Col2 |
| "Col1 of new row" : SvFieldDelimiter : | |
|"Col2 of new row" | |
---------------------------------------------------------

in the derivation column of the transformer or the one of the stage variables..

if it is in the derivation column on the transformer, what do i have to specify in the derivation column of the stage var?
Derivation columns

Umberto · Post by **Umberto** » Thu Jun 28, 2007 2:47 am

Hi

I resolve a similar problem with a trasformer Stage. It receives one input link, the data red from the table, and put the output in three link. In the first you put in the first colunn "aaa", in the second "bbb" and in the Third "ccc". Merge this three links using the funnel Stage and the output end in the flat file.

If it is not clear I'll try to send you an example.

Have a nice day

Umberto

Umberto · Post by **Umberto** » Thu Jun 28, 2007 2:48 am

Hi

I resolve a similar problem with a trasformer Stage. It receives one input link, the data red from the table, and put the output in three link. In the first you put in the first colunn "aaa", in the second "bbb" and in the Third "ccc". Merge this three links using the funnel Stage and the output end in the flat file.

If it is not clear I'll try to send you an example.

Have a nice day

Umberto

Umberto · Post by **Umberto** » Thu Jun 28, 2007 2:50 am

Hi

I resolve a similar problem with a trasformer Stage. It receives one input link, the data red from the table, and put the output in three link. In the first you put in the first colunn "aaa", in the second "bbb" and in the Third "ccc". Merge this three links using the funnel Stage and the output end in the flat file.

If it is not clear I'll try to send you an example.

Have a nice day

Umberto

RakshaPai · Post by **RakshaPai** » Thu Jun 28, 2007 2:51 am

could you please send me the link.... that would be great help....

email: raksha.pai@gmail.com

Umberto · Post by **Umberto** » Thu Jun 28, 2007 3:10 am

I send you an email and a dsx file with an example.

RakshaPai · Post by **RakshaPai** » Thu Jun 28, 2007 4:27 am

hey all,
i just coded a job looking at the link given by chulett.
i am selecting two columns,
have 2 stage variables
SvLineSeperator char(10)
SvFieldDelimiter char(1), initial value ","

in the transformer stage, i have stage variables as
----------------------------------------------------
Stage Variables |
----------------------------------------------------
Derivation | Stage Variable |
-----------------------------------------------------
Char(10) | SvLineSeperator |
------------------------------------------------------
"," | SvFieldDelimiter |
-----------------------------------------------------

----------------------------------------------------
DSLink1 |
----------------------------------------------------
Constraint
----------------------------------------------------
Derivation | Column Name |
-----------------------------------------------------
DSLink2.contr_proj_id| contr_proj_id |
------------------------------------------------------
DSLink2.crsrc_emp_id
: SvLineSeperator :
"col 1 of new row" :
SvFieldDelimiter :
"col2 of new row" | crsrc_emp_id |
-----------------------------------------------------

My data when i run the job is just what i am selecting... without any chane... what am i doing wrong here

Also can any of u lemme know how do i open and view a dsx file.
Umberto has sent an example which i cant view..!!
i work on a remote unix server and hence cant open the file on my desktop from DS...

Umberto · Post by **Umberto** » Thu Jun 28, 2007 4:34 am

You can usa a normal text editor to view a dsx file. It is a normal text file. Anyway you have to import into you project, using datastage manager, to see the job. The job, named "test0001", is saved in "Test" category. Tell me if you are not able to do that. In this case I send you another email to explain you better the job.

RakshaPai · Post by **RakshaPai** » Thu Jun 28, 2007 4:47 am

can u please send me an email on the job.. i cant make out when i open the dsx file in textpad....