Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Select query in cobol db2 pgm returning INCORRECT value

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
faizm

New User


Joined: 13 Apr 2012
Posts: 59
Location: India

PostPosted: Mon Mar 31, 2014 4:03 pm    Post subject: Select query in cobol db2 pgm returning INCORRECT value
Reply with quote

Hi All,
We have a table of around 25 columns. I have written a select query in cobol db2 pgm where in I am trying to select 14 columns. Out of these, the initial 12 columns are returning correct values, however the last 2 column values are not coming correctly and returning some junk values or sometime space. Here is the query:

EXEC SQL
SELECT col1,col2,col3,.......,col 12,col13,col14
INTO :ws-col1,:ws-col2,:ws-col3,....,:ws-col12,:ws-col13,:ws-col14
FROM table1
WHERE col1='12345'
END-EXEC


The length of working storage variable is exactly the same as corresponding column length.

Also, do we need to fetch the column in the order it is defined in table.
Please help on this. Thanks.
Back to top
View user's profile Send private message

enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10201
Location: italy

PostPosted: Mon Mar 31, 2014 4:19 pm    Post subject: Reply to: Select query in cobol db2 pgm returning INCORRECT
Reply with quote

You did not give too much to work on...
and psychic day is wednesday icon_cool.gif

could it be that the last two columns are nullable ?
Back to top
View user's profile Send private message
faizm

New User


Joined: 13 Apr 2012
Posts: 59
Location: India

PostPosted: Mon Mar 31, 2014 4:29 pm    Post subject:
Reply with quote

Hi,

All the columns are NOT NULL.

When I shuffled the order of columns in select query, the value in the col13 and col 14 came correlctly however the col12 returned junk value.

EXEC SQL
SELECT col1,,col13,col14,col2,col3,.......,col 12
INTO :ws-col1,:ws-col13,:ws-col14:ws-col2,:ws-col3,....,:ws-col12
FROM table1
WHERE col1='12345'
END-EXEC
Back to top
View user's profile Send private message
Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1529
Location: Andromeda Galaxy

PostPosted: Mon Mar 31, 2014 4:32 pm    Post subject:
Reply with quote

What is the data type of col13,col14
What happens when you execute the same query in SPUFI and what was the result?
Back to top
View user's profile Send private message
faizm

New User


Joined: 13 Apr 2012
Posts: 59
Location: India

PostPosted: Mon Mar 31, 2014 4:36 pm    Post subject:
Reply with quote

All are CHAR type. When I executed the query in SPUFI it is returning correct values.
Back to top
View user's profile Send private message
Marso

REXX Moderator


Joined: 13 Mar 2006
Posts: 1218
Location: Israel

PostPosted: Mon Mar 31, 2014 6:41 pm    Post subject: Reply to: Select query in cobol db2 pgm returning INCORRECT
Reply with quote

If you say that first col13 and col14 had "junk" but after moving the columns to the beginning they are OK and col12 receive the "junk", I suspect that this "junk" is coming from somewhere else...

1. To be absolutely sure of what you receive in these fields, add a DISPLAY right after checking the SQLCODE.
2. Check the counters for any OCCURS defined before the table in WS. Maybe you're overflowing.
Back to top
View user's profile Send private message
faizm

New User


Joined: 13 Apr 2012
Posts: 59
Location: India

PostPosted: Mon Mar 31, 2014 6:49 pm    Post subject: Reply to: Select query in cobol db2 pgm returning INCORRECT
Reply with quote

Actually, I did a mistake. I defined one of the host variable as group variable. After making it an elementary variable, it worked fine and values are coming correctly. Thank you all for replies.
Back to top
View user's profile Send private message
daveporcelan

Active Member


Joined: 01 Dec 2006
Posts: 645
Location: Pennsylvania

PostPosted: Mon Mar 31, 2014 6:56 pm    Post subject:
Reply with quote

Please show the EXACT SQL statement using code tags.

My guess it has something to do with your syntax (going past col 72).

Try putting each colxx and :colxx on its own line (or two to a line).

Just a guess based on the limited information provided.


Edit:
NEVER MIND, I am too slow....
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 1609
Location: NY,USA

PostPosted: Tue Apr 01, 2014 1:07 am    Post subject:
Reply with quote

Faizm,

Thanks for letting us know, However, were you not seen any warnings/errors during the compilation or in the listings?

for more ref.

https://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=%2Fcom.ibm.db2z10.doc.apsg%2Fsrc%2Ftpc%2Fdb2z_hostvariablecobol.htm

Note: Also a Code Tag can make the post more profressional , hence please make a use of it.
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 -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
This topic is locked: you cannot edit posts or make replies. Db2 SQL query to convert rows into co... subratarec DB2 5 Thu Nov 17, 2016 4:51 pm
This topic is locked: you cannot edit posts or make replies. RANDOM Function in COBOL swapnil781 COBOL Programming 2 Tue Nov 15, 2016 6:17 pm
No new posts Regarding COBOL Stored Procedure opti... selvamsrinivasan85 DB2 4 Fri Nov 04, 2016 8:57 pm
No new posts Two Selects in a single select query Rohit Umarjikar DB2 1 Fri Nov 04, 2016 8:46 pm
No new posts COBOL Version 6.1 Virendra Shambharkar COBOL Programming 5 Tue Nov 01, 2016 11:24 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us