Re: SQL CTE in COBOL SQL statement

["SamL" <lennon_s_j@xxxxxxxxxxx>]

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.