Another InList question: escaping chars:I'm about to give up

[richieRich]

Hi All,

I have been searching on DS Exchange for a while now reading up on how to pass in an In List into an SQL query from a job parameter.

I have taken the approach to create a csv seperated list in a server job (via sql query) and then to pass it to a parallel job (which will perform another sql query) using a job parameter via dsuserstatus.

It all works fine however the bit that doesn't work is trying to separate the in list using quote characters. My quote characters keep disappearing.

The parallel job that will use the in list has a db2 stage. Inside this stage the sql predicate looks like...
and fieldname in ('#PARAMETER_NAME#')

The raw in list created by the server job looks like the following...
Bike,Car

To seperate the in list using quote characters I have tried a number of escape characters. Here are my findings...

Tried to escape using backslash...
Passing in a list of: Bike\',\'Car
Creates a resulting of: Bike\,\Car

Tried to escape using three single quotes...
Passing in a list of: Bike''','''Car
Creates a resulting of: Bike\,\Car

Tried to escape using two single quotes...
Passing in a list of: Bike'',''TerminalAdminTr
Creates a resulting of: Bike\,\TerminalAdmin

Tried to escape using single quote inside double quote...
Passing in a list of: Bike"'","'"Car
Creates a resulting of: Bike","Car

All I'm looking for is the result to be but I'm finding it incredibly hard to produce this result ....
Bike','Car

I appreciate any of the help you guys can give me.


P.S. I decided to have a look at how the query looks in the osh for some clues and it looks like this....

and alias\.fieldname1 \= \\\'N\\\'
and alias\.fieldname2 \> alias\.value
and alias\.fieldname3 in (\\\'[&"PARAMETERNAME"]\\\')) out_of_band

alias.fieldname3 is where I am trying to pass a job parameter into the sql as an in list.

[richieRich]

From the looks of it perhaps sometimes it's just not possible to escape special characters in ds. Am I right?

[chulett]

I don't know. I would think there should be some way to do that, but I don't have any kind of DataStage access to test things out. Yes, DataStage is notorious for stripping quotes from job parameters but I don't recall anyone who couldn't find some kind of escape mechanism that works... eventually.

What other options have you considered? Perhaps you could put the contents of the in-list into a work table and join to that, or (if your source stage supports it) write your complete sql out to a file and then use the 'sql file' option in the stage to read it from a pathname at runtime. Lastly, have you contacted your official support provider about this? Depending on your exact 8.x release and patch level you may find this is a known issue with your version.

[piyu]

I know this is coming late and someone might have done it some way too...But this is what I managed :

- Use a Sequencer;

- Pass you IN list as a comma separated string : (in Parm file : filedname=ABC,CED,MNO );

- In the command stage, put this complete line:
echo #fieldname# |sed -e 's/^/?/g' |sed -e 's/$/?/g'|sed -e 's/,/?,?/g'|sed -e "s/?/?'/g" -e 's/?/\\/g' |awk '{ printf("%s", $0)}'

This whole command puts the back slashes in place after inserting single quotes. (Its a lot of round-about processing but works )

- Pass the output of this command in the next Job Execute stage to the parameter which you will code in SQL :
SQLfieldname = cmdStage.$CommandOutput

[ray.wurlod]

Wouldn't a User Variables stage have been easier?