SQL & PL/SQL

HI All,

When i ran the below query i got this error.

ORA-01785: ORDER BY item must be the number of a SELECT-list expression

Please help to resolve this.

Thanks in advance
Regards,
Aparna S


Query---- >


select AREA_AREA_CODE,
TO_CHAR(A.prfd_start_time) grpCol1,
--&P_AGG(TO_CHAR(A.prfd_start_time, :QRY4)&P_MIN) grpCol2,
AREA_CODE SSA_NAME,
LAC.PRFR_OBJECT_ID,
A.PRFR_NEMS_OBJECT_ID BSC_NAME,
A.PRFR_CHILD_ID CELL,
A.COUNTER40 COLUMN_1,-- Call_volume
A.COUNTER42 COLUMN_2,--Tch_traffic_erl
A.COUNTER45/NULLIF(A.COUNTER45,0)*(1-A.COUNTER32/NULLIF(A.COUNTER36,0))*(A.COUNTER19/NULLIF(A.COUNTER17,0))*100 COLUMN_3,--cssr
A.COUNTER39 / NULLIF(A.COUNTER38,0) *100 COLUMN_4,--TCH_Blocking_Rate
A.COUNTER43 COLUMN_5,--Max_TCH_Available_Cell
A.COUNTER47 COLUMN_6,--MAX TCH Used_FR
A.COUNTER48 COLUMN_7,--MAX TCH Used_HR
nvl(A.COUNTER47,0)+nvl(A.COUNTER48,0) COLUMN_8,--MAX TCH Used_Total
(A.COUNTER35/NULLIF(A.COUNTER34,0))*100 COLUMN_9,--SDCCH_Block_Rate
A.COUNTER37 COLUMN_10,--MAX SDCCH Available
A.COUNTER46 COLUMN_11,--MAX SDCCH Used
(nvl(A.COUNTER1,0)+nvl(A.COUNTER2,0)+nvl(A.COUNTER3,0)+nvl(A.COUNTER4,0)+nvl(A.COUNTER5,0)+nvl(A.COUNTER6,0)+nvl(A.COUNTER7,0)+nvl(A.COUNTER8,0)+nvl(A.COUNTER9,0)+nvl(A.COUNTER10,0)+nvl(A.COUNTER11,0))/(NULLIF(A.COUNTER17,0))*100 COLUMN_12,--Assign_Fail_Ratio_Cell
A.COUNTER33/NULLIF(( nvl(A.COUNTER40,0)+nvl(A.COUNTER41,0) ),0)*100 COLUMN_13,--Call_Drop_Rate_Cell
(nvl(A.COUNTER21,0)+ nvl(A.COUNTER23,0) + nvl(A.COUNTER28,0) )/NULLIF(( nvl(A.COUNTER20,0) + nvl(A.COUNTER22,0) + nvl(A.COUNTER27,0) ),0) *100 COLUMN_14,--HO_Failure_Ratio_Cell1V1
( nvl(A.COUNTER24,0)+ nvl(A.COUNTER26,0) + nvl(A.COUNTER29,0)+ nvl(A.COUNTER31,0) ) / NULLIF(( nvl(A.COUNTER22,0) + nvl(A.COUNTER25,0) + nvl(A.COUNTER27,0) + nvl(A.COUNTER30,0) ),0) *100 COLUMN_15,--HO_Success_Rate_Cell
A.COUNTER49 COLUMN_16,
(A.COUNTER14/ NULLIF(( nvl(A.COUNTER14,0)+ nvl(A.COUNTER13,0) ),0))*100 COLUMN_17,
A.COUNTER12-120 COLUMN_18,
A.COUNTER16 COLUMN_19,
A.COUNTER15 COLUMN_20,
A.COUNTER54 COLUMN_21,
A.COUNTER51 COLUMN_22,
A.COUNTER53 COLUMN_23,
A.COUNTER50 COLUMN_24,
nvl(A.COUNTER55,0)+nvl(A.COUNTER52,0) COLUMN_25,
(A.COUNTER42/L.ERLANG_VALUE)*100 COLUMN_26, --CALCULATION MAY DIFFER BECAUSE IN REPORT WE DISPLAY ROUND(VALUE,2) OF K3014-COUNTER42 EX: 0.006 TO 0.01
LAC.C_COUNTER2 COLUMN_27,
LAC.COUNTER3 COLUMN_28,
LAC.COUNTER4 COLUMN_29,
LAC.COUNTER5 COLUMN_30,
LAC.COUNTER7 COLUMN_31,
LAC.COUNTER8 COLUMN_32,
LAC.COUNTER9 COLUMN_33
FROM (
SELECT * FROM
(SELECT PRFD_START_TIME, PRFD_START_DATE, PRFR_OBJECT_ID, PRFR_CHILD_ID, PRFR_PARENT_ID, PRFR_NEMS_OBJECT_ID, PRFR_NEMS_PARENT_ID,
COUNTER1, COUNTER2, COUNTER3, COUNTER4, COUNTER5, COUNTER6, COUNTER7, COUNTER8, COUNTER9, COUNTER10, COUNTER11, COUNTER12, COUNTER13, COUNTER14, COUNTER15,
COUNTER16, COUNTER17, COUNTER18, COUNTER19, COUNTER20, COUNTER21, COUNTER22, COUNTER23, COUNTER24, COUNTER25, COUNTER26, COUNTER27, COUNTER28, COUNTER29, COUNTER30,
COUNTER31, COUNTER32, COUNTER33, COUNTER34, COUNTER35, COUNTER36, COUNTER37, COUNTER38, COUNTER39, COUNTER40, COUNTER41, COUNTER42,
MAX(COUNTER42) OVER (PARTITION BY PRFD_START_DATE, PRFR_OBJECT_ID, PRFR_CHILD_ID) M_COUNTER42,
COUNTER43, COUNTER44, COUNTER45, COUNTER46, COUNTER47, COUNTER48, COUNTER49, COUNTER50, COUNTER51, COUNTER52, COUNTER53, COUNTER54, COUNTER55
FROM ETL_PERF_FACT_LARGE
WHERE REP_ID=362
AND prfd_start_date >= TRUNC(TO_DATE(:p_start_date,'DD-MON-YYYY HH24:MI') )
AND prfd_start_date <= TRUNC(TO_DATE(:p_end_date,'DD-MON-YYYY HH24:MI') )
AND prfd_start_time >=TO_DATE(:p_start_date,'DD-MON-YYYY HH24:MI')
AND prfd_start_time <= TO_DATE(:p_end_date,'DD-MON-YYYY HH24:MI')
--AND PRFR_CHILD_ID='102_4401_A_NARAYANAPURAM_AT2212,CellID=4401'
) WHERE COUNTER42=M_COUNTER42
) A, MVIEW_EQUIPMENT, ETL_LOOKUP L , --ETL_PERF_FACT LAC
( SELECT LA.* FROM ETL_PERF_FACT LA, MVIEW_EQUIPMENT WHERE LA.REP_ID=366
AND LA.prfd_start_date >= TRUNC(TO_DATE(:p_start_date,'DD-MON-YYYY HH24:MI') )
AND LA.prfd_start_date <= TRUNC(TO_DATE(:p_end_date,'DD-MON-YYYY HH24:MI') )
AND EQUP_ID=LA.PRFR_PARENT_ID
-- &P_EQUPIDLIST &QRY &QRY1
) LAC
WHERE A.prfd_start_date = LAC.prfd_start_date(+)
AND A.PRFR_PARENT_ID=LAC.PRFR_PARENT_ID(+)
AND A.PRFR_OBJECT_ID = LAC.PRFR_OBJECT_ID(+)
AND ROUND(A.COUNTER43) = L.OFFERED_ERLANG(+)
AND EQUP_ID=A.PRFR_PARENT_ID
--&P_EQUPIDLIST
----&QRY
--&QRY1
/*GROUP BY AREA_AREA_CODE, TO_CHAR(prfd_start_time,:QRY3) ,&P_AGG(TO_CHAR(prfd_start_time, :QRY4)&P_MIN) ,
AREA_CODE ,PRFR_OBJECT_ID,PRFR_NEMS_OBJECT_ID,PRFR_CHILD_ID */
union all
select AREA_AREA_CODE,
TO_CHAR(A.prfd_start_time) grpCol1,
--&P_AGG(TO_CHAR(A.prfd_start_time, : grpCol2,
AREA_CODE SSA_NAME,
LAC.PRFR_OBJECT_ID,
A.PRFR_NEMS_OBJECT_ID BSC_NAME,
A.PRFR_CHILD_ID CELL,
A.counter9 COLUMN_1,-- Call_volume
A.COUNTER14 COLUMN_2,--Tch_traffic_erl
A.COUNTER8 COLUMN_3,--cssr
null COLUMN_4,--TCH_Blocking_Rate
A.COUNTER4 COLUMN_5,--Max_TCH_Available_Cell
A.COUNTER7 - A.COUNTER6 COLUMN_6,--MAX TCH Used_FR
A.COUNTER6 COLUMN_7,--MAX TCH Used_HR
A.Counter7 COLUMN_8,--MAX TCH Used_Total
A.COUNTER13 COLUMN_9,--SDCCH_Block_Rate
A.COUNTER2 COLUMN_10,--MAX SDCCH Available
A.COUNTER5 COLUMN_11,--MAX SDCCH Used
A.COUNTER1 COLUMN_12,--Assign_Fail_Ratio_Cell
A.COUNTER10 COLUMN_13,--Call_Drop_Rate_Cell
A.COUNTER11 COLUMN_14,--HO_Failure_Ratio_Cell1V1
A.COUNTER12 COLUMN_15,--HO_Success_Rate_Cell
NULL COLUMN_16,
NULL COLUMN_17,
NULL COLUMN_18,
NULL COLUMN_19,
NULL COLUMN_20,
NULL COLUMN_21,
NULL COLUMN_22,
NULL COLUMN_23,
NULL COLUMN_24,
NULL COLUMN_25,
NULL COLUMN_26, --CALCULATION MAY DIFFER BECAUSE IN REPORT WE DISPLAY ROUND(VALUE,2) OF K3014-COUNTER42 EX: 0.006 TO 0.01
LAC.C_COUNTER2 COLUMN_27,
LAC.COUNTER3 COLUMN_28,
LAC.COUNTER4 COLUMN_29,
LAC.COUNTER5 COLUMN_30,
LAC.COUNTER7 COLUMN_31,
LAC.COUNTER8 COLUMN_32,
LAC.COUNTER9 COLUMN_33
FROM (
SELECT * FROM
(SELECT PRFD_START_TIME, PRFD_START_DATE, PRFR_OBJECT_ID, PRFR_CHILD_ID, PRFR_PARENT_ID, PRFR_NEMS_OBJECT_ID, PRFR_NEMS_PARENT_ID,
COUNTER1, COUNTER2, COUNTER3, COUNTER4, COUNTER5, COUNTER6, COUNTER7, COUNTER8, COUNTER9, COUNTER10, COUNTER11, COUNTER12, COUNTER13, COUNTER14,
MAX(COUNTER14) OVER (PARTITION BY PRFD_START_DATE, PRFR_OBJECT_ID, PRFR_CHILD_ID) M_COUNTER42
FROM ETL_PERF_FACT
WHERE REP_ID=441
AND prfd_start_date >= TRUNC(TO_DATE(:p_start_date,'DD-MON-YYYY HH24:MI') )
AND prfd_start_date <= TRUNC(TO_DATE(:p_end_date,'DD-MON-YYYY HH24:MI') )
AND prfd_start_time >=TO_DATE(:p_start_date,'DD-MON-YYYY HH24:MI')
AND prfd_start_time <= TO_DATE(:p_end_date,'DD-MON-YYYY HH24:MI')
--AND PRFR_CHILD_ID='102_4401_A_NARAYANAPURAM_AT2212,CellID=4401'
) WHERE COUNTER14=M_COUNTER42
) A, MVIEW_EQUIPMENT, ETL_LOOKUP L , --ETL_PERF_FACT LAC
( SELECT LA.* FROM ETL_PERF_FACT LA, MVIEW_EQUIPMENT WHERE LA.REP_ID=366
AND LA.prfd_start_date >= TRUNC(TO_DATE(:p_start_date,'DD-MON-YYYY HH24:MI') )
AND LA.prfd_start_date <= TRUNC(TO_DATE(:p_end_date,'DD-MON-YYYY HH24:MI') )
AND EQUP_ID=LA.PRFR_PARENT_ID
-- &P_EQUPIDLIST &QRY &QRY1
) LAC
WHERE A.prfd_start_date = LAC.prfd_start_date(+)
AND A.PRFR_PARENT_ID=LAC.PRFR_PARENT_ID(+)
AND A.PRFR_OBJECT_ID = LAC.PRFR_OBJECT_ID(+)
AND ROUND(A.COUNTER4) = L.OFFERED_ERLANG(+)
AND EQUP_ID=A.PRFR_PARENT_ID
--&P_EQUPIDLIST
--&QRY
----&QRY1
ORDER BY TO_CHAR(A.prfd_start_time) ,A.PRFR_NEMS_OBJECT_ID,A.PRFR_CHILD_ID