Check the value of SQLERMC (or SQLERRMC can't remember which one). It will
usually tell you what field it's having trouble casting.
--
Michael Schutte
Admin Professional
Try Bob Evans GRILLING SAUSAGE! This summerâs hottest destination is your
own backyard with Bob Evans Brats and Italian Sausage! For tasty recipes
using Bob Evans grilling sausage, visit
http://www.BobEvans.com/Recipes
"Dennis Lovelady"
<iseries@lovelady
.com> To
Sent by: "'RPG programming on the IBM i /
rpg400-l-bounces@ System i'" <rpg400-l@xxxxxxxxxxxx>
midrange.com cc
Subject
09/04/2009 07:59 SQL CAST with Common Table
AM Expression
Please respond to
RPG programming
on the IBM i /
System i
<rpg400-l@midrang
e.com>
Hi, Folks:
I have the following code in an ILE RPG program at V5R4, and it fails at
the
FETCH with an SQL0420 error (Character in CAST argument not valid). I have
added the CAST() functions as you see here in an effort to eliminate the
error, but no go. I am sure that I'm overlooking something, but it evades
me completely. Can you help? I believe that everything pertinent to this
question is below.
<snip>
P topDefects B
D topDefects PI
D fromDate D Value
D toDate D Value
D department S 64 Varying
D deptSeq S 10I 0
D rank S 5U 0
D reason S 32 Varying
D totQty S 10I 0
<snip>
/Free
GatherStats('Processing: "Top Defects"') ;
<snip>
Exec SQL DECLARE topDefCursor03 CURSOR FOR
WITH ranker AS (
SELECT REASON, SUM(QUANTITY) as WRKQTY
, row_number() over(ORDER BY SUM(quantity) DESC) as RANK
FROM BRKDATA
WHERE data_date BETWEEN :fromDate AND :toDate
AND department > ' '
GROUP BY REASON)
, summary as (
SELECT DEPTSEQ, REASON, SUM(QUANTITY) AS totQty
FROM BRKDATA
WHERE data_date BETWEEN :fromDate AND :toDate
GROUP BY REASON, DEPTSEQ)
SELECT CAST(R.RANK AS INT)
, CAST(D.COLNBR AS INT)
, CAST(S.REASON AS VARCHAR(32))
, CAST(S.totQty AS INT)
FROM summary S
JOIN ranker R ON S.REASON = R.REASON
JOIN QTEMP/TOPDEPTS D on S.DEPTSEQ = D.DEPTSEQ
WHERE R.RANK <= 75
ORDER BY R.RANK
;
checkSQL(EOF_OK: SQL_ABORT: 'topDefCursor03') ;
Exec SQL OPEN topDefCursor03 ;
checkSQL(EOF_OK: SQL_ABORT: 'topDefCursor03-A') ;
Exec SQL FETCH topDefCursor03 INTO :colNbr, :reason, :totQty ;
DoW SQLCOD = *Zero ;
<snip>
Exec SQL FETCH topDefCursor03 INTO :colNbr, :reason, :totQty ;
EndDO ;
checkSQL(EOF_OK: SQL_ABORT: 'topDefCursor03-B') ;
Exec SQL CLOSE topDefCursor03 ;
<snip>
Thanks,
Dennis E. Lovelady
AIM: delovelady MSN: fastcounter@xxxxxxxxxxxx
<
http://www.linkedin.com/in/dennislovelady>
www.linkedin.com/in/dennislovelady --
"Opportunity is missed by most people because it is dressed in overalls and
looks like work."
- Thomas Edison
--
This is the RPG programming on the IBM i / System i (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at
http://archive.midrange.com/rpg400-l.
midrange.com
