Re: SQL Statement failing in COBOL program

Tom,

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
On 8/20/07, Tom Liotta <qsrvbas@xxxxxxxxxxxx> 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

On 8/17/07, Wilt, Charles <WiltC@xxxxxxxxxx> wrote:

Hm, that's strange...

1) Try run SQL scripts from ops-nav

2) Try outputting the results from STRSQL into a file, see anything

fishy?

-----Original Message-----

Thanks for the reply. The job log tells me nothing. It fails
on the first fetch. The result is a small set, usually
between 20 and 500 records depending on the day. I have paged
through all the records in the STRSQL execution of the statement.

On 8/17/07, Wilt, Charles <WiltC@xxxxxxxxxx> wrote:

The system's telling you the problem. I'm guessing you've

got a data

record somewhere with a bad value in BRQDVA. Look for a

prior message

in the job log that tells you which record.

Or add some code in your program to tell you the last

record you got

successfully.

STRSQL may be working because it hadn't hit the bad record

yet, unless

you've paged through all the records.

-----Original Message-----

I asked this in the WDSc list, but none of the responses

helped me

find the problem. I have the following statement;

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 Dec(BORR_SSN,9,0) = Dec(BRQDVA *100,9,0)
Order by LnBond, Zip_Code
END-EXEC

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