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

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?
LVL 1
trenthaynesAsked:
Who is Participating?
 
vc01778Commented:
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
 
pratikroyCommented:
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
 
vc01778Commented:
@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
 
trenthaynesAuthor Commented:
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
 
pratikroyCommented:
@VC Thanks again :) I will keep this in mind :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.