This content has been marked as final. Show 9 replies
you can use CASE
SQL> select case when val < 10 then 'Small' 2 when val > 15 then 'Large' 3 end 4 from (select 1 val from dual union all 5 select 9 from dual union all 6 select 21 from dual 7 ) 8 / CASEW ----- Small Small Large
please let us know what do you want to do in the query, you can use IF else in the pl sql block.
We are already using case but we need to return multiple values in the else clause. So, we were trying to use if then else with case.
you can't use IF THEN ELSE in queries, but you can nest CASE expressions
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
Hi!1 person found this helpful
SELECT DISTINCT state.state_code || '-' || state.state_name FROM state, ppp 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')
This works like a charm... Thank You so much!!
Appreciate all your help.