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

Question

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

Why do you need the in_sort_order parameter at all if it's determined by data type, which you already know? — Alex Poole
– Alex Poole, Commented Mar 14, 2015 at 0:01
I just added the parameter because when i tried it with query like this - SELECT col1, col2, col3 from tabl e1 where col1 > 1000 ORDER BY decode(in_sort_column,'col1', col1 desc, 'col2', col2 asc, 'col3', col3 asc); ... this does not work — ravi
– ravi, Commented Mar 14, 2015 at 0:13
To make it simple and verbose, use a case expression in order by. Look at stackoverflow.com/a/26033176/3989608 — Lalit Kumar B
– Lalit Kumar B, Commented Mar 14, 2015 at 10:30

Gordon Linoff · Accepted Answer · 2015-03-13 23:44:32Z

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.

This worked and i dont need to pass the in_sort_order parameter too. I changed my decode to case statements like this - order by (case when in_sort_column = 'col1' then col1 end) ASC, (case when in_sort_column = 'col2' then col2 end) DESC, (case when in_sort_column = 'col3' then col3 end) ASC

Alex Poole · Accepted Answer · 2015-03-14 00:29:27Z

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.

It was my idea too ;-) But I also added date type column to test data and decode produced error ORA-00932.
Yep, using case instead avoids such issues, as Gordon had already poiinted out.

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Linked

Related