Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

DB2 precompile error when using CTE's (WITH clause) in COBOL

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
Satya Poola

New User


Joined: 29 Dec 2010
Posts: 2
Location: Chennai

PostPosted: Thu Dec 30, 2010 11:31 am    Post subject: DB2 precompile error when using CTE's (WITH clause) in COBOL
Reply with quote

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
View user's profile Send private message

sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Thu Dec 30, 2010 9:03 pm    Post subject:
Reply with quote

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
View user's profile Send private message
Satya Poola

New User


Joined: 29 Dec 2010
Posts: 2
Location: Chennai

PostPosted: Fri Dec 31, 2010 9:11 am    Post subject:
Reply with quote

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 icon_smile.gif .
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Need help with below DB2 error ashek15 DB2 3 Sat Mar 18, 2017 5:56 am
No new posts FINDREP Syntax Error seahawk789 DFSORT/ICETOOL 4 Thu Mar 09, 2017 10:11 pm
No new posts INVOKE WEBSERVICE error danik56 CICS 11 Thu Mar 09, 2017 11:52 am
No new posts Row not found Sql error GaganGarg DB2 7 Tue Mar 07, 2017 6:29 am
No new posts SQL query not working in Cobol program. CuriousMainframer COBOL Programming 14 Wed Feb 22, 2017 5:56 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us