Re: SQL Statement failing in COBOL program -- COBOL400-L

All,

Ok - here is the solution. Count the variables correctly and things will
work. I had repeated one field in the declare cursor statement, and when I
removed the duplicate field, I get the result that I desired. It sure helps
when SQL can match up the number of variables!

On comparing the character version of a number with an actual number field,
I found that

cast(1234567.89 as Char(10))

leaves the decimal in place. What I needed was the entire number without
the decimal.

replace(1234567.89,".","")
or
digits(1234567..89)

seems to do what I need in one step. The result is a valid compare with the
character number "123456789".

Thanks for all your help! I am learning a lot about this SQL stuff.

On 8/22/07, Jim Essinger <dilbernator@xxxxxxxxx> wrote:

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