Re: 8i CASE dates

Bradley Crockett <brad_at_crockett.extraneous.ca> wrote in message news:<3D2305CD.6050509_at_crockett.extraneous.ca>...
> 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

Change NULL in lines 3 and 4 to to_date(NULL). By default, NULL has varchar2 datatype and datatype of CASE expression is defined by first THEN expression.

> 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 - 21:12:10 CEST