4 Replies Latest reply: Nov 20, 2012 6:41 AM by 567822 RSS

    decode for order by combined with ass and desc

    567822
      Hi All,
      I've a query which's results needs to be ordered dynamically. The issue is that I need to order by some values in desc and some others in desc:
      select cust.id, cust.name, cust.region
      from   customer cust
      order by cust.id asc, cust.name desc, cust.region asc
      What I need now that if cust.sort_value = 1 then i need to order by cust.id asc, cust.name desc, cust.region asc
      and if the cust.sort_value = 2 need to order by cust.region asc

      Since cust.name is non numeric value I can't do
      select cust.id, cust.name, cust.region
      from   customer cust
      order by case when cust.sort_value = 1 then cust.id || cust.name*-1 ||cust.region end asc,
                  case when cust.sort_value= 2 then cust.region asc
      How could I be able to do the sorting based on the requirement that if sort_value = 1 I need the order by in this order: cust.id asc, cust.name desc, cust.region asc