Solved

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

Posted on 2004-04-16
5
5,693 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
Comment Utility
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
Comment Utility
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
Comment Utility
@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
Comment Utility
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
Comment Utility
@VC Thanks again :) I will keep this in mind :)
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

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.
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to recover a database from a user managed backup

763 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

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now