RE: Embedded SQL with Coalesce

I've used coalesce in embedded SQL w/o issue.

Random guess: is it that you have a sign on the 0? Try w/o the +?

-Kurt

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Dave Boettcher
Sent: Tuesday, May 29, 2012 4:03 PM
To: RPG programming on the IBM i / System i; Midrange-L
Subject: Embedded SQL with Coalesce


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'),
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)