Re: SQL Statement failing in COBOL program

In your WS-SQL-Fetch you declare 13 host variables an in dyour SELECT
DISTINCT... you have 14 fields - the table field CAR_RTE_CD occures
twice.
Then the selected fields do not match the host variables. Maybe, this is
your problem ...?




"Jim Essinger" <dilbernator@xxxxxxxxx>
Sent by: cobol400-l-bounces@xxxxxxxxxxxx
23.08.2007 00:20
Please respond to
COBOL Programming on the iSeries/AS400 <cobol400-l@xxxxxxxxxxxx>


To
"COBOL Programming on the iSeries/AS400" <cobol400-l@xxxxxxxxxxxx>
cc

Subject
Re: [COBOL400-L] SQL Statement failing in COBOL program






Tom,

Here are the relevant parts of the SQLCBLLE source;
------------------------------ Start of source copy
----------------------------

Working storage.

01 WS-SQL-Fetch.
05 SQL-Zip-Code Pic X(05).
05 SQL-Plus-Four Pic X(04).
05 SQL-Car-Rte-Cd Pic X(04).
05 SQL-Plus-Six Pic X(06).
05 SQL-Page-Info Pic X(6006).
05 SQL-Addr-Line1 Pic X(38).
05 SQL-Addr-Line2 Pic X(38).
05 SQL-Addr-Line3 Pic X(38).
05 SQL-Addr-Line4 Pic X(38).
05 SQL-Borr-Name Pic X(38).
05 SQL-Borr-SSN Pic X(09).
05 SQL-Rtn-Codes Pic X(03).
05 SQL-Bond-Number Pic S9(03).

Procedure division.

Exec SQL
Declare LTRC1 cursor for

with t1 as (Select distinct lnbss, lnbond
from slsfiles/sllnrep
WHERE lnlsts < "P90" )
select
Distinct ZIP_CODE,
PLUS_FOUR,
CAR_RTE_CD,
CAR_RTE_CD,
PLUS_SIX,
PAGE_INFO,
ADDR_LINE1,
ADDR_LINE2,
ADDR_LINE3,
ADDR_LINE4,
BORR_NAME,
BORR_SSN,
RTN_CODES,
LNBOND
from BCTEST/BCSRREP,
SLSFILES/SLBRREP Join T1 on BrBSS = LNBSS
WHERE BORR_SSN = replace(replace(BRQDVA,",",""),".","")
Order by LnBond, Zip_Code
END-EXEC

Exec SQL Open LtrC1 End-Exec

*---------------------------------------------------------------------*
* Get the next record

*---------------------------------------------------------------------*
exec sql
fetch ltrc1 into :WS-SQL-Fetch
end-exec

------------------ End Source copy -----------------------------
Note; All fields selected except the BOND number are stored in character
format in the files. Not my design, just what I have to work with. The
BRQDVA field is stored as a packed number in 9999999.99 format. I have to
compare with a character "999999999" field. I adapted some suggestions to
come up with the replace in the WHERE clause.

The select statement works in STRSQL and iNav Run SQL Scripts with no
errors I thought it was the host variables, but they match what has
worked
in other SQLCBBLLE programs I have done.


On 8/22/07, Tom Liotta <qsrvbas@xxxxxxxxxxxx> wrote:

Jim Essinger wrote:

Going from memory, as I am not at my work computer. 99% sure that the
following is true.

Declare the cursor with the select statement listed earlier,
I open the cursor, which has an SQLSTATE and SQLCODE of 00000
I then fetch using the cursor name, that is when I get the error with

no

data.

Jim:

[x-posted to COBOL400-L, I hope; please reply there]

When you have it available, can you show the FETCH? Since the error
occurs at that time, perhaps that's worth seeing. (And if you found
the problem already, can we hear the resolution?)

Part of the problem seems related to "Ran the statement in iNav
scripts" although I'm not clear how iNav handles DECLARE CURSOR.
When you use embedded FETCH for the noted SELECT, you necessarily
add the complexity of host variables.

Tom Liotta

On 8/20/07, Tom Liotta

<qsrvbas-VsqqI1RANlHk1uMJSBkQmQ@xxxxxxxxxxxxxxxx>

wrote:

Jim Essinger wrote:

Ran the statement in iNav scripts - no error, returned the data I

expected.

As a work around I used the statement to create a file using STRSQL,
put the code in a QMQry as an insert, and insert the records into a
temp table with a CL wrapper. I then open the temp file with the
COBOL program to process. That's the long way around! The temp file
does not show any data problems.

Jim:

You've shown the DECLARE CURSOR, but I don't recall seeing what you
actually _do_ with the CURSOR in your COBOL program... ?

--
Tom Liotta
The PowerTech Group, Inc.
19426 68th Avenue South
Kent, WA 98032
Phone 253-872-7788 x313
253-479-1416
Fax 253-872-7904
http://www.powertech.com