Can a parameter be used to supply sql with a list of values?

ronchi · Post by **ronchi** » Sun Feb 22, 2009 9:09 pm

We have some sql that contains the clause:
WHERE CODE IN ('123','456','789')

We'd like to replace the hard-coded list with a parameter, something like:
WHERE CODE IN (#pCodeList#)

We'd rather not have separate paramaters for each code, because the list may expand. So we wish to avoid something like:
WHERE CODE IN ('#param1#','#param2#','#param3#')

What I've tried:

1. Single-quotes
pCodeList='123','456','789'
sql contains WHERE CODE IN (#pCodeList#)
Result:
SQL0104N An unexpected token " " was found following ""
...
WHERE CODE IN (?\,?\,789)

2. Double-quotes
pCodeList="123","456","789"
sql contains WHERE CODE IN (#pCodeList#)
Result:
SQL0206N "123" is not valid in the context where it is used.
(this makes sense, since the double-quotes also fails in the db2 client).

3. Triple-quotes
pCodeList='''123''','''456''','''789''' (that is three single-quotes)
sql contains WHERE CODE IN (#pCodeList#)
Result:
SQL0104N An unexpected token "\" was found following "".
...
WHERE CODE IN (\\?\\,\\?\\,\\789\\)

4. Double-quotes around the parameter
pCodeList='123','456','789'
sql contains WHERE CODE IN ("#pCodeList#")
Result:
SQL0206N " \, \, \, \, \,872\" is not valid in the context where it is used.
...
WHERE CTRYNUM NOT IN ("?\,?\,789\")

5. Single-quotes around the parameter
pCodeList='123','456','789'
sql contains WHERE CODE IN ('#pCodeList#')
Result:
When processing argument -outlinkpropsWhen processing output link property list:
...
WHERE CODE NOT IN (\'\?\\,\?\\,\789\\\ is not a valid property list: Expected value for property "USERSQL"
Encountered <eof> while seeking end of proplist

Can anyone point me to a solution?
Or is it simply something that cannot be done?

ray.wurlod · Post by **ray.wurlod** » Sun Feb 22, 2009 11:08 pm

Have you done a Search? I'm sure this has been asked and answered previously.

Gazelle · Post by **Gazelle** » Sun Feb 22, 2009 11:20 pm

Yes, I did a search and could not find anything.
Admittedly, I am having trouble with the search facility for anything except the "exact match" option, so maybe I missed it.
If anyone does have a link to an existing topic, please let me know.

I did find this on the IBM Info Center:

DB2 Enterprise

* If a job contains a DB2 stage that uses user-defined SQL and a job parameter that also contains a single quote, then these single quotes are stripped out and the SQL statement becomes non-valid. (eCase 116556)

I am waiting for a response regarding a workaround.

Gazelle · Post by **Gazelle** » Sun Feb 22, 2009 11:46 pm

The workaround was for an older version of DataStage, and was to "protect the quotes with a backslash".
Unfortunately, it didn't work with v8.0.1

Continuing on from above...

6. Escape the quotes with a backslash
pCodeList=\'123\',\'456\',\'789\'
sql contains WHERE CODE IN (#pCodeList#)
Result:
SQL0104N An unexpected token "\" was found following "".
...
WHERE CTRYNUM NOT IN (\?\\,\?\\,\789\)

We'll be opening a Case with Tech Support, but in the meantime, if anyone has any suggestions, please let us know.

Kirtikumar · Post by **Kirtikumar** » Tue Feb 24, 2009 4:12 am

Why dont you try something like below?

1. A row generator generating the values you want in IN query. Or a Seq file containing the IN list - one two for each value.
2. A Sparse lookup (if the IN list is small, otherwise join) on the table from on which you wanted to run IN query
3. On the output do the remove duplicate.

If you want the IN list to be a parameters, you can generate a seq file from that parameters and then use it here.

MarkB · Post by **MarkB** » Tue Feb 24, 2009 5:47 am

I have no problem with this in DB2 .... for example ....

SELECT * FROM TABLENAME WHERE COL1 IN(#MYPARAMS#);

In MYPARAMS I enter something like 'ABC','DEF' and it works fine. This is for a varchar column - if it was numeric you would of course lose the quotes. This is in DataStage 8.

Gazelle · Post by **Gazelle** » Mon Mar 02, 2009 12:47 am

Another option: we could also read the sql from a file (and build the file using environment variables).

Thanks for the ideas.
I'll run Kirtikumar's ideas past ronchi and the rest of the team, but my opinion is that this introduces another set of objects (e.g. a row generator or a sequential file) that need to be maintained, rather than simply the DSParams file.

@MarkB, we must have slightly different configurations. We're running DataStage v8.0.1 on a Redhat Linux server, connecting to DB2 on a zOS mainframe. We need to use the good old DB2 API stages, not the Enterprise stage. So I guess it is something peculiar to either the API stage or to Linux.

Tech Support have tested a solution involving (for Redhat Linux):
1) Patch e128097
2) Patch JR30717
3) Unsetting the APT_OSL_ESC_SQUOTE variable

manuel.gomez · Post by **manuel.gomez** » Thu Mar 12, 2009 9:01 am

We are having the same problem here

Can we pick these patches from any IBM site???

Gazelle wrote:Another option: we could also read the sql from a file (and build the file using environment variables).

Thanks for the ideas.
I'll run Kirtikumar's ideas past ronchi and the rest of the team, but my opinion is that this introduces another set of objects (e.g. a row generator or a sequential file) that need to be maintained, rather than simply the DSParams file.

@MarkB, we must have slightly different configurations. We're running DataStage v8.0.1 on a Redhat Linux server, connecting to DB2 on a zOS mainframe. We need to use the good old DB2 API stages, not the Enterprise stage. So I guess it is something peculiar to either the API stage or to Linux.

Tech Support have tested a solution involving (for Redhat Linux):
1) Patch e128097
2) Patch JR30717
3) Unsetting the APT_OSL_ESC_SQUOTE variable

Gazelle · Post by **Gazelle** » Thu Mar 12, 2009 2:29 pm

We've just installed the patch and confirmed that it fixes the problem.
Rather than assigning $UNSET to the APT_OSL_ESC_SQUOTE variable, we simply don't have it as one of the project environment variables.

manuel.gomez wrote:We are having the same problem here
Can we pick these patches from any IBM site???

I may be wrong, but my understanding is that you need to first log a problem with tech support, then they make the patches available on the IBM ftp server for a limited time.
I do not think the patches are publicly available... which I find a bit odd.

Maybe if enough people pressure IBM, they will make their knowledge base searchable, and their patches readily available.

greggknight · Post by **greggknight** » Mon Mar 16, 2009 10:36 am

Create a parameter set and add a param called (whatever you want )
I use WhereClause

Write you where clause as the value
I use
((BIZ_CURR_REC_FLAG = 'Y' ) OR (BIZ_CURR_REC_FLAG = 'N' AND BIZ_DATE_MODIFIED >= '2005-12-31'))
for one of mine.

In your stage use
select * from #Library#.#TableName# where #WhereClause#

greggknight · Post by **greggknight** » Mon Mar 16, 2009 10:37 am

Create a parameter set and add a param called (whatever you want )
I use WhereClause

Write you where clause as the value
I use
((BIZ_CURR_REC_FLAG = 'Y' ) OR (BIZ_CURR_REC_FLAG = 'N' AND BIZ_DATE_MODIFIED >= '2005-12-31'))
for one of mine.

In your stage use
select * from #Library#.#TableName# where #WhereClause#