Create output file names based on data in a column

PAULOM · Post by **PAULOM** » Thu Jul 11, 2013 2:19 am

Hello,

I shall want to know if it was possible to put in the name of my files of exits, the data of a column, I am understandable :

On a job, I extract from Oracle via a request SQL a table. On this table, there is a column Code_Test which possesses several values (ex: Code1, Code2, Code3). I create afterward files in the format *.csv.

My question is, it is possible to create my files for example :

Test_Code1.csv
Test_Code2.csv
Test_Code3.csv

To create so much file as there is of values different Code_Test.

Thanks a lot.

Sorry for my bad english, i'm french.

ray.wurlod · Post by **ray.wurlod** » Thu Jul 11, 2013 2:56 am

Welcome aboard. Bienvenue. You can use a Folder stage for this purpose.

PAULOM · Post by **PAULOM** » Thu Jul 11, 2013 4:09 am

Thank you very much for your answer.

On the other hand, have you an example on Folder Stages? Because my skills in DataStage are more than limited

Thanks

chulett · Post by **chulett** » Thu Jul 11, 2013 6:49 am

Well... they are a little tricky to use. Check the Server Job Developer's Guide manual for the official description and do an exact search here for 'folder stage' to see what has been discussed here. Seem to recall one such conversation just last month...

PAULOM · Post by **PAULOM** » Thu Jul 11, 2013 8:21 am

Yes, thank you I have already read this doc but I block on the place where it is necessary to insert the Stages Folder.

My job :

Oracle-->Transformer--->Sequentiel File Stage

Where place the Stages Folder?

Thanks

chulett · Post by **chulett** » Thu Jul 11, 2013 9:35 am

It replaces the Sequential File stage as the target of the job.

PAULOM · Post by **PAULOM** » Fri Jul 12, 2013 1:41 am

oK, It will be :

Oracle-->Transformer--->Stage Folder ?

chulett · Post by **chulett** » Fri Jul 12, 2013 7:04 am

Yes.

This is the post I referred to earlier. There are others if you search.

viewtopic.php?t=150112

PAULOM · Post by **PAULOM** » Fri Jul 12, 2013 9:01 am

Ok thanks, it's possible de create *.csv files et not *.txt?

chulett · Post by **chulett** » Fri Jul 12, 2013 9:59 am

Sure, just use the proper field delimiter - a comma in that case.

chulett · Post by **chulett** » Tue Jul 16, 2013 7:13 am

As noted in the linked post and the docs, yes it takes a single record so if you have multiples you need to get them all concatenated together with a record delimiter between each. Do that in a stage variable and then send it to an aggregator set to 'last' before the Folder stage.

Sorry but I don't understand your second question.

chulett · Post by **chulett** » Tue Jul 16, 2013 9:28 am

You generate rows like this:

1
1 2
1 2 3
1 2 3 4

All you want to do is send one to the Folder, hence the aggregator set to 'last'.

No clue on the second thing at the moment.

PAULOM · Post by **PAULOM** » Tue Jul 16, 2013 10:01 am

To recapitulate my job : I will do this

Oracle(SQL)-->Transformer-->AGGREGATOR-->StageFolder

it's ok?

Thanks Chulett

PAULOM · Post by **PAULOM** » Wed Jul 17, 2013 7:53 am

I have a problem, how I can make in my StageVariable :

file :
ColumnA;ColumnB;ColumnC
1;2;3
3;4;5
6;7;8

What I want before TheAggregator:
ColumnA
1;2;3;4;5;6;8...

Thanks

ray.wurlod · Post by **ray.wurlod** » Wed Jul 17, 2013 4:43 pm

You're getting into Routine territory now. You need to scan the existing list to determine whether the value already exists (use the Locate statement) and insert it if not.

Maintain the list as a field mark (@FM) delimited dynamic array sorted. Implement the following logic in your routine.

Code: Select all

Locate NewValue In ValuesList By "AR" Setting Location
Else
   Ins NewValue Before ValuesList<Location>
End
Ans = ValuesList