Re: Multiple column sorting with a decode

voidhack_at_gmail.com wrote:
: Hello,

: I am trying to sort on multiple columns using a parameter that I
: specify as the sort order.
: For example,
: ...
: ORDER BY DECODE(:param,1, '1, 3', 2, '1,4')

The decode returns the result of the decode and the rows are compared using that value for each row.

Depending on :param, you are either doing a

	select * from table 
	ORDER BY '1, 3';

or 
        select * from table
        ORDER BY '1, 4';

Notice the sort criteria is a literal value! The literal value means that every row is the same from the point of view of the sort - i.e. the sorting will not change the original order. (Or Oracle gives an error, which I can't test for right now).

You must return the _values_ of the rows for the sort to do anything. I think you will need to use the columns name, I don't know how else to pull out the value when you aren't using using the notation "ORDER BY 1,2,3 etc", though someone else may know better.

	ORDER BY 
		1, -- columns 1, or its name
		DECODE(:param,	
			1 , COL3_NAME ,	-- use the column's real name!
			2 , COL4_NAME ,	-- use the column's real name!
			''              -- default value is nothing 
		      )

So if col3 is actually 'FIRST_NAME' and col4 is actually LAST_NAME then the above would be

        ORDER BY
                1,
                DECODE(:param,
                        1 , FIRST_NAME
                        2 , LAST_NAME
                        ''              -- default value is nothing
                      )

(I am assuming you can still use "1" to access column 1.)

--

This programmer available for rent.