DSXchange

I have a routine that generates custom SQL. This is called in my sequence and subsequently passed to a job.

By way of illustrating let's say MakeSQL routine returns the following SQL:
SELECT <ARG_1> as SK,
<ARG_2> as SURVIVORSHIP_KEY.


Now, the routine is always passed ARG_1, but I want ARG_2 to be optional (the routine would then use a default value from within it's code).
i.e. both the following are valid syntax:
MakeSQL(#ARG1#)
MakeSQL(#ARG1#,#ARG2#)

The question is: how do I create a routine that allows optional arguments?

note: I realise I can pass a dummy parameter for ARG_2 but I don't want to do this. The driver behind this question is that a number of sequences already call the MakeSQL routine. ARG_2 has only recently been identified as a required argument in a handful of instances and I do not want to have to go back and modify all other sequences to specify a dummy ARG_2.

-David

DataStage BASIC does not support optional arguments, so you could not use a routine in this language (e.g. from a job sequence). You did not indicate whether your routine is a server or parallel routine; I will assume the latter.

Even though C++ supports optional arguments, the interface from DataStage (the "parallel Routine" entry in the Repository, which is only an interlude that describes the routine and its arguments) does not.

Therefore you need to create a two-argument routine, and pass a dummy value (even "") for Arg2 to trigger use of the internal default replacement value.

yup, that's what I suspected. Thanks for confirming.

As they say in register arithmetic... shift happens.