NULL in union causing ORA-01790

[martinm]

Oracle 8.0.5.0

The following code fails when I return a NULL as c2 in the first part of the union, but is OK if the date is used.

Seems very odd to me!

Is this correct (hopefully not.....)

select  c1,
        c2 
from( 
 
select   t1.wip_due_date as c1,
         null as c2 --to_date('01/01/2002', 'dd/mm/yyyy') as c2
from     visib.mm_mrp_tmp t1
  where t1.due_date is null
    and t1.wip_due_date is not null
 
  union all 
 
select   to_date('01/01/2002', 'dd/mm/yyyy'),
         t1.prom
from     visib.mm_mrp_tmp t1
  where t1.due_date is null
    and t1.prom is not null
    and to_char(t1.prom,'dd/mm/yyyy') = '00/00/0000' 
    and t1.prom < '01 jan 3999'
    
    );

 

[SantaMufasa]

Martin,

It is reasonable for Oracle to throw an error on a UNION operation if it cannot unambiguously identify the datatype for a column...Oracle must ensure that datatypes between corresponding columns are compatible, and Oracle uses the datatype of the expressions appearing in the first SELECT of the UNION(s) to determine datatypes.

Since NULL can appear in virtually all column datatypes, Oracle cannot assign an unambiguous datatype to "c2".

It seems to me that since the datatype for "t1.prom" is certainly static and discernable, and since changing the order of your SELECTs will not change the result set, you should be able to work around your error with this code adjustment:

select c1,c2 
from( 
select   to_date('01/01/2002', 'dd/mm/yyyy') c1,
         t1.prom c2
from     visib.mm_mrp_tmp t1
  where t1.due_date is null
    and t1.prom is not null
    and to_char(t1.prom,'dd/mm/yyyy') = '00/00/0000' 
    and t1.prom < '01 jan 3999'
union all
select   t1.wip_due_date,
         null --to_date('01/01/2002', 'dd/mm/yyyy') as c2
from     visib.mm_mrp_tmp t1
  where t1.due_date is null
    and t1.wip_due_date is not null    
    );

Let us know your findings.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[martinm]

Thanks, that makes sense.

However.... I had already tried a cast on the NULL to a datetime but get an error (ORA-22907 invalid cast to a type that is not a nested table or VARRAY).

The BIG problem is the query actually returns 6 columns via 6 unions, but only one is not null per query, so I can't put the 'defining' query firs as there isn't one!

These values are then put into a nested NVL statement to produc a single value. It is very nasty, but I'm using a reporting tool that doesn't allow anything but select statements and the logic is very complex.

I guess I could try a Decode instead of the NVL and return an 'unused' date instead of NULL.

I'll let you know!

Cheer.

 

[hoinz]

Hello,

this syntax seems to work with Oracle 9i.
But there isn't any Oracle 8 around here any more for testing, sorry.

What do you mean by a cast on the NULL to a datetime?
Did you try this:
... to_date(null) as c2 ...

If this won't help, another, a bit nasty idea comes to my mind:
You could add a first dummy line, just for enforcing data type.
Then leave out this line again afterwards.
Something like this:

select to_date('22220101','YYYYMMDD') as c1, to_date('22220101','YYYYMMDD') as c2 from dual
UNION
select t1.wip_due_date as c1, null as c2 from ...
UNION
select ...
UNION
....
MINUS
select to_date('22220101','YYYYMMDD'), to_date('22220101','YYYYMMDD') from dual

hope this helps

 

[martinm]

Well, got it working.

I was using cast(null as datetime), this didn't work.

TO_DATE(NULL) does which makes life easier.

the full code just to show what I was actually trying to achieve.

Thanks all :

 select  orderno,
         lineno,
         releaseno,
         NVL(NVL(NVL(NVL(NVL(c1,c2),c3),c4),c5),c6)
 from(         
   select   t1.orderno,
            t1.lineno,
            t1.releaseno,  
            t1.wip_due_date as c1,
            to_date(null) as c2,
            to_date(null) as c3,
            to_date(null) as c4,
            to_date(null) as c5,
            to_date(null) as c6         
        from visib.mm_mrp_tmp t1
     where t1.due_date is null
       and t1.wip_due_date is not null
    
     union all 
    
   select   t1.orderno,
            t1.lineno,
            t1.releaseno,  
            to_date(null)  as c1,
            t1.prom as c2,
            to_date(null) as c3,
            to_date(null) as c4,
            to_date(null) as c5,
            to_date(null) as c6 
   from     visib.mm_mrp_tmp t1
     where t1.due_date is null
       and t1.prom is not null
       and to_char(t1.prom,'dd/mm/yyyy') <> '00/00/0000' 
       and t1.prom < '01 jan 3999'
         
     union all 
    
   select   t1.orderno,
            t1.lineno,
            t1.releaseno,  
            to_date(null) as c1,
            to_date(null) as c2,
            t1.po_date_req as c3,
            to_date(null) as c4,
            to_date(null) as c5,
            to_date(null) as c6
   from     visib.mm_mrp_tmp t1
     where t1.due_date is null
       and t1.po_date_req is not null
    
     union all 
    
   select   t1.orderno,
            t1.lineno,
            t1.releaseno,  
            to_date(null) as c1,
            to_date(null) as c2,
            to_date(null) as c3,
            t1.pr_date_req as c4,
            to_date(null) as c5,
            to_date(null) as c6         
   from     visib.mm_mrp_tmp t1
     where t1.due_date is null
       and t1.pr_date_req is not null
   
     union all 
    
   select   t1.orderno,
            t1.lineno,
            t1.releaseno,  
            to_date(null) as c1,
            to_date(null) as c2,
            to_date(null) as c3,
            to_date(null) as c4,         
            t1.mrp_date_req + t1.resc as c5,
            to_date(null) as c6
   from     visib.mm_mrp_tmp t1
     where t1.due_date is null
       and t1.plan_type = 'P'
    
     union all 
    
   select   t1.orderno,
            t1.lineno,
            t1.releaseno,  
            to_date(null) as c1,
            to_date(null) as c2,
            to_date(null) as c3,
            to_date(null) as c4,
            to_date(null) as c5,         
            t1.mrp_date_req as c6
   from     visib.mm_mrp_tmp t1
     where t1.due_date is null
       and t1.plan_type = 'S'
       and t1.peg_type = 'W');