OraFAQ Forum: SQL & PL/SQL » dynamic order by and decode and order by (merged threads)

Home » SQL & PL/SQL » SQL & PL/SQL » dynamic order by and decode and order by (merged threads)

Show: Today's Messages :: Show Polls :: Message Navigator
E-mail to friend

dynamic order by and decode and order by (merged threads) [message #132702]

Mon, 15 August 2005 15:21

bella13
Messages: 90
Registered: July 2005

Member

HI

I am writing a query for my procedure. At runtime I want to order by a particular column and also at runtime i want to specify if it is order by column ASC or order by column Desc.

select * from mytable
ORDER BY Decode (:pSort, 1,DECODE(:pOrderCol,'0',2,'1',3),
DECODE(:pOrderCol,'0',2,'1',3) Desc)

i.e if my pOrderCol = 0 then order by col 2 else order by col 3
and if pSort <> 1 then order by should be desc.

However the above query gives me an error . It says missing right paranthisis. Basically it is not understanding the word desc.

Also i tried doing a case

ORDER BY case
when :pSort = 1
Then DECODE(:pOrderCol,'0',1,'1',2)
else DECODE(:pOrderCol,'0',1,'1',2) desc
end
here it gives me the error missing keyword.ora-00905

Any clues/ help is appreciated.Thnx in advance

Report message to a moderator

decode and order by [message #132711 is a reply to message #132702]

Mon, 15 August 2005 18:28

bella13
Messages: 90
Registered: July 2005

Member

HI
I had written a post about ordering by earlier today. Here is the question more specifically.

When I run this query -

Select * from emp
order by
DECODE (&a, 'a', DECODE(&p, 'no', ENAME, JOB) ASC,'1')

I get an error :

ERROR at line 3:
ORA-00907: missing right parenthesis

However, when I run

Select * from emp
order by
DECODE (&a, 'a', DECODE(&p, 'no', ENAME, JOB) )DESC, DECODE(&p, 'no', ENAME, JOB)

It gives me no error.

However my result is as expected using the 2nd query. any suggestions ?

Thnx

Report message to a moderator

Re: decode and order by [message #132722 is a reply to message #132711]

Mon, 15 August 2005 20:24

Barbara Boehmer
Messages: 7668
Registered: November 2002
Location: California, USA

Senior Member

You cannot include ASC or DESC as part of the DECODE parameters, but you can use them after the DECODE, so that your order by statement amounts to:

ORDER BY 'whatever the first decode evaluates to' ASC or DESC,
'whatever some other decode evaluates to' ASC or DESC

The above are evaluated like two separate columns to order by. You cannot nest one within the other and add DESC in the middle. It can help to understand how things work by breaking it down into pieces. The query first orders things by the first expression, then orders the ties within that first ordering by the second expresion. If &a is not equal to 'a', then the first expression evaluates to null, so only the second expression counts. Please see the demonstration below, in which I have corrected your query by adding single quotes around the substitution variables and selected only one evaluated expression at a time, with verify on.

scott@ORA92> SET VERIFY ON
scott@ORA92> DEFINE a = 'a'
scott@ORA92> DEFINE p = 'no'
scott@ORA92> Select *
  2  from   emp
  3  order  by DECODE ('&a', 'a', DECODE ('&p', 'no', ENAME, JOB)) DESC, -- first
  4  	       DECODE ('&p', 'no', ENAME, JOB) ASC -- second
  5  /
old   3: order  by DECODE ('&a', 'a', DECODE ('&p', 'no', ENAME, JOB)) DESC, -- first
new   3: order  by DECODE ('a', 'a', DECODE ('no', 'no', ENAME, JOB)) DESC, -- first
old   4:           DECODE ('&p', 'no', ENAME, JOB) ASC -- second
new   4:           DECODE ('no', 'no', ENAME, JOB) ASC -- second

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7788 SCOTT      ANALYST         7566 09-DEC-82       3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7566 JONES      MANAGER         7839 02-APR-81       2000                    20
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7876 ADAMS      CLERK           7788 12-JAN-83       1100                    20

14 rows selected.

scott@ORA92> DEFINE a = 'a'
scott@ORA92> DEFINE p = 'no'
scott@ORA92> Select DECODE ('&a', 'a', DECODE ('&p', 'no', ENAME, JOB))
  2  	      AS first
  3  from   emp
  4  order  by DECODE ('&a', 'a', DECODE ('&p', 'no', ENAME, JOB)) DESC
  5  /
old   1: Select DECODE ('&a', 'a', DECODE ('&p', 'no', ENAME, JOB))
new   1: Select DECODE ('a', 'a', DECODE ('no', 'no', ENAME, JOB))
old   4: order  by DECODE ('&a', 'a', DECODE ('&p', 'no', ENAME, JOB)) DESC
new   4: order  by DECODE ('a', 'a', DECODE ('no', 'no', ENAME, JOB)) DESC

FIRST
----------
WARD
TURNER
SMITH
SCOTT
MILLER
MARTIN
KING
JONES
JAMES
FORD
CLARK
BLAKE
ALLEN
ADAMS

14 rows selected.

scott@ORA92> Select DECODE ('&p', 'no', ENAME, JOB)
  2  	      AS second
  3  from   emp
  4  order  by DECODE ('&p', 'no', ENAME, JOB) ASC
  5  /
old   1: Select DECODE ('&p', 'no', ENAME, JOB)
new   1: Select DECODE ('no', 'no', ENAME, JOB)
old   4: order  by DECODE ('&p', 'no', ENAME, JOB) ASC
new   4: order  by DECODE ('no', 'no', ENAME, JOB) ASC

SECOND
----------
ADAMS
ALLEN
BLAKE
CLARK
FORD
JAMES
JONES
KING
MARTIN
MILLER
SCOTT
SMITH
TURNER
WARD

14 rows selected.

scott@ORA92> DEFINE a = 'a'
scott@ORA92> DEFINE p = 'yes'
scott@ORA92> Select DECODE ('&a', 'a', DECODE ('&p', 'no', ENAME, JOB))
  2  	      AS first
  3  from   emp
  4  order  by DECODE ('&a', 'a', DECODE ('&p', 'no', ENAME, JOB)) DESC
  5  /
old   1: Select DECODE ('&a', 'a', DECODE ('&p', 'no', ENAME, JOB))
new   1: Select DECODE ('a', 'a', DECODE ('yes', 'no', ENAME, JOB))
old   4: order  by DECODE ('&a', 'a', DECODE ('&p', 'no', ENAME, JOB)) DESC
new   4: order  by DECODE ('a', 'a', DECODE ('yes', 'no', ENAME, JOB)) DESC

FIRST
----------
SALESMAN
SALESMAN
SALESMAN
SALESMAN
PRESIDENT
MANAGER
MANAGER
MANAGER
CLERK
CLERK
CLERK
CLERK
ANALYST
ANALYST

14 rows selected.

scott@ORA92> Select DECODE ('&p', 'no', ENAME, JOB)
  2  	      AS second
  3  from   emp
  4  order  by DECODE ('&p', 'no', ENAME, JOB) ASC
  5  /
old   1: Select DECODE ('&p', 'no', ENAME, JOB)
new   1: Select DECODE ('yes', 'no', ENAME, JOB)
old   4: order  by DECODE ('&p', 'no', ENAME, JOB) ASC
new   4: order  by DECODE ('yes', 'no', ENAME, JOB) ASC

SECOND
----------
ANALYST
ANALYST
CLERK
CLERK
CLERK
CLERK
MANAGER
MANAGER
MANAGER
PRESIDENT
SALESMAN
SALESMAN
SALESMAN
SALESMAN

14 rows selected.

scott@ORA92> DEFINE a = 'b'
scott@ORA92> DEFINE p = 'no'
scott@ORA92> Select DECODE ('&a', 'a', DECODE ('&p', 'no', ENAME, JOB))
  2  	      AS first
  3  from   emp
  4  order  by DECODE ('&a', 'a', DECODE ('&p', 'no', ENAME, JOB)) DESC
  5  /
old   1: Select DECODE ('&a', 'a', DECODE ('&p', 'no', ENAME, JOB))
new   1: Select DECODE ('b', 'a', DECODE ('no', 'no', ENAME, JOB))
old   4: order  by DECODE ('&a', 'a', DECODE ('&p', 'no', ENAME, JOB)) DESC
new   4: order  by DECODE ('b', 'a', DECODE ('no', 'no', ENAME, JOB)) DESC

FIRST
----------















14 rows selected.

scott@ORA92> Select DECODE ('&p', 'no', ENAME, JOB)
  2  	      AS second
  3  from   emp
  4  order  by DECODE ('&p', 'no', ENAME, JOB) ASC
  5  /
old   1: Select DECODE ('&p', 'no', ENAME, JOB)
new   1: Select DECODE ('no', 'no', ENAME, JOB)
old   4: order  by DECODE ('&p', 'no', ENAME, JOB) ASC
new   4: order  by DECODE ('no', 'no', ENAME, JOB) ASC

SECOND
----------
ADAMS
ALLEN
BLAKE
CLARK
FORD
JAMES
JONES
KING
MARTIN
MILLER
SCOTT
SMITH
TURNER
WARD

14 rows selected.

scott@ORA92> DEFINE a = 'b'
scott@ORA92> DEFINE p = 'yes'
scott@ORA92> Select DECODE ('&a', 'a', DECODE ('&p', 'no', ENAME, JOB))
  2  	      AS first
  3  from   emp
  4  order  by DECODE ('&a', 'a', DECODE ('&p', 'no', ENAME, JOB)) DESC
  5  /
old   1: Select DECODE ('&a', 'a', DECODE ('&p', 'no', ENAME, JOB))
new   1: Select DECODE ('b', 'a', DECODE ('yes', 'no', ENAME, JOB))
old   4: order  by DECODE ('&a', 'a', DECODE ('&p', 'no', ENAME, JOB)) DESC
new   4: order  by DECODE ('b', 'a', DECODE ('yes', 'no', ENAME, JOB)) DESC

FIRST
----------















14 rows selected.

scott@ORA92> Select DECODE ('&p', 'no', ENAME, JOB)
  2  	      AS second
  3  from   emp
  4  order  by DECODE ('&p', 'no', ENAME, JOB) ASC
  5  /
old   1: Select DECODE ('&p', 'no', ENAME, JOB)
new   1: Select DECODE ('yes', 'no', ENAME, JOB)
old   4: order  by DECODE ('&p', 'no', ENAME, JOB) ASC
new   4: order  by DECODE ('yes', 'no', ENAME, JOB) ASC

SECOND
----------
ANALYST
ANALYST
CLERK
CLERK
CLERK
CLERK
MANAGER
MANAGER
MANAGER
PRESIDENT
SALESMAN
SALESMAN
SALESMAN
SALESMAN

14 rows selected.

Report message to a moderator

Re: decode and order by [message #132866 is a reply to message #132722]

Tue, 16 August 2005 08:20

bella13
Messages: 90
Registered: July 2005

Member

Thnx Barbara. Your example was simple and straitforward. Understood it beautifully.

Thnx for your time.

CHeers,

Report message to a moderator

Previous Topic:	Loading date field with CCYYMMDD format using SQL*Loader
Next Topic:	Help with SQL Query

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu May 23 00:09:43 CDT 2013

Total time taken to generate the page: 0.17722 seconds