DSXchange

Hi
I need to have a job that uses a field on the input record of a sequential file and then creates a multiple of output lines dependent on the value. Is there any way of doing this without using Basic code in the Job Control window?
Eg
Input File:
Title NumUses
A 1
B 3
C 2

Outputs:-
A
B
B
B
C
C

Any ideas?
Cheers
Andy

you can do it while using, at exit your to transform, several files having the same file name. On the level of the sequentiel file, select the option Append. You will be able create of recording as much than you have output files.
If this option is not enough, you can have this Job to control by another Job (with basic) which is in a loop.

Pey

Andy,

You could try a multirow lookup to a uv stage table containing the data:
1
2
2
3
3
3
...

This might work if your occurance field is limited and you can hard code enough data in the uv stage. You might have to add another field so there is something to carry into the lookup. If so then you could use another transform to strip off the excess columns.

Hope this helps,
Steve

Andy,

What I would do would be to write a routine where you pass it Title and NumUses as an argument. This routine would return a string containing a multi-value field with Title the appropriate number of times separated by a @VM (look up the FieldStore function to create this string).

Write this multi-value string into a hash file and "normalize" on this field when reading from the hash file. You have to tell the hash file that this field is a multi-value field in the metadata for the column.

I know this sounds confusing, but it works pretty well and with a minimum of coding.

Good Luck,
Tony

Assuming your output is also a sequential file, try the following column derivation expression. You will also need a stage variable (called EOL in my example) that has been initialized to the correct end-of-line sequence for your platform (that is Char(13) for UNIX, or Char(10):Char(13) for Windows).

Str(InLink.Col1 : EOL, InLink.Col2)

From your data this would generate (representing end of line by ~):
A~
B~B~B~
C~C~

Now, in the target sequential file stage, specify "None" as the line termination character on the Inputs tab. If you subsequently read from this stage specifying the appropriate line termination character on its Outputs tab, you will see the result you desire.

Ray Wurlod
Education and Consulting Services
ABN 57 092 448 518

I haven't seen this technique before, Ray. This accomplished the same thing I was trying to do, but with a sequential file. Cool.

Another one to add to the old bag of tricks [:D].

Thanks,
Tony

Credit for this one belongs to Jim Tsimis, one of the original technical people involved with the original development of DataStage at VMARK. It is hugely faster than any other technique for this result.

Hi,
As usull Ray has it right on the money [:)].
I just wanted to corrent a point,
a char(13): char(10) is the windows CR + LF line termination and char(10) is the unix LF line termination.
An even more simple way is (on windows platforms) :
MyRoutine(string,instances)
newLine = @NULL
for i = 1 to instances
newLine := string : char(13): char(10)
Next i
Ans = newLine
(for unix leave out the char(13) concatenation)

then you just write to a sequential file the string you got and when you read the seq file you'll see that you actually wrote several lines in an actually 1 write and you can even read it properly using a notepad

*** and you need not adjust the seq file stage even [:)] ***

Roy R.

Ah, yes, but Jim's solution does not require you to create a Routine. (There is no capacity for loop constructs in column derivation expressions - or any other kind of DataStage expression for that matter.)

Thanks everyone. i've now got this working fine.

Cheers
Andy[;)]