Problem solve
Get help with specific problems with your technologies, process and projects.
Using CASE in the SQL ORDER BY clause
Read about why one Oracle user is getting an error when trying to use CASE in the SQL ORDER BY clause.
I am trying to use a CASE expression in the ORDER BY clause of my SQL statement, but I want to order by more than one column. I keep getting a "missing keyword" error when I try to compile this.
ORDER BY CASE WHEN sortorder = 1 THEN x WHEN sortorder = 2 THEN x,y END;
"sortorder" is a variable I pass in, by the way.
The first thing to notice is that in both cases, for sortorder 1 and also for sortorder 2, the rows must be sorted into major sequence on the "x" column. So we can simplify the ORDER BY clause like this:
ORDER
BY x
, ... something for sortorder 2
Now the only thing to worry about is sortorder 2. This requires that the rows be in sequence by the "y" column values within each "x" value.
But what if we simply do this:
ORDER
BY x
, y
Let's recap. If sortorder is 2, then the rows will be in sequence by "y" within "x," and we're all set, right? But if sortorder is 1, then the rows will also be in sequence by "y" within "x," but for sure they'll be in sequence by "x." So that's okay too, right?
You don't need a CASE expression at all.
ORDER BY
CASE
WHEN :P_SORT_BY = '1' THEN 1
WHEN :P_SORT_BY = '5' THEN 5 desc
WHEN :P_SORT_BY = null THEN 1,3,4
END;
":P_SORT_BY" is a variable I pass in, by the way.