CODEWITHSUNDEEP
sqloracle-database
Jericho
Jericho

Reputation: 10953

Dynamic Order by using Case in SQL

How to get dynamic Order by based on the parameter in Oracle sql,Base on the condition any one of the Dynamic Order by to be binded ,What is wrong with my query please help to solve this.Parameter value will be passed dynamically for user.

SELECT actionstatus,
       id 
  FROM rdt_orderdetail
 WHERE id IN (45565,44033,45761,45543,45495,43472,42462,43477)

CASE WHEN ':user' = 'supplier THEN ORDER BY id 
  ELSE ORDER BY actionstatus
END

Upvotes: 1

Views: 148

Answers (3)

G one
G one

Reputation: 2729

Try something like this:

SELECT actionstatus,
       id 
 FROM rdt_orderdetail
 WHERE id IN (45565,44033,45761,45543,45495,43472,42462,43477)
 order by (CASE WHEN ':user' = 'supplier THEN id 
                                         ELSE actionstatus END)

Example fiddle

Upvotes: 4

Emmanuel
Emmanuel

Reputation: 14209

You want to use dynamic order ? Then you'll have to use dynamic SQL :-)

create or replace function GET_DETAILS(iField in varchar2) return sys_refcursor is
  aQuery varchar2(1000);
  aCursor SYS_REFCURSOR;
begin
  aQuery := 'select actionstatus,id from rdt_orderdetail
             where id in (45565,44033,45761,45543,45495,43472,42462,43477)
             order by ' || case iField when 'sup' then 'id' else 'actionstatus' end;
  open aCursor for aQuery;
  return aCursor;
end;
/

Upvotes: 1

Alexander
Alexander

Reputation: 3179

You cannot use CASE outside of a SELECT clause.
Futhermore 'sup' always equals 'sup', so it doesn't make sence anyway.

And about whole logic - you ordering the whole resultset, so it either by id or by actionstatus. You can not order couple of rows by id, and the next 3 by actionstatus

Upvotes: 0

Related Questions