Bizzare order by using Ref Cursor Query

[bobbypradhan]

Hi all,

Versions
Oracle DB 10.2.0.1
Oracle Reports 6i or Oracle Report 10g

It seemed very simple (for me) when I first thought about it but I am hitting a thick concrete wall ... Splatttt....

The order by clause used in the Ref Cursor was not
recognized in Reports or atleast not in effect.

Report output is order by first and second column instead
of third and fourth column which was intended output.

I used the same query in sqlplus and the results are
expected, they are ordered by as stated in order by
clause.

Thank you in advance for your help.

Here's the package I used for the Ref Cursor
and the Query

The Package
-----------------------------------------------------
CREATE OR REPLACE PACKAGE SUMMARY_REPORT_PACKAGE IS
TYPE FAULT_RECORD IS RECORD
(P_TAGNAME SYSTEMFAULTLOG.TAGNAME%TYPE,
P_SEVERITY VARCHAR2(10),
P_TOTALFAULTS NUMBER(10),
P_TOTALTIME NUMBER(20),
P_DURATION VARCHAR2(25)
);
TYPE FAULT_REC IS REF CURSOR RETURN FAULT_RECORD;
PROCEDURE GETRECORDS (PAR_START_DATETIME varchar2,
PAR_END_DATETIME varchar2,
PAR_TOUR varchar2,
PAR_AP varchar2,
RecordsOut out SUMMARY_REPORT_PACKAGE.FAULT_REC);
END;
/

CREATE OR REPLACE PACKAGE BODY SUMMARY_REPORT_PACKAGE IS
PROCEDURE GETRECORDS (PAR_START_DATETIME varchar2,
PAR_END_DATETIME varchar2,
PAR_TOUR varchar2,
PAR_AP varchar2,
RecordsOut out SUMMARY_REPORT_PACKAGE.FAULT_REC) IS
STARTREPORTRANGE DATE := TO_DATE(PAR_START_DATETIME,'YYYY/MM/DD HH24:MI');
STOPREPORTRANGE DATE := TO_DATE(PAR_END_DATETIME,'YYYY/MM/DD HH24:MI');
EPICTIME DATE:=TO_DATE('1900/01/01','YYYY/MM/DD');
BEGIN
IF PAR_TOUR = '1' THEN
IF TO_NUMBER(TO_CHAR(SYSDATE,'HH24MI')) >= 0 AND
TO_NUMBER(TO_CHAR(SYSDATE,'HH24MI')) < 700 THEN
STARTREPORTRANGE := TO_DATE(TO_CHAR(SYSDATE-2,'YYYY/MM/DD')||' 23:00:00','YYYY/MM/DD HH24:MI:SS');
STOPREPORTRANGE := TO_DATE(TO_CHAR(SYSDATE-1,'YYYY/MM/DD')||' 07:00:00','YYYY/MM/DD HH24:MI:SS');
ELSIF TO_NUMBER(TO_CHAR(SYSDATE,'HH24MI')) >= 2300 AND
TO_NUMBER(TO_CHAR(SYSDATE,'HH24MI')) < 2359 THEN
STARTREPORTRANGE := TO_DATE(TO_CHAR(SYSDATE-1,'YYYY/MM/DD')||' 23:00:00','YYYY/MM/DD HH24:MI:SS');
STOPREPORTRANGE := TO_DATE(TO_CHAR(SYSDATE,'YYYY/MM/DD')||' 07:00:00','YYYY/MM/DD HH24:MI:SS');
ELSIF TO_NUMBER(TO_CHAR(SYSDATE,'HH24MI')) >= 700 AND
TO_NUMBER(TO_CHAR(SYSDATE,'HH24MI')) < 2300 THEN
STARTREPORTRANGE := TO_DATE(TO_CHAR(SYSDATE-1,'YYYY/MM/DD')||' 23:00:00','YYYY/MM/DD HH24:MI:SS');
STOPREPORTRANGE := TO_DATE(TO_CHAR(SYSDATE,'YYYY/MM/DD')||' 07:00:00','YYYY/MM/DD HH24:MI:SS');
END IF;
END IF;
IF PAR_TOUR = '2' THEN
IF TO_NUMBER(TO_CHAR(SYSDATE,'HH24MI')) >= 0000 AND
TO_NUMBER(TO_CHAR(SYSDATE,'HH24MI')) < 1500 THEN
STARTREPORTRANGE := TO_DATE(TO_CHAR(SYSDATE-1,'YYYY/MM/DD')||' 07:00:00','YYYY/MM/DD HH24:MI:SS');
STOPREPORTRANGE := TO_DATE(TO_CHAR(SYSDATE-1,'YYYY/MM/DD')||' 15:00:00','YYYY/MM/DD HH24:MI:SS');
ELSIF TO_NUMBER(TO_CHAR(SYSDATE,'HH24MI')) >= 1500 AND
TO_NUMBER(TO_CHAR(SYSDATE,'HH24MI')) <= 2359 THEN
STARTREPORTRANGE := TO_DATE(TO_CHAR(SYSDATE,'YYYY/MM/DD')||' 07:00:00','YYYY/MM/DD HH24:MI:SS');
STOPREPORTRANGE := TO_DATE(TO_CHAR(SYSDATE,'YYYY/MM/DD')||' 15:00:00','YYYY/MM/DD HH24:MI:SS');
END IF;
END IF;
IF PAR_TOUR = '3' THEN
IF TO_NUMBER(TO_CHAR(SYSDATE,'HH24MI')) >= 0000 AND
TO_NUMBER(TO_CHAR(SYSDATE,'HH24MI')) < 2300 THEN
STARTREPORTRANGE := TO_DATE(TO_CHAR(SYSDATE-1,'YYYY/MM/DD')||' 15:00:00','YYYY/MM/DD HH24:MI:SS');
STOPREPORTRANGE := TO_DATE(TO_CHAR(SYSDATE-1,'YYYY/MM/DD')||' 23:00:00','YYYY/MM/DD HH24:MI:SS');
ELSIF TO_NUMBER(TO_CHAR(SYSDATE,'HH24MI')) >= 2300 AND
TO_NUMBER(TO_CHAR(SYSDATE,'HH24MI')) >= 2359 THEN
STARTREPORTRANGE := TO_DATE(TO_CHAR(SYSDATE,'YYYY/MM/DD')||' 15:00:00','YYYY/MM/DD HH24:MI:SS');
STOPREPORTRANGE := TO_DATE(TO_CHAR(SYSDATE,'YYYY/MM/DD')||' 23:00:00','YYYY/MM/DD HH24:MI:SS');
END IF;
END IF;

open RecordsOut for
SELECT TAGNAME P_TAGNAME, SEVERITY P_SEVERITY, COUNT(ELAPSEDTIME) P_TOTALFAULTS, SUM(ELAPSEDTIME) P_TOTALTIME,
DECODE(LENGTH(TO_CHAR(FLOOR(TO_NUMBER(SUBSTR(LPAD(TO_CHAR(SUM(ELAPSEDTIME)),15,'0'),1,12))/3600))),0,'00',1,
LPAD(TO_CHAR(FLOOR(TO_NUMBER(SUBSTR(LPAD(TO_CHAR(SUM(ELAPSEDTIME)),15,'0'),1,12))/3600)),2,'0'),
TO_CHAR(FLOOR(TO_NUMBER(SUBSTR(LPAD(TO_CHAR(SUM(ELAPSEDTIME)),15,'0'),1,12))/3600)))||':'||
LPAD(TO_CHAR(FLOOR(MOD(TO_NUMBER(SUBSTR(LPAD(TO_CHAR(SUM(ELAPSEDTIME)),15,'0'),1,12)),3600)/60)),2,'0')||':'||
LPAD(TO_CHAR(ROUND(MOD(MOD(TO_NUMBER(SUBSTR(LPAD(TO_CHAR(SUM(ELAPSEDTIME)),15,'0'),1,12)),3600),60))),2,'0')||'.'||
SUBSTR(LPAD(TO_CHAR(SUM(ELAPSEDTIME)),15,'0') ,13,3) P_DURATION FROM (
SELECT TAGNAME TAGNAME, -- **************** Case 1 Complete Event ****************
'' SEVERITY,
ELAPSEDTIME
FROM SYSTEMFAULTLOGREPORT
WHERE STARTTIMESTMP >= STARTREPORTRANGE
AND STOPTIMESTMP <= STOPREPORTRANGE
AND ELAPSEDTIME IS NOT NULL
AND ELAPSEDTIME > 0
UNION
SELECT TAGNAME TAGNAME, -- **************** Case 2 Complete Event ****************
'' SEVERITY,
ROUND(TO_NUMBER(STOPREPORTRANGE - STARTREPORTRANGE) * 86400000) ELAPSEDTIME
FROM SYSTEMFAULTLOGREPORT
WHERE STARTTIMESTMP <= STARTREPORTRANGE
AND STOPTIMESTMP > STOPREPORTRANGE
AND ELAPSEDTIME IS NOT NULL
AND ELAPSEDTIME > 0
UNION
SELECT TAGNAME TAGNAME, -- **************** Case 3 Complete Event ****************
'' SEVERITY,
ROUND((TO_NUMBER(STOPTIMESTMP - STARTREPORTRANGE) * 86400000) + STOPMILLITIME) ELAPSEDTIME
FROM SYSTEMFAULTLOGREPORT
WHERE STARTTIMESTMP < STARTREPORTRANGE
AND STOPTIMESTMP > STARTREPORTRANGE
AND STOPTIMESTMP < STOPREPORTRANGE
AND ELAPSEDTIME IS NOT NULL
AND ELAPSEDTIME > 0
UNION
SELECT TAGNAME TAGNAME, -- **************** Case 4 Complete Event ****************
'' SEVERITY,
ROUND((TO_NUMBER(STOPREPORTRANGE - EPICTIME)*86400000)
-((TO_NUMBER(STARTTIMESTMP - EPICTIME)*86400000)+STARTMILLITIME)) ELAPSEDTIME
FROM SYSTEMFAULTLOGREPORT
WHERE STARTTIMESTMP > STARTREPORTRANGE
AND STARTTIMESTMP < STOPREPORTRANGE
AND STOPTIMESTMP > STOPREPORTRANGE
AND ELAPSEDTIME IS NOT NULL
AND ELAPSEDTIME > 0
UNION
SELECT TAGNAME TAGNAME, -- **************** Case 1 Incomplete Event ****************
'Active' SEVERITY,
ROUND(((TO_NUMBER(sysdate - epictime) * 86400000)
- ((TO_NUMBER(starttimestmp - epictime) * 86400000) + startmillitime))) ELAPSEDTIME
FROM SYSTEMFAULTLOGREPORT
WHERE STARTTIMESTMP >= STARTREPORTRANGE
AND STARTTIMESTMP < STOPREPORTRANGE
AND SYSDATE > STARTTIMESTMP
AND SYSDATE >= STARTREPORTRANGE
AND SYSDATE < STOPREPORTRANGE
AND ELAPSEDTIME IS NULL
UNION
SELECT TAGNAME TAGNAME, -- **************** Case 2 Incomplete Event ****************
'Active' SEVERITY,
ROUND(TO_NUMBER(STOPREPORTRANGE - STARTREPORTRANGE) * 86400000) ELAPSEDTIME
FROM SYSTEMFAULTLOGREPORT
WHERE STARTTIMESTMP < STARTREPORTRANGE
AND SYSDATE > STOPREPORTRANGE
AND ELAPSEDTIME IS NULL
UNION
SELECT TAGNAME TAGNAME, -- **************** Case 3 Incomplete Event ****************
'Active' SEVERITY,
ROUND(TO_NUMBER(SYSDATE-STARTREPORTRANGE) * 86400000) ELAPSEDTIME
FROM SYSTEMFAULTLOGREPORT
WHERE STARTTIMESTMP < STARTREPORTRANGE
AND SYSDATE > STARTREPORTRANGE
AND SYSDATE <= STOPREPORTRANGE
AND ELAPSEDTIME IS NULL
UNION
SELECT TAGNAME TAGNAME, -- **************** Case 4 Incomplete Event ****************
'Active' SEVERITY,
ROUND(((TO_NUMBER(stopreportrange - epictime) * 86400000)
- ((to_number(starttimestmp - epictime) * 86400000) + startmillitime))) ELAPSEDTIME
FROM SYSTEMFAULTLOGREPORT
WHERE STARTTIMESTMP >= STARTREPORTRANGE
AND STARTTIMESTMP < STOPREPORTRANGE
AND SYSDATE > STOPREPORTRANGE
AND ELAPSEDTIME IS NULL
UNION
SELECT TAGNAME||CHR(178) TAGNAME, -- **************** Case 5 Incomplete Event ****************
'Active' SEVERITY,
TO_NUMBER('0') ELAPSEDTIME
FROM SYSTEMFAULTLOGREPORT
WHERE STARTTIMESTMP < STARTREPORTRANGE
AND SYSDATE < STARTREPORTRANGE
AND ELAPSEDTIME IS NULL
UNION
SELECT TAGNAME||CHR(178) TAGNAME, -- **************** Case 6 Incomplete Event ****************
'Active' SEVERITY,
TO_NUMBER('0') ELAPSEDTIME
FROM SYSTEMFAULTLOGREPORT
WHERE STARTTIMESTMP > STOPREPORTRANGE
AND SYSDATE > STOPREPORTRANGE
AND ELAPSEDTIME IS NULL
order by ELAPSEDTIME desc)
GROUP BY TAGNAME,SEVERITY
ORDER BY COUNT(ELAPSEDTIME) DESC, SUM(ELAPSEDTIME) DESC;
END;
END;
/

-----------------------------------------------------
The SQL Query
-----------------------------------------------------variable STARTREPORTRANGE varchar2(20);
variable STOPREPORTRANGE varchar2(20);
variable EPICTIME varchar2(20);
exec :STARTREPORTRANGE := '2008/04/03 00:00';
exec :STOPREPORTRANGE := '2008/04/05 00:00';
exec :EPICTIME := '1900/01/01 00:00';
SELECT TAGNAME P_TAGNAME, SEVERITY P_SEVERITY, COUNT(ELAPSEDTIME) P_TOTALFAULTS, SUM(ELAPSEDTIME) TOTALTIME,
DECODE(LENGTH(TO_CHAR(FLOOR(TO_NUMBER(SUBSTR(LPAD(TO_CHAR(SUM(ELAPSEDTIME)),15,'0'),1,12))/3600))),0,'00',1,
LPAD(TO_CHAR(FLOOR(TO_NUMBER(SUBSTR(LPAD(TO_CHAR(SUM(ELAPSEDTIME)),15,'0'),1,12))/3600)),2,'0'),
TO_CHAR(FLOOR(TO_NUMBER(SUBSTR(LPAD(TO_CHAR(SUM(ELAPSEDTIME)),15,'0'),1,12))/3600)))||':'||
LPAD(TO_CHAR(FLOOR(MOD(TO_NUMBER(SUBSTR(LPAD(TO_CHAR(SUM(ELAPSEDTIME)),15,'0'),1,12)),3600)/60)),2,'0')||':'||
LPAD(TO_CHAR(ROUND(MOD(MOD(TO_NUMBER(SUBSTR(LPAD(TO_CHAR(SUM(ELAPSEDTIME)),15,'0'),1,12)),3600),60))),2,'0')||'.'||
SUBSTR(LPAD(TO_CHAR(SUM(ELAPSEDTIME)),15,'0') ,13,3) P_DURATION FROM (
SELECT TAGNAME, -- **************** Case 1 Complete Event ****************
'' SEVERITY,
ELAPSEDTIME
FROM SYSTEMFAULTLOGREPORT
WHERE STARTTIMESTMP >= to_dateSTARTREPORTRANGE,'YYYY/MM/DD HH24:MI')
AND STOPTIMESTMP <= to_dateSTOPREPORTRANGE,'YYYY/MM/DD HH24:MI')
AND ELAPSEDTIME IS NOT NULL
AND ELAPSEDTIME > 0
UNION
SELECT TAGNAME, -- **************** Case 2 Complete Event ****************
'' SEVERITY,
ROUND((TO_NUMBER(to_dateSTOPREPORTRANGE,'YYYY/MM/DD HH24:MI')
- to_dateSTARTREPORTRANGE,'YYYY/MM/DD HH24:MI')) * 86400000)) ELAPSEDTIME
FROM SYSTEMFAULTLOGREPORT
WHERE STARTTIMESTMP <= to_dateSTARTREPORTRANGE,'YYYY/MM/DD HH24:MI')
AND STOPTIMESTMP > to_dateSTOPREPORTRANGE,'YYYY/MM/DD HH24:MI')
AND ELAPSEDTIME IS NOT NULL
AND ELAPSEDTIME > 0
UNION
SELECT TAGNAME, -- **************** Case 3 Complete Event ****************
'' SEVERITY,
ROUND((TO_NUMBER(STOPTIMESTMP - to_dateSTARTREPORTRANGE,'YYYY/MM/DD HH24:MI')) * 86400000) + STOPMILLITIME) ELAPSEDTIME
FROM SYSTEMFAULTLOGREPORT
WHERE STARTTIMESTMP < to_dateSTARTREPORTRANGE,'YYYY/MM/DD HH24:MI')
AND STOPTIMESTMP > to_dateSTARTREPORTRANGE,'YYYY/MM/DD HH24:MI')
AND STOPTIMESTMP < to_dateSTOPREPORTRANGE,'YYYY/MM/DD HH24:MI')
AND ELAPSEDTIME IS NOT NULL
AND ELAPSEDTIME > 0
UNION
SELECT TAGNAME, -- **************** Case 4 Complete Event ****************
'' SEVERITY,
ROUND(((TO_NUMBER(to_dateSTOPREPORTRANGE,'YYYY/MM/DD HH24:MI') - TO_DATEEPICTIME,'YYYY/MM/DD HH24:MI'))*86400000)
-((TO_NUMBER(STARTTIMESTMP - TO_DATEEPICTIME,'YYYY/MM/DD HH24:MI'))*86400000)+STARTMILLITIME))) ELAPSEDTIME
FROM SYSTEMFAULTLOGREPORT
WHERE STARTTIMESTMP > to_dateSTARTREPORTRANGE,'YYYY/MM/DD HH24:MI')
AND STARTTIMESTMP < to_dateSTOPREPORTRANGE,'YYYY/MM/DD HH24:MI')
AND STOPTIMESTMP > to_dateSTOPREPORTRANGE,'YYYY/MM/DD HH24:MI')
AND ELAPSEDTIME IS NOT NULL
AND ELAPSEDTIME > 0
UNION
SELECT TAGNAME, -- **************** Case 1 Incomplete Event ****************
'Active' SEVERITY,
ROUND(((TO_NUMBER(sysdate - TO_DATEEPICTIME,'YYYY/MM/DD HH24:MI')) * 86400000)
- ((TO_NUMBER(starttimestmp - TO_DATEEPICTIME,'YYYY/MM/DD HH24:MI')) * 86400000) + startmillitime))) ELAPSEDTIME
FROM SYSTEMFAULTLOGREPORT
WHERE STARTTIMESTMP >= to_dateSTARTREPORTRANGE,'YYYY/MM/DD HH24:MI')
AND STARTTIMESTMP < to_dateSTOPREPORTRANGE,'YYYY/MM/DD HH24:MI')
AND SYSDATE > STARTTIMESTMP
AND SYSDATE >= to_dateSTARTREPORTRANGE,'YYYY/MM/DD HH24:MI')
AND SYSDATE < to_dateSTOPREPORTRANGE,'YYYY/MM/DD HH24:MI')
AND ELAPSEDTIME IS NULL
UNION
SELECT TAGNAME, -- **************** Case 2 Incomplete Event ****************
'Active' SEVERITY,
ROUND((TO_NUMBER(to_dateSTOPREPORTRANGE,'YYYY/MM/DD HH24:MI') - to_dateSTARTREPORTRANGE,'YYYY/MM/DD HH24:MI')) * 86400000)) ELAPSEDTIME
FROM SYSTEMFAULTLOGREPORT
WHERE STARTTIMESTMP < to_dateSTARTREPORTRANGE,'YYYY/MM/DD HH24:MI')
AND SYSDATE > to_dateSTOPREPORTRANGE,'YYYY/MM/DD HH24:MI')
AND ELAPSEDTIME IS NULL
UNION
SELECT TAGNAME, -- **************** Case 3 Incomplete Event ****************
'Active' SEVERITY,
ROUND(TO_NUMBER(SYSDATE-to_dateSTARTREPORTRANGE,'YYYY/MM/DD HH24:MI')) * 86400000) ELAPSEDTIME
FROM WIC.SYSTEMFAULTLOGREPORT
WHERE STARTTIMESTMP < to_dateSTARTREPORTRANGE,'YYYY/MM/DD HH24:MI')
AND SYSDATE > to_dateSTARTREPORTRANGE,'YYYY/MM/DD HH24:MI')
AND SYSDATE <= to_dateSTOPREPORTRANGE,'YYYY/MM/DD HH24:MI')
AND ELAPSEDTIME IS NULL
UNION
SELECT TAGNAME, -- **************** Case 4 Incomplete Event ****************
'Active' SEVERITY,
ROUND(((TO_NUMBER(to_dateSTOPREPORTRANGE,'YYYY/MM/DD HH24:MI') - TO_DATEEPICTIME,'YYYY/MM/DD HH24:MI')) * 86400000)
- ((to_number(starttimestmp - TO_DATEEPICTIME,'YYYY/MM/DD HH24:MI')) * 86400000) + startmillitime))) ELAPSEDTIME
FROM SYSTEMFAULTLOGREPORT
WHERE STARTTIMESTMP >= to_dateSTARTREPORTRANGE,'YYYY/MM/DD HH24:MI')
AND STARTTIMESTMP < to_dateSTOPREPORTRANGE,'YYYY/MM/DD HH24:MI')
AND SYSDATE > to_dateSTOPREPORTRANGE,'YYYY/MM/DD HH24:MI')
AND ELAPSEDTIME IS NULL
UNION
SELECT TAGNAME, -- **************** Case 5 Incomplete Event ****************
'Active' SEVERITY,
TO_NUMBER('0') ELAPSEDTIME
FROM SYSTEMFAULTLOGREPORT
WHERE STARTTIMESTMP < to_dateSTARTREPORTRANGE,'YYYY/MM/DD HH24:MI')
AND SYSDATE < to_dateSTARTREPORTRANGE,'YYYY/MM/DD HH24:MI')
AND ELAPSEDTIME IS NULL
UNION
SELECT TAGNAME, -- **************** Case 6 Incomplete Event ****************
'Active' SEVERITY,
TO_NUMBER('0') ELAPSEDTIME FROM
SYSTEMFAULTLOGREPORT
WHERE STARTTIMESTMP > to_dateSTOPREPORTRANGE,'YYYY/MM/DD HH24:MI')
AND SYSDATE > to_dateSTOPREPORTRANGE,'YYYY/MM/DD HH24:MI')
AND ELAPSEDTIME IS NULL)
GROUP BY TAGNAME, SEVERITY
ORDER BY COUNT(ELAPSEDTIME) DESC,SUM(ELAPSEDTIME) DESC;