RE: Embedded SQL with Coalesce

Dave-

I'm not sure if this is the problem, but looking at your SQL it appears
SQL scripts might be doing an implicit cast on the "+0" portion of the
phone and extension coalesces. Try setting those to just 0 or
encapsulating them in quotes if those are character fields.


-Tom Stieger
IT Manager
California Fine Wire



-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Dave Boettcher
Sent: Tuesday, May 29, 2012 2: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)