Multiple value in job parameter

ankita · Post by **ankita** » Wed Jan 30, 2008 2:26 am

Hi All,
I want to pass one or multiple values through a job parameter which will be used in IN clause of a SQL.
E.g. Job parameter : Destination
SELECT * FROM DEST WHERE DESTINATION IN (#Destination#);

Destination will have varchar values as ABCD, XYZ etc. If I am taking the job parameter as string then it's not accepting single quote, but finally the SQL should be like DESTINATION IN ('ABCD','XYZ').
Please suggest how to implement it using one parameter.

Thanks in advance !
Ankita

chulett · Post by **chulett** » Wed Jan 30, 2008 7:34 am

Escape the quotes by putting a back-slash (\) before each one.

ankita · Post by **ankita** » Thu Jan 31, 2008 4:25 am

Hi Craig,
I have tried passing \'XYZ\',\'ABCD\' and \'XYZ\'\,\'ABCD\', but none of these is working. Is there any other way to implement it ?

Thanks,
Ankita

ray.wurlod · Post by **ray.wurlod** » Thu Jan 31, 2008 7:38 am

What precisely do you mean by "not accepting"?

chulett · Post by **chulett** » Thu Jan 31, 2008 7:45 am

"Not working" means what here, what ends up in the job? And what stage / database are we discussing here?

ankita · Post by **ankita** » Thu Jan 31, 2008 8:24 am

The SQL has below where clause,

FINAL_DEST_UN_COD IN (#AB_Parm.PORT_OF_DESTINATION#)

I have passed value as \'USCMH\',\'HKHKG\'

Job is aborting with below error :
main_program: [DataDirect][ODBC DB2 Wire Protocol driver][UDB DB2 for OS/390 and z/OS]ILLEGAL SYMBOL \; VALID SYMBOLS ARE ( + - ? : CASE CAST USER SELECT <FLOAT> CURRENT NEXTVAL PREVVAL.

The parameter is defined as String and it's DB2 table. Please help !

Thanks,
Ankita

ankita · Post by **ankita** » Thu Jan 31, 2008 8:27 am

It's a ODBC Enterprise stage used to fetch data from DB2 table.

chulett · Post by **chulett** » Thu Jan 31, 2008 9:00 am

Do the logs show you what the actual sql ends up being for the query?

kcbland · Post by **kcbland** » Thu Jan 31, 2008 9:18 am

Where does the problem occur? Running from Director or running from unix command line dsjob and passing in this list as a parameter value? You're going to have to consider that any parameter values must also work from the command line if you're using any scheduling tools.

What you are trying to do seems easy but it has been tried for YEARS and never works in the end. Your best solution is to generate the complete SQL statement and write to a file and then use the "run SQL from file" feature. Even better, create a work table to hold the values and change your IN to an IN (select values from worktable) query.

ankita · Post by **ankita** » Fri Feb 08, 2008 6:47 am

I tried from Data Stage , as well as from Unix. In either case it's not fetching values.
Kept the SQL as, WHERE DEST in ('#DESTINATIONS#') and passed value as ABC','XYZ. It's running but not fetching any record. But view data is working.

Then changed the SQL as, WHERE DEST in (#DESTINATIONS#) and passed value as 'ABC','XYZ'. Then it's aborting.

Also tried with back slash (from director) before every single quote as stated earlier, that's also aborting.

I want to know the Data Stage solution i.e. how to pass value when run from Director. Then I can change the shell script accordinly.

Director log (OSH script) doesn't show the final SQL, it shows the SQL with parameter. Is ther any other way to check the final SQL ?

Thanks for your replies,
Ankita

chulett · Post by **chulett** » Fri Feb 08, 2008 7:14 am

ankita wrote:Director log (OSH script) doesn't show the final SQL, it shows the SQL with parameter.

If we were discussing a Server job here, that - the 'non translation' of a job parameter in the logs - would imply the parameter name didn't exist in the job. Is the same not true here? How, in a PX job, does one find out the final fully parameter substituted SQL a stage is attempting to run?

General question for the home audience.

ray.wurlod · Post by **ray.wurlod** » Fri Feb 08, 2008 2:17 pm

Anyone looked at the score, rather than the generated OSH?