SQL CAST with Common Table Expression

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