ORDER BY DECODE(BLAH, [COLUMN NUMBER]) on a single column query. How does it work?

Question

Hi I need help to understand the decode part of a query that goes something like the following.

SELECT ax.animal_code    
FROM  raw_animal_xref ax,
      animal_xref_type axt
WHERE ax.animal_mnemonic = l_animal_mnemonic -- Example 'COUGAR'
AND   ax.animal_code_type = axt.animal_code_type
ORDER BY DECODE (animal_type, 
                l_type_to_be_matched, -1, -- Example 'CATS'
                l_current_type, 0, -- Example 'BIG CATS'
                nvl(axt.type_search_priority, 100)) ASC; -- EXAMPLE 'Big Cats' Priority is 1

Since this query returns only 1 query, I'm a little stumped on how the ORDER BY works with the different non-existing column numbers supplied by DECODE. The query works as a cursor to find a unique code for the animal in question given a animal mnemonic the current animal type and the type to be matched with.

I'm thinking that DECODE returns the different column numbers to ORDER BY with and I tried experimenting with a different simple single column selects on some other tables with ORDER by '-1', '0' and '100' and the ORDER by seems to fail for 0 and 100. Why does it work with -1 or any of the other numbers?

Hope someone can explain this to me. Thanks!

Answer 1

It creates the set that will be used for ordering.

If animal_type = l_type_to_be_matched, use a -1 as the sort value for that row
else if animal_type = l_current_type, use 0 as the sort value of that row
else if axt.type_search_priority is null then use 100 as the sort value of that row
else use axt.type_search_priority as the sort value for that row.

It gives a kind of conditional sort cirteria. Often used to ensure that certain items are always at the top or bottom of a sorted set.

Answer 2

The ORDER BY can use one of three expressions. Firstly an alias of the select list, secondly the number of a column in the select list or thirdly an SQL expression which may use zero or more columns from the source tables.

So when you use ORDER BY SUBSTR(col,2,10) you order by a 10 character substring of the column value starting from the second character.

Similarly when use

ORDER BY decode(col,'DOG',1,'CAT',2,'EEL', 3, 5)

you translate 'CAT' into the value 1, DOG into the value 2 and EEL into the value 3, then order by the resulting numeric value (ie DOG first, then CAT, then EEL, finally anything else).

You can achieve the same ordering using

ORDER BY decode(col,'DOG','A','CAT','B','EEL', 'C', 'D')

Answer 3

Referring to this portion of your question:

I'm thinking that DECODE returns the different column numbers to ORDER BY with and I tried experimenting with a different simple single column selects on some other tables with ORDER by '-1', '0' and '100' and the ORDER by seems to fail for 0 and 100. Why does it work with -1 or any of the other numbers?

Your confusion is understandable; but no, the values returned by the DECODE are not interpreted as column numbers.

Oracle supports a little syntactic shortcut, in which columns of the result set can be referred to positionally in an ORDER BY clause. So for example this:

SELECT a, b FROM some_table ORDER BY 1,2

is the same as:

SELECT a, b FROM some_table ORDER BY a,b

However, this positional notation can only be done with non-negative integer literals. If the ORDER BY includes an expression which produces a numeric value, it will not be interpreted as a column number, but as an actual value to be sorted on. Also, negative numeric literals are interpreted as sort values, not as column numbers.

SELECT * FROM table ORDER BY -1 will sort all rows on the constant value -1 (effectively no sorting).

SELECT * FROM table ORDER BY 0 will return an error because 0 is an invalid column number.

SELECT * FROM table ORDER BY 1 will sort all rows on the value of the first column in the table.

SELECT * FROM table ORDER BY 100 will sort all rows on the value of the 100th column in the table, or return an error if there are fewer than 100 columns.

SELECT * FROM table ORDER BY TO_NUMBER('1') will sort all rows on the constant value 1.

I haven't tested this out thoroughly, but looking at some execution plans, it appears that you can even specify a non-integer numeric literal, and it will be rounded down and used as a column number.

SELECT * FROM table ORDER BY 1.5 appears to sort on the value of the first column.