Hi,
I need to use the following query for hierarchial retrieval
WITH AGCY_LVL (TAX_AGCY,TAX_AGCY_PRNT,LEVEL) AS
(
SELECT TAX_AGCY,TAX_AGCY_PRNT,0
FROM TAX_AGECNCY
WHERE TAX_AGCY = 'PRN'
UNION ALL
SELECT B.TAX_AGCY, B.TAX_AGCY_PRNT, LEVEL + 1
FROM AGCY_LVL A , TAX_AGECNCY B
WHERE A.TAX_AGCY_PRNT = B.TAX_AGCY
AND A.LEVEL < 10
)
SELECT * FROM AGCY_LVL
WHERE TAX_AGCY <> TAX_AGCY_PRNT
ORDER BY LEVEL DESC
This worked fine in SPUFI and QMF and got the result.
But when I tried to include the same in COBOL program, during the DB2 precomplie getting ERROR
DSNH199I E DSNHLEXC LINE 524 COL 17 INVALID KEYWORD "WITH"; VALID SYMBOLS ARE: INCLUDE DECLARE BEGIN END
Can anyone please help me resolve this. Thanks in advance
I did not have any problem compililng and executing a program having CTE.
My cursor looked like,
Code:
************************************************************
CURSOR DECLARATION
************************************************************
EXEC SQL
DECLARE ECURSOR CURSOR FOR
WITH R(EMPNO,FIRSTNME,MIDINIT,LASTNAME,HIREDATE,JOB,
SALARY,BONUS,COMM) AS (
SELECT EMPNO,FIRSTNME,MIDINIT,LASTNAME,HIREDATE,JOB,
SALARY,BONUS,COMM
FROM BOBT1.EMPLOYEE
WHERE SEX='M'
)
SELECT EMPNO,FIRSTNME,MIDINIT,LASTNAME,HIREDATE,JOB,
SALARY,BONUS,COMM
FROM R
END-EXEC.
***********************************************************
.