SQL & PL/SQL

Hi,

I found same issue in a metalink thread which is addressed in Oracle 12.1 which doesn't help me now. I need to rewrite the below query. Does anyone have any suggestions how to go about it?

thanks,

April

working in Oracle 11.2.0.3.1, windows server, doing an upgrade from 10g to 11g. Piece of code is failing with following error via TOAD

Oracle Database Error Code ORA-22902 Description :
CURSOR expression not allowed
Error Cause:
CURSOR on a subquery is allowed only in the top-level SELECT list of a query.

The below code is returning a tree of data for projects, units within the buildings.


Code as follows:

SELECT LEVEL
, p.project_id
, '['
|| NVL (p.file_ref, ' ')
|| '] ['
|| NVL (p.project_ref, ' ')
|| '] '
|| p.project_name
|| ' ( '
|| (SELECT COUNT (*)
FROM PROJECT p1
WHERE p1.parent_project_id = p.project_id)
|| ' sub-projects, '
|| (SELECT COUNT (*)
FROM PROJECT_ELEMENT pe2
WHERE pe2.project_id = p.project_id)
|| ' elements)' AS project_description
,
CURSOR
(SELECT pe.element_id
, '['
|| pe.element_ref
|| '] '
|| pe.element_name
|| ' ('
|| pe.unit_count
|| ')' AS element_description
,

CURSOR
(SELECT hu.hu_id
, '['
|| hu.hu_ref
|| '] '
|| CASE
WHEN hu.bedroom_count IS NOT NULL
THEN ', Bedrooms: ' || hu.bedroom_count
ELSE NULL
END
|| CASE
WHEN hu.bedspace_count IS NOT NULL
THEN ', Bedspaces: ' || hu.bedspace_count
ELSE NULL
END AS hu_descripton
FROM HOUSING_UNIT hu
WHERE hu.element_id = pe.element_id
ORDER BY hu.hu_ref
) AS housing_units

FROM PROJECT_ELEMENT pe
WHERE pe.project_id = p.project_id
ORDER BY pe.element_ref, pe.element_name
) elements
FROM PROJECT p
START WITH p.project_id = l_root_project_id

CONNECT BY PRIOR p.project_id = p.parent_project_id -- connect by used with LEVEL keyword

ORDER SIBLINGS BY p.file_ref DESC
, p.project_ref DESC
, p.project_name DESC;

Edited by: AprilM on Jul 17, 2012 10:28 AM