link管理

链接快照平台

输入网页链接，自动生成快照
标签化管理网页链接

相关文章推荐

失恋的牙膏 · 数据库：查询生成器 | Laravel中文文档· 1 周前 ·

酒量大的煎鸡蛋 · SQL重复记录查询的几种方法(转) - ...· 4 天前 ·

憨厚的硬盘 · Optimizer Hints | ...· 4 天前 ·

伤情的遥控器 · mybatis-plus连接SQL ...· 4 天前 ·

道上混的紫菜汤 · 查询服务中的SQL语法 | Adobe ...· 4 天前 ·

健身的钥匙 · 《如何与大两岁的继子们相处》by奶香蟑螂苏_ ...· 3 月前 ·

豁达的地瓜 · javascript正则表达式处理中文和中文 ...· 6 月前 ·

灰常酷的猴子 · 使用模板向 Amazon ...· 6 月前 ·

大方的人字拖 · 走近中国维和女军人 - 中国军网· 8 月前 ·

有胆有识的炒饭 · 桂花<i>OfMYB</i>1<i>R</i ...· 10 月前 ·

){outline:none;box-shadow:none;}select::-ms-expand{;}:root,:host{--chakra-vh:100vh;}@supports (height: -webkit-fill-available){:root,:host{--chakra-vh:-webkit-fill-available;}}@supports (height: -moz-fill-available){:root,:host{--chakra-vh:-moz-fill-available;}}@supports (height: 100dvh){:root,:host{--chakra-vh:100dvh;}}

Create Account

trenthaynes

asked on

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?

ASKER CERTIFIED SOLUTION

vc01778

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

pratikroy

I tried the following :
SQL> select * from inspectiondata;
STCORRRATE LTCORRRATE
---------- ----------
10 -5
10 10
8 4
-3 10
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 ?

vc01778

@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;
----------
==================
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

trenthaynes

ASKER

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.