I would like to make a SELECT and t 1

[Iby]

I would like to make a SELECT and to use with CASE cause or something else because I have to handle more ELSE.
Something like this:

SELECT STATUS FROM apple_tree WHERE STATUS = (CASE WHEN STATUS = '1' THEN '1'
else (CASE WHEN STATUS = '2' THEN '2' end)
else (CASE WHEN STATUS = '3' THEN '3' end)
else (case when STATUS = '4' then '4' end)
else '1' end)

Just my problem that my statement never execute only just the main else.

 

[andypara]

Look up DECODE in any good Oracle book.

Eg.

SELECT DECODE(Name, 'Robert', 'Rob', 'Timothy', 'Tim', 'William', 'Will')
FROM
CUSTOMER;

This just searches the CUSTOMER table's Name column, returns all the names but substitues 'Rob' where it finds 'Robert' etc... So it is in effect an IF THEN statement.

 

[Iby]

Thank you andypara, I know the 'decode' so well but I wouldn't like to use that because in my case it looks like too slow and that's why I would like to choose an another possibility if it's possible.
Could you please to offer an another solution for that?

 

[andypara]

Do you want to use PL/SQL code?

 

[sfvb]

Here is an example:

SELECT decode(least(status,4),status,status,1)
FROM apple_tree;

 

[Iby]

Yes andypara, I would like to use PL/SQL code.
Do you have any idea?

 

[ThomVF]

Your original syntax was wrong.
It should be:

SELECT STATUS FROM apple_tree
WHERE STATUS =
(CASE WHEN STATUS = '1' THEN '1'
WHEN STATUS = '2' THEN '2'
WHEN STATUS = '3' THEN '3'
WHEN STATUS = '4' THEN '4'
ELSE '1' END)

 

[sfvb]

I'm confused. What is the SQL statement above going to do when status = 5?

If I parse the values, then I get:
SELECT STATUS FROM apple_tree
WHERE '5' = '1'

Is this what it's supposed to do when status = 5?