Solved

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

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

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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Query 34 116
'G_F01' is not a procedure or is undefined 3 34
join a table with user_tab_columns in oracle 3 48
Oracle Nested table uses ? 2 34
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Via a live example, show how to take different types of Oracle backups using RMAN.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

828 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