Maybe I'm just missing something - but how can you replace characters in a
packed field? It doesn't have any.

As to the compare - are you looking for an exact match with the SSN field?
In which case are you implying .00 after that last character or are the last
two characters considered to be the decimal places. Either way in order to
compare the two you're either going to have to cast the character to numeric
or the numeric to character as a minimum.

Jon Paris

-----Original Message-----
From: cobol400-l-bounces@xxxxxxxxxxxx
[mailto:cobol400-l-bounces@xxxxxxxxxxxx] On Behalf Of Jim Essinger
Sent: Wednesday, August 22, 2007 6:20 PM
To: COBOL Programming on the iSeries/AS400
Subject: Re: [COBOL400-L] SQL Statement failing in COBOL program


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" )
Distinct ZIP_CODE,
Order by LnBond, Zip_Code

Exec SQL Open LtrC1 End-Exec

* Get the next record

exec sql
fetch ltrc1 into :WS-SQL-Fetch

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


[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
Jim Essinger wrote:

Ran the statement in iNav scripts - no error, returned
the data I
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.

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
Fax 253-872-7904

This is the COBOL Programming on the iSeries/AS400
(COBOL400-L) mailing list To post a message email:
COBOL400-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change
list options,
or email: COBOL400-L-request@xxxxxxxxxxxx Before posting,
please take a moment to review the archives at

As an Amazon Associate we earn from qualifying purchases.

This thread ...


Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2022 by and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.