Re: Null dates in a union query question

From: Marc Eilens <M.Eilens_at_DeutschePost.de>
Date: Wed, 23 Sep 1998 11:08:40 +0200
Message-ID: <3608BA97.8F88304F@DeutschePost.de>

PMG wrote:

> Can somebody tell me how to get around this problem:
>
> create or replace view x as
> select 'pete' NAME,
> null ADATE
> from dual
> union all
> select null NAME,
> sysdate ADATE
> from dual
>
> I get ORA-01790: expression must have same datatype as corresponding
> expression in line 3 of the SQL.
>
> In other words, how do I insert a NULL into a column which will be a
> date field.
>
> TIA
>
> Pete

Hi,

use the to_date() function, following should work:

create or replace view x as

   select 'pete'     NAME,
             to_date(null)      ADATE

   from dual
union all

   select null       NAME,
   sysdate         ADATE

 from dual

--
Marc Eilens, Student
FB Angewandte Informatik, FH Trier, Germany [ Applied Computer Science, Tech. University of Trier ] mailto: eilensm_at_fh-trier.de Received on Wed Sep 23 1998 - 04:08:40 CDT