Skip to Main Content

SQL Developer

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

SQL Developer 4.1.0.19 and WITH clause; can't execute queries

Loris Medici .AKTEK.Jun 15 2015 — edited Jun 16 2015

Hi all, I have trouble in executing a statement in the SQL Worksheet window of SQL Developer:

WITH COST_CENTERS(COST_CENTER_LEVEL,

                  COST_CENTER_ID,

                  COST_CENTER_CODE,

                  COST_CENTER_NAME,

                  PARENT_COST_CENTER_ID) AS (

  SELECT 1 AS COST_CENTER_LEVEL,

         PCC.COST_CENTER_ID,

         PCC.COST_CENTER_CODE,

         PCC.COST_CENTER_NAME,

         PCC.PARENT_COST_CENTER_ID

    FROM XXPN.XXPN_COST_CENTERS PCC

    WHERE PARENT_COST_CENTER_ID IS NULL

  UNION ALL

  SELECT COST_CENTERS.COST_CENTER_LEVEL + 1 AS COST_CENTER_LEVEL,

         CCC.COST_CENTER_ID,

         CCC.COST_CENTER_CODE,

         CCC.COST_CENTER_NAME,

         CCC.PARENT_COST_CENTER_ID

    FROM COST_CENTERS,

         XXPN.XXPN_COST_CENTERS CCC

    WHERE CCC.PARENT_COST_CENTER_ID = COST_CENTERS.COST_CENTER_ID)

  SELECT COST_CENTER_LEVEL,

         COST_CENTER_ID,

         COST_CENTER_CODE,

         COST_CENTER_NAME,

         PARENT_COST_CENTER_ID

    FROM COST_CENTERS

    ORDER BY COST_CENTER_LEVEL,

             COST_CENTER_CODE ;

The same code executes w/o any problem in SQL*Plus or TOAD. (I've tried to execute the statement both by clicking "Run Statement" and "Run Script buttons", both return with an error...)

Could it be that SQL Developer can't parse WITH statements?

Thanks in advance and regards,

Loris.

This post has been answered by Loris Medici .AKTEK. on Jun 16 2015
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jul 14 2015
Added on Jun 15 2015
17 comments
2,698 views