This content has been marked as final. Show 2 replies
Welcome to the forum!
This is NOT the forum for SQL or PL/SQL questions. As the title says this forum is for sql developer questions only.
If you have any additional comments or follow please DO NOT followup in this forum but repost the question in the sql and pl/sql forum.
SQL and PL/SQL
In a table OT_C_S_R_HEAD in this there is a column "CSR_STATUS" having datatype "Number" and i have to apply case on this that if "2 or null is no, 1 is yes".
But apply getting some error "ORA-00932: inconsistent datatypes: expected NUMBER got CHAR"
The reply that you marked CORRECT will not cause the exception you were receiving but it will also NOT give you the results you want.
You said you wanted 'NO' if the column has a NULL value but that query gives you a NULL. The query will also give you NULL for values other than 1 or 2 because there is no ELSE clause. Except in unusual cases there should always be an ELSE clause as a 'catch all'.
create table a (col1 number) insert into a values (null) insert into a values (1) insert into a values (2) insert into a values (3) SELECT col1, CASE col1 WHEN 2 THEN ('NO') WHEN 1 THEN ('YES') WHEN NULL THEN ('NO') END test FROM a COL1,TEST , 1,YES 2,NO 3,
If all you want is YES for a 1 and NO for anything else just use
SELECT col1, CASE col1 WHEN 1 THEN ('YES') ELSE ('NO') END test FROM a COL1,TEST ,NO 1,YES 2,NO 3,NO