Solved

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

Posted on 2004-04-16
5
6,149 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
  • 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many‚Ķ
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

685 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