Re: Embedded SQL with Coalesce

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

---
To avoid the VARCHAR and certain other results I don't like when using a character string like that, I cast it specifically this way, with the exact length I want. For example, if B.PPRNAME has a length of 20, I might do this:

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



On 5/29/12 5:23 PM, Jonathan Ball wrote:

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)