Multiple sort orders with one query

[B Rabbit]

I have a report in Oracle Reports 6.0 that does the same query 3
different times, each with a different sort order. However, sometimes
the query takes a long time and running the query 3 times is
unacceptable. My question is, is there a way to just run the query one
time, then somehow change the sort order dynamically so that I can
output the query's results in the 3 different sort orders?

[DA Morgan]

B Rabbit wrote: I have a report in Oracle Reports 6.0 that does the same query 3 different times, each with a different sort order. However, sometimes the query takes a long time and running the query 3 times is unacceptable. My question is, is there a way to just run the query one time, then somehow change the sort order dynamically so that I can output the query's results in the 3 different sort orders?

Not having any idea the database version: You didn't provide it.
Not having any idea the amount of data: You didn't provide it.
Not having any idea the DML statement: You didn't provide it.
Not having the Explain Plans: You didn't provide them.
Not having the nature of the sort order differences: You didn't provide it.

What kind of help do you expect?
--
Daniel A. Morgan
http://www.psoug.org
damorgan@x.washington.edu
(replace x with u to respond)

[Noel]

Użytkownik B Rabbit napisał:
I have a report in Oracle Reports 6.0 that does the same query 3 different times, each with a different sort order. However, sometimes the query takes a long time and running the query 3 times is unacceptable. My question is, is there a way to just run the query one time, then somehow change the sort order dynamically so that I can output the query's results in the 3 different sort orders?

You might write output of query to a temporary table, and base your
report on that table.

But first try to optimize your query.

--
Noel

[Mark C. Stock]

"Noel" <tbal@go2.pll-l> wrote in message
news:d7mh01$i0s$1@inews.gazeta.pl... Uzytkownik B Rabbit napisal: I have a report in Oracle Reports 6.0 that does the same query 3 different times, each with a different sort order. However, sometimes the query takes a long time and running the query 3 times is unacceptable. My question is, is there a way to just run the query one time, then somehow change the sort order dynamically so that I can output the query's results in the 3 different sort orders? You might write output of query to a temporary table, and base your report on that table. But first try to optimize your query. -- Noel

you can also use subquery factoring with UNIONs if
[_] your version supports it
[_] you can't or don't want to create a temporary table with the structure
of your report data source
[_] you can base the 3 sections of your report on a single query

example (note the need to generate a string-based sort key for each UNIONed
SELECT):

SQL> with emps as (
2 select empno, ename, hiredate, dname
3 from emp, dept
4 where emp.deptno = dept.deptno
5 )
6 select 1 as section, lpad(empno,4) as sortkey, emps.*
7 from emps
8 union all
9 select 2, ename, emps.*
10 from emps
11 union all
12 select 3, to_char(hiredate, 'yyyymmdd'), emps.*
13 from emps
14 order by 1, 2
15 /

SECTION SORTKEY EMPNO ENAME HIREDATE DNAME
---------- ---------- ---------- ---------- --------- --------------
1 7369 7369 smith 17-DEC-80 RESEARCH
1 7499 7499 ALLEN 20-FEB-81 SALES
1 7521 7521 WARD 22-FEB-81 SALES
1 7566 7566 JONES 02-APR-81 RESEARCH
1 7654 7654 MARTIN 28-SEP-81 SALES
1 7698 7698 BLAKE 01-MAY-81 SALES
1 7782 7782 CLARK 09-JUN-81 ACCOUNTING
1 7788 7788 SCOTT 19-APR-87 RESEARCH
1 7839 7839 KING 17-NOV-81 ACCOUNTING
1 7844 7844 TURNER 08-SEP-81 SALES
1 7876 7876 ADAMS 23-MAY-87 RESEARCH
1 7900 7900 JAMES 03-DEC-81 SALES
1 7902 7902 FORD 03-DEC-81 RESEARCH
1 7934 7934 MILLER 23-JAN-82 ACCOUNTING
2 ADAMS 7876 ADAMS 23-MAY-87 RESEARCH
2 ALLEN 7499 ALLEN 20-FEB-81 SALES
2 BLAKE 7698 BLAKE 01-MAY-81 SALES
2 CLARK 7782 CLARK 09-JUN-81 ACCOUNTING
2 FORD 7902 FORD 03-DEC-81 RESEARCH
2 JAMES 7900 JAMES 03-DEC-81 SALES
2 JONES 7566 JONES 02-APR-81 RESEARCH
2 KING 7839 KING 17-NOV-81 ACCOUNTING
2 MARTIN 7654 MARTIN 28-SEP-81 SALES
2 MILLER 7934 MILLER 23-JAN-82 ACCOUNTING
2 SCOTT 7788 SCOTT 19-APR-87 RESEARCH
2 TURNER 7844 TURNER 08-SEP-81 SALES
2 WARD 7521 WARD 22-FEB-81 SALES
2 smith 7369 smith 17-DEC-80 RESEARCH
3 19801217 7369 smith 17-DEC-80 RESEARCH
3 19810220 7499 ALLEN 20-FEB-81 SALES
3 19810222 7521 WARD 22-FEB-81 SALES
3 19810402 7566 JONES 02-APR-81 RESEARCH
3 19810501 7698 BLAKE 01-MAY-81 SALES
3 19810609 7782 CLARK 09-JUN-81 ACCOUNTING
3 19810908 7844 TURNER 08-SEP-81 SALES
3 19810928 7654 MARTIN 28-SEP-81 SALES
3 19811117 7839 KING 17-NOV-81 ACCOUNTING
3 19811203 7902 FORD 03-DEC-81 RESEARCH
3 19811203 7900 JAMES 03-DEC-81 SALES
3 19820123 7934 MILLER 23-JAN-82 ACCOUNTING
3 19870419 7788 SCOTT 19-APR-87 RESEARCH
3 19870523 7876 ADAMS 23-MAY-87 RESEARCH

42 rows selected.


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=7 Card=42 Bytes=1596)
1 0 TEMP TABLE TRANSFORMATION
2 1 LOAD AS SELECT
3 2 MERGE JOIN (Cost=6 Card=14 Bytes=476)
4 3 TABLE ACCESS (BY INDEX ROWID) OF 'DEPT' (TABLE) (Cost=2
Card=4 Bytes=52)
5 4 INDEX (FULL SCAN) OF 'PK_DEPT' (INDEX (UNIQUE)) (Cost=1
Card=4)
6 3 SORT (JOIN) (Cost=4 Card=14 Bytes=294)
7 6 TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=14
Bytes=294)
8 1 SORT (ORDER BY) (Cost=6 Card=42 Bytes=1596)
9 8 UNION-ALL
10 9 VIEW (Cost=2 Card=14 Bytes=532)
11 10 TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9D6611_3F8E71B'
(TABLE (TEMP)) (Cost=2 Card=14 Byt
12 9 VIEW (Cost=2 Card=14 Bytes=532)
13 12 TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9D6611_3F8E71B'
(TABLE (TEMP)) (Cost=2 Card=14 Byt
14 9 VIEW (Cost=2 Card=14 Bytes=532)
15 14 TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9D6611_3F8E71B'
(TABLE (TEMP)) (Cost=2 Card=14 Byt

++ mcs