Re: SQL CTE in COBOL SQL statement

Could this statement be causing the problem?

WHERE BORR_SSN = Dec(BRQDVA *100,9,0)

Possibly BRQDVA * 100 is too big.

Snippet from the SQL Reference - Decimal scalar function:

An error is returned if the number of significant digits to the left of the
decimal character (the whole part of the number) in string-expression is
greater than p-s.

Regards,

Robert A. Rogerson
Senior Programmer/Analyst
Kohl & Frisch Ltd
Ext. 652
905-660-7622
rrogerson@xxxxxxxxxxxxxxxxx
www.kohlandfrisch.com


This message (including attachments, if any) is confidential, may be
privileged and is intended for the above-named recipient(s) only. If you
have received this message in error, please notify me by return email and
delete this message from your system. Any unauthorized use or disclosure of
this message is strictly prohibited.

-----Original Message-----
From: wdsci-l-bounces@xxxxxxxxxxxx [mailto:wdsci-l-bounces@xxxxxxxxxxxx]
On Behalf Of SamL
Sent: Tuesday, August 14, 2007 9:31 PM
To: 'Websphere Development Studio Client for iSeries'
Subject: Re: [WDSCI-L] SQL CTE in COBOL SQL statement

Sounds maybe like a mismatch between a host variable and the data base
field. Check the job log in case there is more specific detail.

Sam

-----Original Message-----
From: wdsci-l-bounces@xxxxxxxxxxxx [mailto:wdsci-l-
bounces@xxxxxxxxxxxx] On Behalf Of Jim Essinger
Sent: Tuesday, August 14, 2007 8:02 PM
To: Websphere Development Studio Client for iSeries
Subject: [WDSCI-L] SQL CTE in COBOL SQL statement

Ok,

Can a CTE be used in embedded SQL statement in a program? I have a
statement that starts;

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 = 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 progarm, 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 set for a Signed
Packed
Decimal 3 long. I'm not sure what I am being told by the message.

Any thoughts/Help?

Thanks!

Jim
--
This is the Websphere Development Studio Client for iSeries (WDSCI-
L) mailing list
To post a message email: WDSCI-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/wdsci-l
or email: WDSCI-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/wdsci-l.

--
This is the Websphere Development Studio Client for iSeries (WDSCI-L)
mailing list
To post a message email: WDSCI-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/wdsci-l
or email: WDSCI-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/wdsci-l.