CODEWITHSUNDEEP
plsqlcase
Chihuahua Enthusiast
Chihuahua Enthusiast

Reputation: 1580

PLSQL: Multiple values in CASE result

I am dynamically setting the columns to order on for my PLSQL query, based on this answer (and many others like it). I want to add a default case where it sorts on three columns.

Here's my attempt:

-- ... long SQL query
--      ps_global_order is the column to sort on, passed in to stored procedure.
--      ps_global_order_dir is the direction to sort on, similarly passed in. 
order by 
  case 
    -- this does not compile (00905 missing keyword):
    when ps_global_order is null then name, phone, email 
    -- this compiles, but I need it on the three columns:
    -- when ps_global_order is null then name  
  end,
  case
    when ps_global_order_dir <> 'ASC' then ''
    when ps_global_order like 'name' then name
  end,
  case
    when ps_global_order_dir <> 'ASC' then ''
    when ps_global_order like 'phone' then phone
  end,
  case
    when ps_global_order_dir <> 'ASC' then ''
    when ps_global_order like 'email' then email
  end,
  case
    when ps_global_order_dir <> 'DESC' then ''
    when ps_global_order like 'name' then name
  end,
  case
    when ps_global_order_dir <> 'DESC' then ''
    when ps_global_order like 'phone' then phone
  end,
  case
    when ps_global_order_dir <> 'DESC' then ''
    when ps_global_order like 'email' then email 
  end

Everything is working fine, but I want to specify those three columns to be sorted on.

Is this possible with the CASE statement? Must I resort to dynamic SQL or is there a workaround that I'm missing?

Upvotes: 0

Views: 62

Answers (2)

Chihuahua Enthusiast
Chihuahua Enthusiast

Reputation: 1580

I hate to answer my own question so soon, but I figured it out:

order by 
    case  when nvl(ps_global_order, ' ') = ' ' then name end,
    case  when nvl(ps_global_order, ' ') = ' ' then phone end,
    case  when nvl(ps_global_order, ' ') = ' ' then email end,
    case
        when ps_global_order_dir <> 'ASC' then ''
    -- continues...

This answer was great help but it was buried in Google results. How this works without the comma eludes me though.

Upvotes: 0

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50027

Do it like this:

-- ... long SQL query
--      ps_global_order is the column to sort on, passed in to stored procedure.
--      ps_global_order_dir is the direction to sort on, similarly passed in. 
order by 
  case 
    when ps_global_order is null then name
    else null
  end,
  case
    when ps_global_order is null then phone
    else null
  end,
  case
    when ps_global_order is null then email
    else null
  end,
  case
    when ps_global_order_dir <> 'ASC' then null
    when ps_global_order like 'name' then name
  end,
  case
    when ps_global_order_dir <> 'ASC' then null
    when ps_global_order like 'phone' then phone
  end,
  case
    when ps_global_order_dir <> 'ASC' then null
    when ps_global_order like 'email' then email
  end,
  case
    when ps_global_order_dir <> 'DESC' then null
    when ps_global_order like 'name' then name
  end,
  case
    when ps_global_order_dir <> 'DESC' then null
    when ps_global_order like 'phone' then phone
  end,
  case
    when ps_global_order_dir <> 'DESC' then null
    when ps_global_order like 'email' then email 
  end

Note that I've replaced all the occurrences of '' with NULL, which is clearer IMO.

Upvotes: 1

Related Questions