MVSFORUMS.com

[mnandakumar]

Suggest me a way in COBOL to add dynamically values to IN Clause in sql.


Example :
During one run of the program the IN Claouse in sql may look like this
IN('IPC1','IPC2','IPC3')

this may change as
IN('IPC1','IPC2','IPC3','IPC4','IPC5')

Dynamic sql should not be used.

Please suggest me a way

Advance thannks.

[vivek1983]

mnandakumar,

Can you tell me how those values comes in the first place? Are the values from any file? How many values maximum is expected in the IN clause?
_________________
Vivek G
--------------------------------------
A dream is just a dream. A goal is a dream with a plan and a deadline. (Harvey Mackay)

[sriramla]

You can try the following couple of suggestions:

1. If the IN clause values are always going to be sequential as given in your example (IPC1 thru IPC3 or IPC1 thru IPC5) then replace the IN clause with BETWEEN clause.

2. If they are not always sequential then identify the maximum number of values you expect. Lets say its 10. Then have 10 host variables in your query and value them appropriately. Note that you need to initialize the remaining host variables to a non-existing value in the database to avoid pulling incorrect records from the query.

[jsharon1248]

A different option is to utilize a declared temporary table. There are no special authorizations required to create a declared temporary table. Create the table in the COBOL program, insert the values into the table, and modify your SQL to join to the table. Click on the Quick Manuals link at the top of all the pages in this forum, open the DB2 UDB for Z/OS SQL Reference and review the DECLARE GLOBAL TEMPORARY TABLE statement for more details. For 5 or 10 items, you'd probably want to use host variables, but if you have more than that or just plain don't know, the declared temporary table is a good option.

[jsharon1248]

Clarification: for your purposes, code the IN predicate with a non-correlated sub-query to obtain the list.