|
View previous topic :: View next topic
|
| Author |
Message |
Satya Poola
New User
Joined: 29 Dec 2010
Posts: 2
Location: Chennai
|
|
|
|
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 |
|
| Back to top |
|
 |
sushanth bobby
Senior Member
Joined: 29 Jul 2008
Posts: 1020
Location: India
|
|
|
|
Hi Satya,
Welcome to IBMMAINFRAMES!
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.
***********************************************************
|
Thanks,
Sushanth |
|
| Back to top |
|
|
Satya Poola
New User
Joined: 29 Dec 2010
Posts: 2
Location: Chennai
|
|
|
|
HI Sushanth,
Thanks for the welcome.
I'm having the DECLARE CURSOR statement after the CTE ( i.e the CTE is not in the CURSOR) that is the reason I'm getting precompiler error.
Now it is resolved. Thanks a lot for immediate help  . |
|
| Back to top |
|
|
|
|
  |
All times are GMT + 6 Hours |
|
