Returning more than one value in DataStage

[RamonaReed]

This may seem like a very basic question , but I am very new to DataStage and I am trying to create a New Server Routine that will allow me to return more than one value. I would be passing the routine 5 arguments and based on those, I want to return 4 new values which would be populated into 4 fields during a Transformer Stage. The New Server Routine only returns ANS, so how do I pass back all 4 fields and also associate those 4 fields with the output fields.

Thanks in advance,
Mona

[chulett]

That's ok, Mona - it's one of the less obvious things when dealing with Routines.

There are several approaches, some more 'advanced' level than others. For an (hopefully) easy answer, what you'll need to do is pass back a delimited string as ANS with all of your return values in it. Use something simple that won't occur in your data, that would typically mean a 'pipe' "|" would be a good choice. The colon ":" is the concatenation operator, in case you hadn't stumbled across it yet.

You can then take the return value from the routine and use the FIELD function to parse it back out into four pieces. This may mean that you add an additional Transformer to your job so that you can call the routine once, store the return value in your data stream and then in the second Transformer use FIELD to turn that one column into four.

If this doesn't make sense or you have more questions, don't hesitate to post back!

[mhester]

Mona,

It is true that you can only return one value so if you require more than one value back you must create a return value that includes all of your values. It is essentially accomplished by creating a single variable for return that is delimited (by something).

Example:

Ans = "A":"|":"B":"|":"C"

Create a stage variable to call your routine -

svRoutineRtrn (or something - does not matter)

The return could then be parsed in a stage variable like -

Return1 = FIELD(svRoutineRtrn,"|",1)
Return2 = FIELD(svRoutineRtrn,"|",2)
Return3 = FIELD(svRoutineRtrn,"|",3)

I hope this helps,

Michael Hester

[RamonaReed]

Thank you Craig and Michael for responding so fast. I appreciate it.

Mona

[ray.wurlod]

An alternative is to update five user variables @USER0 through @USER4 in the routine. These are available in the stage that invokes the routine.

For example, I have a routine that parses a telephone number into country code, area code, carrier id and line number and returns these through @USER1 through @USER4. The routine itself returns a success/fail indicator that I use to constrain output. Four columns on the output link have derivations @USER1 through @USER4.

There are only five of these variables, named @USER0 through @USER4.

[crouse]

Yet another, more straight forward approach is to define 4 Stage Variables, say A,B,C and D.

Create your routine with 9 args, the last 4 being A, B, C and D.

Inside your routine assign the answers as applicable to A, B, C and D.

When you return from the routine, your stage variables are populated with answers.

Pretty slick.

[ray.wurlod]

Your approach relies on routine arguments continuing to be passed by reference rather than by value. There's no such guarantee, in fact this is why warnings are generated by the Routine pre-compiler when it detects any attempt to change the value of any argument.
@USER0 through @USER4, on the other hand, are documented as "system variables for user use".

[vmcburney]

Just a warning on concatenation, because it's caught me out in the past, the concatenate will return a null if any of the values being concatenated are null. "A" : "B" : NULL : "D" will become NULL. You need to make sure your routine sets each output variable a non null value before you concatenate them. Obviously this problem is avoided with the @USER approach.

[RamonaReed]

Thanks for all the help. I used the @USER0 through @USER3 and it worked wonderfully.

Thanks again,
Mona