Re: SQL CTE in COBOL SQL statement

["Rob Rogerson" <rrogerson@xxxxxxxxxxxxxxxxx>]

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.