link管理
链接快照平台
- 输入网页链接，自动生成快照
- 标签化管理网页链接
相关文章推荐
|
读研的充电器 · mysql日期只比较年月日 ...· 3 周前 · |
|
|
大力的松鼠 · Mysql比较日期和时间 - · 3 周前 · |
|
|
帅气的牛肉面 · 【SQL】COUNT()函数 ...· 2 周前 · |
|
|
微笑的汉堡包 · Sybase·调用存储过程并返回结果· 2 周前 · |
|
|
绅士的水煮肉 · Sequence 使用、原理全面解析 · ...· 2 周前 · |
|
|
悲伤的数据线 · 成为伯爵家的废物少爷免费漫画(酷漫画)_成为 ...· 3 月前 · |
|
|
独立的黑框眼镜 · 特朗普政府撤销“种族平权”政策，亚裔学生或因此受益· 4 月前 · |
|
|
欢快的创口贴 · 中国农业大学农学院 新闻动态 Nature ...· 4 月前 · |
|
|
正直的洋葱 · PANTONE 19-3930TCX ...· 4 月前 · |
link管理
›
Solved: ORA-01790: expression must have same datatype as corresponding expression from a Union query
| union select |
| https://www.experts-exchange.com/questions/20956985/ORA-01790-expression-must-have-same-datatype-as-corresponding-expression-from-a-Union-query-with-NULL.html |
|
苦恼的闹钟
5 月前 |
推荐文章
|
|
大力的松鼠 · Mysql比较日期和时间 - 3 周前 |
|
|
微笑的汉堡包 · Sybase·调用存储过程并返回结果 2 周前 |
|
|
独立的黑框眼镜 · 特朗普政府撤销“种族平权”政策，亚裔学生或因此受益 4 月前 |
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
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
ASKER
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.