|
View previous topic :: View next topic
|
| Author |
Message |
GaganGarg
Active User
Joined: 31 Mar 2010
Posts: 134
Location: India
|
|
|
|
HI,
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:
| Code: |
UPDATE RETAIL_TABLE
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:
| Code: |
01 WS-TEMP-ARRAY.
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
| Code: |
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. |
|
| Back to top |
|
 |
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
i don't remember how a WORKING-STORAGE INLIST needs to be referenced,
but either way:
1. place a colon in front of your host variable (WS-TEMP-ARRAY in your example)
2. WS-FILLER does not do what you think.
In and ODO WS-FILLER's address always starts at what would be the 100th occurance.
i don't think you are building an appropriate literal for the INLIST.
db2 takes the length of the structure referenced in the SQL.
since odo's are compiled with max length.
Large INLIST's should be sorted.
the complete table must be populated with valid data values - even if it is zero.
try removing comma from last item.
oh, and get rid of the ( and ). |
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
in your sql you may have to reference a structure
that starts with a comp-3 field,
and ends with a comp-3 field,
not a space.
(and yes, each comp-3 field is separated by a comma) |
|
| Back to top |
|
|
|
|
