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

Jim,

Just making a (very) wild guess, I wonder if it couldn't be
an "environment" issue. Something in the line of , for
example, using a comma as a decimal point and COBOL
expecting a period.

I would check the differences between the STRSQL
environment and the Create COBOL stmt.

Regards,

Luis Rodriguez

--- midrange-l-request@xxxxxxxxxxxx wrote:

------------------------------

message: 8
date: Fri, 17 Aug 2007 13:10:44 -0600
from: "Jim Essinger" <dilbernator@xxxxxxxxx>
subject: Re: SQL Statement failing in COBOL program

Brian,

Thanks for the idea. It would be best to avoid the
decimal conversion. I
have implemented your suggestion. It did not fix the
problem with the
embedded statement, but is a good suggestion.

Jim

On 8/17/07, Brian Johnson <brian.johnson.mn@xxxxxxxxx>
wrote:

I agree with Wilt; the error seems to indicate data

value that cannot

be converted to decimal. Why you don't get the error

in STRSQL is

unexplained, though.

You could avoid the always dangerous char-to-decimal

conversion with

something like:

WHERE BORR_SSN = replace(replace(BRQDVA, ',', ''), '.',

'')

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

Charles,

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.

Jim

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

Jim,

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.

HTH,
Charles

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On

Behalf Of Jim Essinger

Sent: Friday, August 17, 2007 12:29 AM
To: Midrange Systems Technical Discussion
Subject: SQL Statement failing in COBOL program

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

The select statement works in STRSQL and selects

the records

I want to process. When I run it in the program,

the open

cursor statement completes normally, but the

fetch gets me a

SQLSTATE of 22018 with a SQLCODE of -420.
The code book says;

"The character value for the CAST, DECIMAL,

FLOAT, or INTEGER

scalar function is invalid."

Every column selected is alpha except the LNBOND

which is a

packed decimal 3 long in the file. I have the

receiving

field for the LNBOND field set as a Signed Packed

Decimal 3

long. BRQDVA in the "WHERE" clause is a number

in

9,999,999.99 format, and so to compare with the

account

number (not really SSN, but was at one time) I

have to move

decimal to the right like 999,999,999. The

BORR_SSN is

actually a number, just stored as character.

This statement happens to be in ILE COBOL SQL,

but the

question is more SQL based then program based.

Also I know

that in this example, taken from the program the

constant

'P90' has double quotes, required by COBOL, and

not single as

required by STRSQL.

I'm not sure what I am being told by the message.

Any thoughts/Help?

Thanks!

Jim
--

Luis Rodriguez

IBM Certified Systems Expert
eServer i5 iSeries Technical Solutions