Writing into a sequential file in a specific format....
Moderators: chulett, rschirm, roy
Writing into a sequential file in a specific format....
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!
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!
Check this FAQ post for one approach.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 612
- Joined: Thu May 03, 2007 4:59 am
- Location: Melbourne
Use a stage variable, say
and assign 3 output columns
Code: Select all
svInput --> '-':Input.A:'~':Input.B:'-':Input.C
Code: Select all
Col1 --> 'XXX': svInput:char(10)
Col2 --> 'YYY': svInput:char(10)
Col3 --> 'ZZZ': svInput
Joshy George
<a href="http://www.linkedin.com/in/joshygeorge1" ><img src="http://www.linkedin.com/img/webpromo/bt ... _80x15.gif" width="80" height="15" border="0"></a>
<a href="http://www.linkedin.com/in/joshygeorge1" ><img src="http://www.linkedin.com/img/webpromo/bt ... _80x15.gif" width="80" height="15" border="0"></a>
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.
file1 --> 'XXX': svInput
file2 --> 'YYY': svInput
file3 --> 'ZZZ': svInput
Just to give the available opitons. This may not be exactly effecient.
Impossible doesn't mean 'it is not possible' actually means... 'NOBODY HAS DONE IT SO FAR'
@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....
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....
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
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
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
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
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
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
?
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
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
could you please send me the link.... that would be great help....
email: raksha.pai@gmail.com
email: raksha.pai@gmail.com
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...
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...
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.