CASE in v8i

The CASE statment became available in 8.1, and its well publicised that it will not work as static SQL within PL/SQL due to the different SQL semantics engines between SQL and PL/SQL.

However, here's another little idiosyncracy for you

SQL> select case when dummy='X' then sysdate else sysdate+1 end from dual;

CASEWHEND
---------
27-APR-04

1 row selected.

SQL> create table my_dual ( dummy, x ) as select 'X', sysdate from dual;

Table created.

SQL> select case when dummy='X' then x else x+1 end from my_dual;
select case when dummy='X' then x else x+1 end from my_dual
                                        *
ERROR at line 1:
ORA-00932: inconsistent datatypes

You need to explicitly cast the return value into a date for the SQL to work

SQL> select case when dummy='X' then x else cast(x+1 as date) end from my_dual;

CASEWHEND
---------
27-APR-04

1 row selected.

Yet another reason to move to version 9