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.