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

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 DOG into value 1, CAT into value 2, EEL into value 3 and others into value 5. 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 2

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.