CODEWITHSUNDEEP
sqloracle-database
Tommen
Tommen

Reputation: 123

CASE ... ELSE *ignore* in Oracle SQL

I'm trying to use CASE in a SELECT statement that will change two values of the possible 12 in a column.

CASE WHEN grade = 0 THEN 'R2'
WHEN grade = -1 THEN 'R1'
ELSE --ignore--
END AS "Grade level"

Does anyone have any idea what to replace the --ignore-- with, so that only those two possible values will be altered? I don't really want to keep writing out separate WHENs for each value in the column.

Upvotes: 0

Views: 13832

Answers (5)

Mike
Mike

Reputation: 25

you could put the whole thing in as a subquery in your FROM clause, then reference the column you named in a separate WHERE clause below it...

SELECT * 
FROM
    (SELECT.....
    CASE WHEN grade = 0 THEN 'R2'
    WHEN grade = -1 THEN 'R1'
    ELSE NULL
    END AS "Grade level" 
    FROM .....
    WHERE.....)

WHERE "Grade level" is not null
;

Upvotes: 0

Rigel1121
Rigel1121

Reputation: 2026

Just remove the ELSE part:

CASE WHEN grade = 0 THEN 'R2'
WHEN grade = -1 THEN 'R1'
END AS "Grade level"

Upvotes: 4

Gordon Linoff
Gordon Linoff

Reputation: 1270071

Perhaps you want this?

(CASE WHEN grade = 0 THEN 'R2'
      WHEN grade = -1 THEN 'R1'
      ELSE grade
 END) AS "Grade level"

I hesitate to suggest this, because grade appears to be a numeric quantity but the case statement is evaluating to a string. However, it appears to be close to what you want.

EDIT:

As expected, you need to convert something. I would suggest:

(CASE WHEN grade = 0 THEN 'R2'
      WHEN grade = -1 THEN 'R1'
      ELSE cast(grade as varchar2(255))
 END) AS "Grade level"

However, if you want to set grade to the value, then you will just get another conversion error, because you cannot assign 'R2' to a number.

Upvotes: 1

mason
mason

Reputation: 32704

Since grade is a number, you need to convert it to a character so it'll fit with R1 and R2.

CASE WHEN grade = 0 THEN 'R2'
WHEN grade = -1 THEN 'R1'
ELSE to_char(grade)
END AS "Grade level"

Upvotes: 4

void
void

Reputation: 7890

Depends on your complete query maybe you could bring the reverse logic of ignore condition to where clause:

CASE WHEN grade = 0 THEN 'R2'
WHEN grade = -1 THEN 'R1'
ELSE -- 1 or any value you can put here it dose not come in result becase of where clause
END AS "Grade level"
.....
where grade in (0,-1)

EDIT: if you be able to use in where clause you can make above query more simple (reduce one when):

...
CASE WHEN grade = 0 THEN 'R2'
     ELSE 'R1'
END AS "Grade level"
.....
where grade in (0,-1)

Upvotes: 1

Related Questions