Page 1 of 1
sequential file data length checking
Posted: Sat Jan 25, 2003 1:06 pm
by sweetleaf
Hi there,
I have a Sequential file stage and an ORAOCI8 stage.
So far I can import the contents of the sequential file into an
oracle table.
But I need to be able to check that the character length of one of the inbound columns from the csv file is 12 digits long, and if not then reject the entry and resume loading the rest of the csv contents.
Can I do this in DataStage?
Any help would be greatly appreciated!
Posted: Sat Jan 25, 2003 4:09 pm
by ray.wurlod
This one's easy.
Set up a constraint expression in the Transformer stage that uses the Len() function. For example:
Len(Link1.Column3) = 12
Only rows in which there are 12 characters in Column3 will be transmitted on this link. Create a separate output link to catch the rejected rows.
Another possibility is to guarantee that there are 12 characters by truncation/padding as appropriate. The easiest way to achieve this is to use the Fmt() function. For example:
Fmt(Link1.Column3, "L#12")
Ray Wurlod
Education and Consulting Services
ABN 57 092 448 518
Posted: Mon Jan 27, 2003 9:26 pm
by sankar18
Hi,
As Ray suggest put a constraint in the Transformer stage, If you want 12 digit or less than 12 digit use
Len(Link1.Column3)
Posted: Tue Jan 28, 2003 12:01 pm
by sweetleaf
Thanks guys!
That was a big help,
As you can tell I'm a DataStage newbie!
I just got your helpful posts now.
A quick question: If I have a job that I want to read the contents of all the csv files in a given directory into a database table, how can I get it so that where it prompts for file name (Sequential File Stage), I can enter file names like "Test" & "*" - so that DataStage will know to pick up and process anything starting with "Test".
Short of this functionality, how can I get it so that DataStage processes all of the CSV's in a given directory (assuming all csv's are the same format)?
Thank you soo very much for your help!
Posted: Tue Jan 28, 2003 4:09 pm
by ray.wurlod
You need to build a hierarchy of jobs, one that processes the search, the other that processes one CSV file. The first sets a "filename" parameter for the second.
A hierarchy is handled via Job Control (on the job properties tab) or, if you have a sufficiently recent release of DataStage (5.x or later) you can manage it with a sequence.
Ray Wurlod
Education and Consulting Services
ABN 57 092 448 518