I am trying to use a variable (group variable) in DB2 query whose length varies due to the presence of variable size cobol table defined in it.
Query looks like as given below:
SET LAST_DT = :WS-LAST-DT,
WHERE ITEM_NBR = :WS-ITEM-NBR
AND STORE_NBR IN :WS-TEMP-ARRAY
And, the array is defind as given below:
05 FILLER PIC X(01) VALUE '('.
05 WS-TEMP-ARRAY-STR OCCURS 0 TO 99 TIMES
DEPENDING ON WS-COUNT.
10 WS-TEMP-STR-NBR PIC S9(04) COMP.
10 WS-COMMA PIC X(01).
05 WS-FILLER PIC X(02).
now, e.g. WS-TEMP-STR-NBR is populated with values 100,101,102,103 and 104 and WS_COMMA is populated with ',' for each instance. WS-COUNT is having the value 5. WS-FILLER with value '0)'.
Now, if I compile the program, it shows precompile error as the datatype of the variable is not matching with STORE_NBR (SMALLINT, S9(04) COMP).
if I use it as
AND STORE_NBR IN (WS-TEMP-ARRAY)
then it gives bind error as WS-TEMP-ARRAY is not defind as the table column.
I populated WS-TEMP-ARRAY variable dynamically this in order to update the table in one shot for the same "item number" and "last date" but for differnt store numbers in the input file. Is there any way to use such a dynamically populated variable in the query? Please suggest, if anything can be done in such a case. thanks.