Union all with group by and order by
908418Jan 31 2012 — edited Jan 31 2012Hi ,
I have used union all in the below query. When i'm running this query i'm getting ORA00904 'PRFR_CHILD_iD' invalid indentifier.
Please help me in resolving this.
select REA_AREA_CODE,
AREA_CODE SSA_NAME,
PRFR_OBJECT_ID,
PRFR_NEMS_OBJECT_ID BSC_NAME,
PRFR_CHILD_ID CELL,
sum(COUNTER1) COL1,
SUM(COUNTER2) COL2 from
(
select AREA_AREA_CODE,
AREA_CODE SSA_NAME,
PRFR_OBJECT_ID,
PRFR_NEMS_OBJECT_ID BSC_NAME,
PRFR_CHILD_ID CELL,
sum(COUNTER1) COL1,
SUM(COUNTER2) COL2
FROM ETL_PERF_FACT A , ( select max(prfd_start_time) keep (DENSE_RANK LAST ORDER BY SUM(nvl(counter10,0))) over (PARTITION BY prfd_start_date,PRFR_PARENT_ID ) busy_hr,
A.prfd_start_time,
a.PRFD_START_DATE,
a.PRFR_PARENT_ID
from etl_perf_fact a
WHERE a.rep_id = 370
AND a.prfd_start_DATE >=TRUNC(TO_DATE(:p_start_date,'DD-MON-YYYY HH24:MI') )
AND a.prfd_start_DATE <= TO_DATE(:p_end_date,'DD-MON-YYYY HH24:MI')
AND a.prfd_start_time >=TO_DATE(:p_start_date,'DD-MON-YYYY HH24:MI')
AND a.prfd_start_time <= TO_DATE(:p_end_date,'DD-MON-YYYY HH24:MI')
GROUP BY a.PRFD_START_DATE,
a.PRFR_PARENT_ID,A.prfd_start_time
) B, MVIEW_EQUIPMENT C
WHERE A.REP_ID=347
AND a.prfd_start_DATE >=TRUNC(TO_DATE(:p_start_date,'DD-MON-YYYY HH24:MI') )
AND a.prfd_start_DATE <= TRUNC(TO_DATE(:p_end_date,'DD-MON-YYYY HH24:MI') )
AND A.prfd_start_time >=TO_DATE(:p_start_date,'DD-MON-YYYY HH24:MI')
AND A.prfd_start_time <= TO_DATE(:p_end_date,'DD-MON-YYYY HH24:MI')
AND A.PRFD_START_DATE = B.PRFD_START_DATE
AND A.PRFD_START_TIME = B.PRFD_START_TIME
AND A.PRFR_PARENT_ID = B.PRFR_PARENT_ID
AND A.PRFR_PARENT_ID = C.EQUP_ID
AND B.BUSY_HR = B.PRFD_START_TIME
union all
select AREA_AREA_CODE,
AREA_CODE SSA_NAME,
PRFR_OBJECT_ID,
PRFR_NEMS_OBJECT_ID BSC_NAME,
PRFR_CHILD_ID CELL,
sum(COUNTER1) COL1,
SUM(COUNTER2) COL2
FROM ETL_PERF_FACT A , ( select max(prfd_start_time) keep (DENSE_RANK LAST ORDER BY SUM(nvl(counter10,0))) over (PARTITION BY prfd_start_date,PRFR_PARENT_ID ) busy_hr,
A.prfd_start_time,
a.PRFD_START_DATE,
a.PRFR_PARENT_ID
from etl_perf_fact a
WHERE a.rep_id = 370
AND a.prfd_start_DATE >=TRUNC(TO_DATE(:p_start_date,'DD-MON-YYYY HH24:MI') )
AND a.prfd_start_DATE <= TO_DATE(:p_end_date,'DD-MON-YYYY HH24:MI')
AND a.prfd_start_time >=TO_DATE(:p_start_date,'DD-MON-YYYY HH24:MI')
AND a.prfd_start_time <= TO_DATE(:p_end_date,'DD-MON-YYYY HH24:MI')
GROUP BY a.PRFD_START_DATE,
a.PRFR_PARENT_ID,A.prfd_start_time
) B, MVIEW_EQUIPMENT C
WHERE A.REP_ID=347
AND a.prfd_start_DATE >=TRUNC(TO_DATE(:p_start_date,'DD-MON-YYYY HH24:MI') )
AND a.prfd_start_DATE <= TRUNC(TO_DATE(:p_end_date,'DD-MON-YYYY HH24:MI') )
AND A.prfd_start_time >=TO_DATE(:p_start_date,'DD-MON-YYYY HH24:MI')
AND A.prfd_start_time <= TO_DATE(:p_end_date,'DD-MON-YYYY HH24:MI')
AND A.PRFD_START_DATE = B.PRFD_START_DATE
AND A.PRFD_START_TIME = B.PRFD_START_TIME
AND A.PRFR_PARENT_ID = B.PRFR_PARENT_ID
AND A.PRFR_PARENT_ID = C.EQUP_ID
AND B.BUSY_HR = B.PRFD_START_TIME
)
GROUP BY aREA_AREA_CODE,AREA_CODE,PRFR_OBJECT_ID,PRFR_NEMS_OBJECT_ID,PRFR_CHILD_ID
ORDER BY PRFR_NEMS_OBJECT_ID,PRFR_CHILD_ID