SQL – Where clause with case statement and wildcards

I’m working in Oracle’s APEX environment, trying to return all values in one case and specific values in another case. If you aren’t familiar with APEX, it uses low-code to produce a front-end page that is data driven; this case uses a dropdown to select from a list, and I’m comparing that list selection to values pulled from my database.

This creates the dropdown list (requires 2 columns – a display column [name], and a return column [id]):

select distinct 'All users' as name,
 '99999' as id
from real_table
union
select distinct name,
 id
from real_table

That input is stored in a variable we’ll call :LIST_INPUT. I want to select all values from another_table when ‘All users’ is selected, and only those associated with the particular user when their name/id is selected. Below is the code I have to try and achieve that, but no dice.

select name,
 id,
 other_col1,
 other_col2
from another_table
where case
  when :LIST_INPUT like '99999' then '%'
  else :LIST_INPUT
 end like id

This works fine when a real user id is selected, but returns nothing when the ‘All users’ value is selected. My logic here is that I’m asking it to compare a wildcard to the ID field, so it should return everything, but instead it returns nothing.

Thanks!

Answer

Probably case statement is not necesasary, look:

select name,
       id,
       other_col1,
       other_col2
  from another_table
 where id = :LIST_INPUT
    OR :LIST_INPUT like '99999' -- You can use any other condition here

Advertisement

Answer