• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1042
  • Last Modified:

Order by Decode( )

I have a query created for a report that allows the user to select what information they wish to sort the end report by.  I uderstand that decode automatically type-casts the decode statement based on the first item decoded.  That is the problem: one of the items is a date field the rest are varchar2 fields if the date field is the first in the list then I cannot order by the other fields I get "literal does not match" or "expecting a number format..." if a varchar2 field is first in the list and the date field is selected to order by it is treated as a varchar and is not sorted properly.  Again I understand that is the way decode is supposed to work but there has to be a way around this.  I have tried to type cast from within the decode statement it does not work...at least not the way I tried.

select platform, engineer, end_date, part, complete_date, rcvd_date
from table
where....
order by (:sort, 'ENGINEER', engineer, 'EDD', end_date, 'PART', part)

the above will not correctly sort by end_date

select platform, engineer, end_date, part, complete_date, rcvd_date
from table
where....
order by (:sort,  'EDD', end_date, 'ENGINEER', engineer, 'PART', part)

the above will not correctly sort by engineer or part

any suggestions


0
Barovian
Asked:
Barovian
1 Solution
 
dsackerContract ERP Admin/ConsultantCommented:
Try:

order by decode(:sort,'ENGINEER',engineer,'EDD',to_char(end_date,'yyyymmddhh24miss'),'PART',part)

If you don't need the hours, take off the hh24miss.
0
 
izblankCommented:
Try this:

order by decode(:sort,  'EDD', end_date, NULL),
   decode(:sort, 'ENGINEER', engineer, NULL),
   decode(:sort, 'PART', part,NULL)
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now