Re: ORA-01790: expression must have same datatype as corresponding expression

Chris Neubauer wrote:
>
> Hi,
>
> the following SQL doesn't work:
>
> select a.num1, a.num2, a.num3, a.num4, a.num5, a.num6, a.num7, a.num8
> from table1
> union all
> select a.num1, a.num2, a.num3, a.num4, NULL as num5, NULL as num6, NULL as
> num7, NULL num8
> from table2;
>
> I need a merged view on both tables, and as table2 does not contain num5 to
> num8, I want to insert the columns with the value NULL. But that doesn't
> work, I get the following Oracle error message:
>
> ORA-01790: expression must have same datatype as corresponding expression
>
> I could use 0 (zero) instead of NULL, but as zero represents a valid value
> in my program, I decided to use NULL.
> Is there any way to state the datatype in my select statement?

select a.num1, a.num2, a.num3, a.num4, a.num5, a.num6, a.num7, a.num8 from table1
union all
select a.num1, a.num2, a.num3, a.num4, to_number(NULL) as num5, to_number(NULL) as num6, to_number(NULL) as num7, to_number(NULL) num8 from table2;

Hth,

Knut Received on Tue Jul 02 2002 - 06:32:51 CDT