Solved

ORA-01790: expression must have same datatype as corresponding expression from a Union query with NULL

Posted on 2004-04-16
5
6,246 Views
Last Modified: 2012-05-04
I have a query that uses a union as its FROM clause.  The problem I'm running into is with the returned values for some of the columns.  Here is an example of what I'm doing:

select avg(STCorrRate), avg(LTCorrRate)
from (SELECT
CASE WHEN isd.STCorrRate<0 THEN 0 ELSE isd.STCorrRate END STCorrRate,
CASE WHEN isd.LTCorrRate<0 THEN 0 ELSE isd.LTCorrRate END LTCorrRate
FROM inspectiondata isd
UNION SELECT
null STCorrRate, null LTCorrRate
FROM inspectiondata isd
) tmpgroup

The error message is "ORA-01790: expression must have same datatype as corresponding expression".  My assumption is that the NULL's and the non-NULL values are the problem.  Is the another way to handle this?
0
Comment
Question by:trenthaynes
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 11

Accepted Solution

by:
vc01778 earned 125 total points
ID: 10845660
In 8i,  you have to convert nulls to the matching data types,  e.g. asssuming x is a number:

select x from t1
union
select to_number(null) from t2

In 9i,  you do not need to do this any longer.

VC
0
 
LVL 9

Expert Comment

by:pratikroy
ID: 10845686
I tried the following :

SQL> select * from inspectiondata;

STCORRRATE LTCORRRATE
---------- ----------
        10         -5
        10         10
         8          4
        -3         10
                   10
         4
       -10
                   -4

8 rows selected.

SQL> select avg(STCorrRate), avg(LTCorrRate)
  2  from (SELECT
  3  CASE WHEN isd.STCorrRate<0 THEN 0 ELSE isd.STCorrRate END STCorrRate,
  4  CASE WHEN isd.LTCorrRate<0 THEN 0 ELSE isd.LTCorrRate END LTCorrRate
  5  FROM inspectiondata isd
  6  UNION SELECT
  7  null STCorrRate, null LTCorrRate
  8  FROM inspectiondata isd
  9  ) tmpgroup
 10  /

AVG(STCORRRATE) AVG(LTCORRRATE)
--------------- ---------------
     5.33333333      5.66666667

If you notice, I have null values as well in my table, but did'nt get the same problem while running exactly same query (as you have provided).

I suspect that there is some other problem. Do you get this error, while executing this statement on the command prompt ? Or is this a part of some PLSQL block (like procedure/package/function etc) ? Which version of Oracle are you using ?
0
 
LVL 11

Expert Comment

by:vc01778
ID: 10845736
@pratikroy

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production

SQL> select 1 from dual
  2  union select null from dual;

         1
----------
         1

==================

Connected to:
Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production
With the Partitioning option
JServer Release 8.1.7.4.0 - Production

SQL> select 1 from dual
  2  union
  3  select null from dual;
select 1 from dual
       *
ERROR at line 1:
ORA-01790: expression must have same datatype as corresponding expression


VC
0
 
LVL 1

Author Comment

by:trenthaynes
ID: 10845987
It's as VC indicated.  I'm on 8.1.7.4.  

I tried this:

select to_char(1) from dual
union
select null from dual

as well as this:

select 1 from dual
union
select to_number(null) from dual

Both work.  Using the union as the from clause (as above), both work as well.  It's an 8i issue.  Thanks for the help.
0
 
LVL 9

Expert Comment

by:pratikroy
ID: 10846695
@VC Thanks again :) I will keep this in mind :)
0

Featured Post

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question