8i CASE dates

Hi. I'm checking an Oracle 8i table for invalid dates and outputing them as NULL, and I'm having problems with CASE and DATE datatypes.

SELECT workflow_id,
  CASE
   WHEN workflow_date > sysdate THEN NULL    WHEN workflow_date < TO_DATE('1800-01-01', 'YYYY-MM-DD') THEN NULL     ELSE workflow_date
  END AS workflow_date
FROM bl_workflow

Here's what I get when I run it:

SQL> /
  ELSE workflow_date

       *
ERROR at line 5:
ORA-00932: inconsistent datatypes

bl_workflow.workflow_date is a DATE type. If I re-run the statement changing line 5 to:

  ELSE TO_DATE(workflow_date)

...it runs (but the dates are out 100 years). It's as if only the WHERE clause sees workflow_date as a CHAR.

Bradley Crockett
Duncan BC Received on Wed Jul 03 2002 - 16:10:15 CEST