Oracle sort substitution variables

Question

I need to sort substitution variables so I can refer to them later like this:

WITH  
vars AS (SELECT SORT(:var1,:var2, :var3) FROM DUAL)  
SELECT least_var, greater_var, greatest_var FROM vars;

Answer 1

Since you have substitution variables, you obviously have some host language. So one approach is to order the three values in the host language before you call the SQL query.

Another approach is to use the following query (e.g. in combination with the WITH clause):

select
    least(:var1, :var2, :var3) as least_var,
    case
      when :var2 < :var1 and :var1 < :var3
        or :var2 > :var1 and :var1 > :var3 then :var1
      when :var1 < :var2 and :var2 < :var3
        or :var1 > :var2 and :var2 > :var3 then :var2
      else :var3
    end as greater_var,
    greatest(:var1, :var2, :var3) as greatest_var
from dual

Creating a SORT function as you propose won't really work as a function cannot return a construct that serves as three columns. If you want to use functions, then use three of them: the built-in functions LEAST and GREATEST as well as a user-defined one called GREATER.