CODEWITHSUNDEEP
sqloracle-databaseplsql
ravi
ravi

Reputation: 1847

How to generate Dynamic Order by clause in PL/SQL procedure?

I am trying to write a PL/SQL procedure which will have the SQL query to get the results. But the requirement is that the order by can be dynamic and is mainly for sorting the columns in the screen. I am passing 2 parameters to this procedure - in_sort_column and in_sort_order. The requirement is such that on text columns the sorting is in ASC and for numbers it is DESC. My query looks something like this without adding the in_sort_order - 

SELECT col1, col2, col3 from tabl e1 where col1 > 1000 
ORDER BY decode(in_sort_column,'col1', col1, 'col2', col2, 'col3', col3);

I am not able to figure out how to use the in_sort_order parameter in this case. Can someone who has done this before help out ?

Thanks

Upvotes: 3

Views: 9808

Answers (2)

Alex Poole
Alex Poole

Reputation: 191570

Since the requirement is based on data type, you could just negate the numeric columns in your decode; if col1 is numeric and the others are text then:

ORDER BY decode(in_sort_column, 'col1', -col1, 'col2', col2, 'col3', col3);

But this is going to attempt to convert the text columns to numbers. You can swap the decode or around to avoid that, but you then do an implicit conversion of your numeric column to a string, and your numbers will then be sorted alphabetically - so 2 comes after 10, for example.

So Gordon Linoff's use of case is better, and you can still negate the col1 value with that to make the numbers effectively sort descending.

Upvotes: 4

Gordon Linoff
Gordon Linoff

Reputation: 1271241

When doing a dynamic sort, I recommend using separate clauses:

order by (case when in_sort_column = 'col1' then col1 end),
         (case when in_sort_column = 'col2' then col2 end),
         (case when in_sort_column = 'col3' then col3 end)

This guarantees that you will not have an unexpected problem with type conversion, if the columns are of different types. Note that case return NULL without an else clause.

Upvotes: 6

Related Questions