Select query in cobol db2 pgm returning INCORRECT value

faizm · New User Joined: 13 Apr 2012 Posts: 59 Location: India

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.

enrico-sorichetti · Posted: Mon Mar 31, 2014 4:19 pm

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

could it be that the last two columns are nullable ?

faizm · New User Joined: 13 Apr 2012 Posts: 59 Location: India

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

Pandora-Box · Posted: Mon Mar 31, 2014 4:32 pm

What is the data type of col13,col14
What happens when you execute the same query in SPUFI and what was the result?

faizm · New User Joined: 13 Apr 2012 Posts: 59 Location: India

All are CHAR type. When I executed the query in SPUFI it is returning correct values.

Marso · Posted: Mon Mar 31, 2014 6:41 pm

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.

faizm · New User Joined: 13 Apr 2012 Posts: 59 Location: India

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.

daveporcelan · Posted: Mon Mar 31, 2014 6:56 pm

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....

Rohit Umarjikar · Posted: Tue Apr 01, 2014 1:07 am

Faizm,

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

for more ref.

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.