Re: Embedded SQL with Coalesce

On 5/29/2012 2:03 PM, Dave Boettcher wrote:
See below.

I have an embedded SQL statement which runs fine in the Navigator Run SQL scripts window and gives me a SQLState of 22018 when I run the RPGIV program that it's embedded in . SQL0420 Message Text: Character in CAST argument not valid. State 22018
The only place I can imagine that I am using CAST is in the coalesce part. I need the coalesce because for the rows with tax credits, there is no taxing entity name. So why does it run in SQL scripts window and not in embedded SQL.
The only difference in the statements from the one below to the embedded one is that I have program variables where indicated.
I know it's long but the statement follows:
SELECT
A.arinst,
A.arsts,
C.PXREC#,
C.PXDATE,
A.arPROP,
A.arTXYR,
A.arJURI,
A.arENTY,
coalesce(B.PPRNAME,'Tax Credit'),

This looks like the most likely culprit to me. B.PPRNAME is probably defined as CHAR, but quoted character strings like 'Tax Credit' are implicitly VARCHAR, so there's an implicit cast being done. See what happens if you rewrite it this way:

coalesce(B.PPRNAME, char('Tax Credit')),

coalesce(B.PPRPHONE,+0),
coalesce(B.PPRphext,+0),
A.arspec,
A.aramtP,
A.arDSCP,
A.arPENP,
A.arINTP
FROM nwstafls.tabill as A
left outer join KCKALMFLS.KCPPRCLINE as B
on ( A.artxyr = B.pprtxyr
and A.arenty = B.pprenty)
join nwstafls.tapadt as C
on A.arprop = C.pxprop
and A.artxyr = C.pxtxyr
and A.arenty = C.pxenty
and A.arinst = C.pxinst
Where
A.arprop = '91-4-122-083-0561' || :RPTPROP
and A.artxyr = 2011 || :RPTTXYR
and A.arsts = 'C'
order by
A.arprop,
A.artxyr,
A.arenty,
A.arinst

Thanks in Advance,

Dave B

Sometimes I get the feeling the whole world is against me, but deep down I know that's not true. Some of the smaller countries are neutral. -- Robert Orben (comedy writer)