SQL> select case when val < 10 then 'Small'
2 when val > 15 then 'Large'
4 from (select 1 val from dual union all
5 select 9 from dual union all
6 select 21 from dual
You can use IF-THEN in a query; those keywords have meaning only in PL/SQL.
The WHERE clause is similar to an IF-THEN-ELSE operation. I don't know what you want, but the smart way to do it might be using the WHERE clause and JOIN conditions.
If you post some sample data (CREATE TABLE and INSERT statements) and the results you want from that data, somebody can help you get those results.
CASE returns a single expression. If you need to return several values, you could concatenate them into a delimited string (which the CASE expression can return) and then split that string into its sub-strings.
STATE.STATE_CODE || '-' || STATE.STATE_NAME
WHERE state.state_code in(
WHEN 'AK' THEN 'AK'
WHEN 'AL' THEN 'AL'
I would like to select all the values for the state_code from the state table in the else statement.
state.state_code || '-' || state.state_name
WHERE state.state_code = substr('01X01##YYXXX',8,2)
OR substr('01X01##YYXXX',8,2) NOT IN ('AK', 'AL') AND state.state_code IN ('AK','AL','AR','AZ','CA')