JavaScript is disabled. For a better experience, please enable JavaScript in your browser before proceeding.
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an
alternative browser
.
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 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'
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:
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.
![[santa]](/data/assets/smilies/santa.gif "[santa] [santa]")
Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services:
www.dasages.com
]
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.
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
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 :
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');
This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
By continuing to use this site, you are consenting to our use of cookies.
Accept
Learn more…
