CASE/LAG statements, inconsistent datatypes ??

[nchapman]

I have a query which has a lag statement nested within a CASE statement. Please see the output from sql below.

Could anyone tell me why in order to get rid of the "ORA-00932: inconsistent datatypes" oracle error I have to place either a "to_date(....'dd-mon-rrrr') around the date_updated field, OR a to_date(to_char(....'dd-mon-yyyy'),'dd-mon-yyyy'). Bear in mind, that the date_updated field is defined as a DATE field on the customer_dim table.

SQL> r
1 select customer_key,
2 case when lag(date_updated,1,to_date(NULL))
3 over (partition by unified_involved_party_id
4 order by date_updated) IS NULL
5 then to_date('01-JAN-1999','dd-MON-yyyy')
6 else date_updated
7 end AS FROM_DATE
8 from customer_dim
9* where unified_involved_party_id = '0010002021965ICBS'
else date_updated
*
ERROR at line 6:
ORA-00932: inconsistent datatypes


SQL> 6 else to_date(date_updated,'dd-mon-rrrr')
SQL> l
1 select customer_key,
2 case when lag(date_updated,1,to_date(NULL))
3 over (partition by unified_involved_party_id
4 order by date_updated) IS NULL
5 then to_date('01-JAN-1999','dd-MON-yyyy')
6 else to_date(date_updated,'dd-mon-rrrr')
7 end AS FROM_DATE
8 from customer_dim
9* where unified_involved_party_id = '0010002021965ICBS'
SQL> /

CUSTOMER_KEY FROM_DATE
------------ ---------
5 01-JAN-99
6 31-MAY-00
7 01-JUN-00
8 05-JUN-00
9 09-JUN-00
10 10-JUN-00
11 18-JUN-00
12 19-JUN-00
13 20-JUN-00
14 21-JUN-00
15 22-JUN-00
16 23-JUN-00
17 24-JUN-00
18 26-JUN-00
19 27-JUN-00
20 28-JUN-00
21 29-JUN-00
22 02-JUL-00
23 03-JUL-00
24 31-JAN-02
25 04-MAR-02
26 16-MAY-02
27 05-JUN-02
28 15-SEP-02
29 21-JAN-03

25 rows selected.

SQL> 6 else to_date(to_char(date_updated,'dd-mon-yyyy'),'dd-mon-yyyy')
SQL> /

CUSTOMER_KEY FROM_DATE
------------ ---------
5 01-JAN-99
6 31-MAY-00
7 01-JUN-00
8 05-JUN-00
9 09-JUN-00
10 10-JUN-00
11 18-JUN-00
12 19-JUN-00
13 20-JUN-00
14 21-JUN-00
15 22-JUN-00
16 23-JUN-00
17 24-JUN-00
18 26-JUN-00
19 27-JUN-00
20 28-JUN-00
21 29-JUN-00
22 02-JUL-00
23 03-JUL-00
24 31-JAN-02
25 04-MAR-02
26 16-MAY-02
27 05-JUN-02
28 15-SEP-02
29 21-JAN-03

25 rows selected.

SQL> l
1 select customer_key,
2 case when lag(date_updated,1,to_date(NULL))
3 over (partition by unified_involved_party_id
4 order by date_updated) IS NULL
5 then to_date('01-JAN-1999','dd-MON-yyyy')
6 else to_date(to_char(date_updated,'dd-mon-yyyy'),'dd-mon-yyyy')
7 end AS FROM_DATE
8 from customer_dim
9* where unified_involved_party_id = '0010002021965ICBS'
SQL> spool off