Geek Answers Handbook

Oracle dynamic DESC and ASC are fine

The by order is dynamic, but the sort order is static.

SELECT ... Order By CASE WHEN InputParam = 'PRICE' THEN OFFER_PRICE END DESC, CASE WHEN InputParam = 'ENDING SOON' THEN EXPIRY_DATE END DESC, CASE WHEN InputParam = 'DISCOUNT' THEN DISC_PERCENTAGE END DESC, CASE WHEN InputParam = 'SAVING' THEN SAVING END DESC

Now I need to make sure that the sort order is also dynamic. Is there a way to do a dynamic sort order in the above query?

+8
sql oracle
source share
2 answers

If you also want to do a dynamic sort order (ASC / DESC), you can do the following:

 SELECT ... Order By CASE WHEN InputParam = 'PRICE' THEN l_so * OFFER_PRICE END, CASE WHEN InputParam = 'ENDING SOON' THEN l_so * (SYSDATE - EXPIRY_DATE) END, CASE WHEN InputParam = 'DISCOUNT' THEN l_so * DISC_PERCENTAGE END, CASE WHEN InputParam = 'SAVING' THEN l_so * SAVING END

with an l_so variable that contains 1 or -1, depending on which sort order you want.

+14
source share

This works for me:

 order by case when :dir_param = 'ASC' then case :col_param when 'col_1_identifier' then col_1_name when 'col_2_identifier' then col_2_name ... end end, case when :dir_param = 'DSC' then case :col_param when 'col_1_identifier' then col_1_name when 'col_2_identifier' then col_2_name ... end end desc

or

 order by case when :dir_param = 'ASC' and :col_param = 'col_1_identifier' then col_1_name end, case when :dir_param = 'DSC' and :col_param = 'col_1_identifier' then col_1_name end desc, case when :dir_param = 'ASC' and :col_param = 'col_2_identifier' then col_2_name end, case when :dir_param = 'DSC' and :col_param = 'col_2_identifier' then col_2_name end desc

Replace literals, variable names, and column names with ones that match your situation. Oracle seemed to be very selective about the definition of the desc direction classifier.

+7
source share

More articles:


All Articles