Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
How to use a dynamically created variable in DB2 query

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> COBOL Programming
View previous topic :: :: View next topic  
Author Message
GaganGarg

Active User


Joined: 31 Mar 2010
Posts: 134
Location: India

PostPosted: Thu Dec 22, 2011 7:02 pm    Post subject: How to use a dynamically created variable in DB2 query
Reply with quote

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
View user's profile Send private message

dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6968
Location: porcelain throne

PostPosted: Thu Dec 22, 2011 7:48 pm    Post subject:
Reply with quote

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
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6968
Location: porcelain throne

PostPosted: Thu Dec 22, 2011 7:59 pm    Post subject:
Reply with quote

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
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> COBOL Programming All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts HEX value search in a DB2 query maxsubrat DB2 2 Wed Oct 04, 2017 3:04 pm
No new posts Create procedure issues -628 when add... chandraBE DB2 1 Mon Sep 18, 2017 12:16 pm
No new posts Moving values to a variable of copybo... Vignesh Sid COBOL Programming 6 Wed Sep 06, 2017 1:04 pm
No new posts Updating the Trailer count in variabl... satheshbabur DFSORT/ICETOOL 6 Wed Aug 30, 2017 9:49 pm
No new posts Can we limit length in concatenation ... balaji81_k DB2 7 Tue Aug 22, 2017 2:50 am

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us