This content has been marked as final. Show 9 replies
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.
Below is the query:
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.
It looks like passing back a delimited list would work best for this problem.
Assuming no state_code is a substring of another (and, if they are all the same length, this will always be so), then you can say something like:
This also assumes that no state_code contains ','.
WHERE INSTR ( CASE SUBSTR (var_cols_89_not_always_YY, 8, 2) WHEN 'AK' THEN 'AK' WHEN 'AL' THEN 'AL' ELSE 'AK,AL,AR,AZ,CA' END , state_code ) > 0