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

Hey Jon,

That is what I thought when I first looked at the suggestion. Funny thing
is, it works. Not at the office now, but I think if you display the number
on the screen in a select statement, you see the decimal point and comma.
SQL edits the number for you. I've run into that before. The replace as
coded makes it a good comparison in STRSQL, and I get the resulting set I
want. When I was testing in STRSQL I used a statement like this;

WHERE BORR_SSN = Char(replace(replace(BRQDVA,",",""),".",""),9)

just to make sure the comparison was between like field types. I will check
tomorrow at work, and post my results.

Someone else pointed out to me, off line, that I had 14 fields in the
declare cursor statement, but only 13 in my working storage. I may have
accidentally copied the carrier Route code field twice. I don't know if
that is in my code, or just my lame attempt at posting an example. That
would cause problems, and I will test tomorrow when I get to work. I think
they were trying to spare me some embarrassment for a stupid keying error.
At this point I am beyond being embarrassed for such things. My eyes ain't
what they used to be!

Jim

On 8/22/07, Jon Paris <Jon.Paris@xxxxxxxxxxxxxx> wrote:

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
Partner400

www.Partner400.com

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

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

--
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,
visit: http://lists.midrange.com/mailman/listinfo/cobol400-l
or email: COBOL400-L-request@xxxxxxxxxxxx Before posting,
please take a moment to review the archives at
http://archive.midrange.com/cobol400-l.

--
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,
visit: http://lists.midrange.com/mailman/listinfo/cobol400-l
or email: COBOL400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/cobol400-l.