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!
Advertisement
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