1 2 Previous Next 17 Replies Latest reply on Jun 16, 2015 9:45 AM by thatJeffSmith-Oracle

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

    Loris Medici .AKTEK.

      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.

        1 2 Previous Next