Another InList question: escaping chars:I'm about to give up
Posted: Thu Mar 25, 2010 11:37 pm
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.
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.