Inserting Data into single comma seperated row

[JezT]

I have a job that creates an output line set to VarChar 1000 and which is comma seperated. And example of 2 rows is shown below.

5,16083,,,LGD,0079.1200
5,16222,EAD,000000000000000010,LGD,0094.5300

As you can see from the second row, there is a value of EAD (Score Type Code) and it's related value (000000000000000010). In row one, this is missing as the data is not currently within the DB2 table I am extracting the data from.

The '5' is the Record Type and the 2nd value (16083, 16222) are the Key fields that all the following score types and values relate to.

Is it possible to carry out checks on each field within the row and to insert values in, if there is nothing there ? For example, in row 1 I would want to place EAD after the second comma and then a 'space' after the 3rd comma.

I have been looking at the substring function on help to check for a specified field but cannot seem to get it to work.

Any ideas ???

[WoMaWil]

I don't see the real problem!

You might use Stage Variables:
If LEN(TRIM(abc.Value3))=0 THEN 'EAD' ELSE abc.Value3
If LEN(TRIM(abc.Value4))=0 THEN space(1) ELSE abc.Value4

seems easy to me, with basic DS-knowhow.

[ray.wurlod]

The FieldStore() function can insert a value into a delimited string. Research it in on-line help or in the DataStage BASIC manual.

[JezT]

I have looked at both solutions given and have managed to get the FieldStore function to work fine for the initial value. However, what I didn't point out initially is that there could be up to 50 Codes that will need inserting into the one output line so how do I use 50 FieldStore statements together ?

So my output line could look like :

5,16083,,,LGD,0079.1200,,,,,DEF,0089.1200

So I would need to insert EAD after 16083, and then another code after 0079.1200 and then another code after that and so on.

[chulett]

Then it sounds like you'll need to write a custom routine and take an iterative approach.

[ray.wurlod]

That you didn't point it out initially is why, of course, we didn't answer it initially - we read the question and answered that.

I agree with Craig that you will need a Routine so that you can take an iterative approach - an expression containing 50 FieldStore() functions would probably exceed the maximum permitted length of an expression (8KB if my memory serves).

[narayana_382]

Ray,

Kindly help us how do I proceed in writing a custom routine for implementing the iterative approach.

Thnks,
Narayana.

[ray.wurlod]

Design your algorithm on paper and from that produce a design plan and test plan. Generate some test cases and document these. In the Repository choose New Server Routine. Make sure that the routine type is "Transform Function" (this should be the default). Document the routine on the General tab. Give names and descriptions for arguments on the Arguments tab. Create the code on the Code tab. If your routine calls any other routines, document these on the Dependencies tab. Use the Compile and Test buttons (respectively) to compile and test your routine.