× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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)






As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.