Count and Hierarchical queries
888443Aug 7 2012 — edited Aug 8 2012Hi Oracle SQL Gurus,
Need your help with getting counts of children for each parent - child relationship using a hierarchical query.
BANNER
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Product
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL VERSION 10.2.0.1.0 - PRODUCTION
Data Set up:
SELECT 1 AS PROJECT_ID, -1 AS PARENT_ID, 1 AS CHILD_ID FROM DUAL
UNION
SELECT 1, 1 , 2 FROM DUAL
UNION
SELECT 1, 2 , 3 FROM DUAL
UNION
SELECT 1, 3 , 4 FROM DUAL
UNION
SELECT 2, -1, 5 FROM DUAL
UNION
SELECT 3, -1, 6 FROM DUAL
UNION
SELECT 3, 6 , 7 FROM DUAL
Expected Output:
SELECT 1 AS PROJECT_ID, -1 AS PARENT_ID, 1 AS CHILD_ID, 4 as EXPECTED_ROW_COUNT FROM DUAL
UNION
SELECT 1, 1 , 2, 3 FROM DUAL
UNION
SELECT 1, 2 , 3, 2 FROM DUAL
UNION
SELECT 1, 3 , 4, 1 FROM DUAL
UNION
SELECT 2, -1, 5, 1 FROM DUAL
UNION
SELECT 3, -1, 6, 2 FROM DUAL
UNION
SELECT 3, 6 , 7, 1 FROM DUAL;
Please help.
Thank you in advance.