Re: mixing dynamic native sql and static sql?

From: Thomas Kyte <tkyte_at_oracle.com>
Date: 13 Apr 2002 08:55:46 -0700
Message-ID: <a99ke20336@drn.newsguy.com>

In article <pqgfbuommtjm1ldoo2ajj29pju9hoos082_at_4ax.com>, John says...
>
>On 12 Apr 2002 02:16:24 -0700, plm_at_gmx.li (Peter Mutsaers) wrote:
>>Hello,
>>
>>I need to parametrize the ordering from a large query.
>
>You can use something like
>
>ORDER BY DECODE(test_expression, case1, column1, case2, column2,
>case3, column3, ...)
>
>to evaluate a complex condition and substitute the appropriate column
>to order by in each case. The expression might be a function that
>performs an arbitrary number of tests. Don't know how well this
>performs on a large result set or whether it's practical to make a
>function-based index on the entire DECODE() or just the expression
>being evaluated.
>

and you must ensure that each of the decodes returns the SAME type..

consider:

scott_at_ORA817DEV.US.ORACLE.COM> variable x varchar2(20)
scott_at_ORA817DEV.US.ORACLE.COM> 
scott_at_ORA817DEV.US.ORACLE.COM> exec :x := 'ENAME';

PL/SQL procedure successfully completed.

scott_at_ORA817DEV.US.ORACLE.COM>
scott_at_ORA817DEV.US.ORACLE.COM> select ename, sal, hiredate 2 from emp
3 order by decode( :x, 'ENAME', ename, 'SAL', sal, 'HIREDATE', hiredate ) 4 /

ENAME SAL HIREDATE

---------- ---------- ---------
ADAMS            1100 12-JAN-83
ALLEN            1600 20-FEB-81
BLAKE            2850 01-MAY-81
CLARK            2450 09-JUN-81
FORD             3000 03-DEC-81
JAMES             950 03-DEC-81
JONES            2975 02-APR-81
KING             5000 17-NOV-81
MARTIN           1250 28-SEP-81
MILLER           1300 23-JAN-82
SCOTT            3000 09-DEC-82
SMITH             800 17-DEC-80
TURNER           1500 08-SEP-81
WARD             1250 22-FEB-81

14 rows selected.

scott_at_ORA817DEV.US.ORACLE.COM> exec :x := 'SAL';

PL/SQL procedure successfully completed.

scott_at_ORA817DEV.US.ORACLE.COM> /

ENAME SAL HIREDATE

---------- ---------- ---------
ADAMS            1100 12-JAN-83
WARD             1250 22-FEB-81
MARTIN           1250 28-SEP-81
MILLER           1300 23-JAN-82
TURNER           1500 08-SEP-81
ALLEN            1600 20-FEB-81
CLARK            2450 09-JUN-81
BLAKE            2850 01-MAY-81
JONES            2975 02-APR-81
SCOTT            3000 09-DEC-82
FORD             3000 03-DEC-81
KING             5000 17-NOV-81
SMITH             800 17-DEC-80
JAMES             950 03-DEC-81

14 rows selected.

scott_at_ORA817DEV.US.ORACLE.COM> exec :x := 'HIREDATE';

PL/SQL procedure successfully completed.

scott_at_ORA817DEV.US.ORACLE.COM> /

ENAME SAL HIREDATE

---------- ---------- ---------
BLAKE            2850 01-MAY-81
JONES            2975 02-APR-81
JAMES             950 03-DEC-81
FORD             3000 03-DEC-81
TURNER           1500 08-SEP-81
SCOTT            3000 09-DEC-82
CLARK            2450 09-JUN-81
ADAMS            1100 12-JAN-83
SMITH             800 17-DEC-80
KING             5000 17-NOV-81
ALLEN            1600 20-FEB-81
WARD             1250 22-FEB-81
MILLER           1300 23-JAN-82
MARTIN           1250 28-SEP-81

14 rows selected.

scott_at_ORA817DEV.US.ORACLE.COM>

Hmm, ename works but SAL and HIREDATE? Not. You have to cast everthing explicitly into some string that is sortable:

scott_at_ORA817DEV.US.ORACLE.COM> select ename, sal, hiredate 2 from emp

  3  order by decode( :x, 'ENAME', ename,
  4                       'SAL', to_char(sal,'99999.99'),
  5                       'HIREDATE', to_char(hiredate,'YYYYMMDDHH24MISS' ) )

6 /

ENAME SAL HIREDATE

---------- ---------- ---------
ADAMS            1100 12-JAN-83
ALLEN            1600 20-FEB-81
BLAKE            2850 01-MAY-81
CLARK            2450 09-JUN-81
FORD             3000 03-DEC-81
JAMES             950 03-DEC-81
JONES            2975 02-APR-81
KING             5000 17-NOV-81
MARTIN           1250 28-SEP-81
MILLER           1300 23-JAN-82
SCOTT            3000 09-DEC-82
SMITH             800 17-DEC-80
TURNER           1500 08-SEP-81
WARD             1250 22-FEB-81

14 rows selected.

scott_at_ORA817DEV.US.ORACLE.COM> exec :x := 'SAL';

PL/SQL procedure successfully completed.

scott_at_ORA817DEV.US.ORACLE.COM> /

ENAME SAL HIREDATE

---------- ---------- ---------
SMITH             800 17-DEC-80
JAMES             950 03-DEC-81
ADAMS            1100 12-JAN-83
WARD             1250 22-FEB-81
MARTIN           1250 28-SEP-81
MILLER           1300 23-JAN-82
TURNER           1500 08-SEP-81
ALLEN            1600 20-FEB-81
CLARK            2450 09-JUN-81
BLAKE            2850 01-MAY-81
JONES            2975 02-APR-81
SCOTT            3000 09-DEC-82
FORD             3000 03-DEC-81
KING             5000 17-NOV-81

14 rows selected.

scott_at_ORA817DEV.US.ORACLE.COM> exec :x := 'HIREDATE';

PL/SQL procedure successfully completed.

scott_at_ORA817DEV.US.ORACLE.COM> /

ENAME SAL HIREDATE

---------- ---------- ---------
SMITH             800 17-DEC-80
ALLEN            1600 20-FEB-81
WARD             1250 22-FEB-81
JONES            2975 02-APR-81
BLAKE            2850 01-MAY-81
CLARK            2450 09-JUN-81
TURNER           1500 08-SEP-81
MARTIN           1250 28-SEP-81
KING             5000 17-NOV-81
JAMES             950 03-DEC-81
FORD             3000 03-DEC-81
MILLER           1300 23-JAN-82
SCOTT            3000 09-DEC-82
ADAMS            1100 12-JAN-83

14 rows selected.

Now, numbers will still give you heartburn as the whole negative thing will mess up the sort as well...

recommended only for pretty small end results where you KNOW the data very very well.

>John
>--
>Got an Oracle database question?
>Try the search engine for the database docs at:
>http://tahiti.oracle.com/

--
Thomas Kyte (tkyte@oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp

Received on Sat Apr 13 2002 - 10:55:46 CDT