select - 为什么向我的查询添加条件会导致 ORA-00934 错误消息?
问题描述
我有以下查询:
select a.tablespace_name "Tab_name",
ROUND(SUM(a.bytes)/1024/1024/1024, 2) "Sum_files_GB",
ROUND(SUM(decode(b.maxextend, null, A.BYTES/1024/1024/1024, b.maxextend*C.BLOCK_SIZE/1024/1024/1024)), 2) "Max_size_GB",
ROUND((SUM(decode(b.maxextend, null, A.BYTES/1024/1024/1024, b.maxextend*C.BLOCK_SIZE/1024/1024/1024)) - (SUM(a.bytes)/1024/1024/1024 - round(c."Free"/1024/1024/1024))), 2) "Free_GB",
round(100*(SUM(a.bytes)/1024/1024/1024 - round(c."Free"/1024/1024/1024))/(SUM(decode(b.maxextend, null, A.BYTES/1024/1024/1024, b.maxextend*C.BLOCK_SIZE/1024/1024/1024)))) "%_used"
from dba_data_files a, sys.filext$ b, (SELECT d.tablespace_name , sum(nvl(c.bytes,0)) "Free",D.BLOCK_SIZE FROM dba_tablespaces d,DBA_FREE_SPACE c where d.tablespace_name = c.tablespace_name(+) group by d.tablespace_name, D.BLOCK_SIZE) c
where a.file_id = b.file#(+)
and a.tablespace_name = c.tablespace_name
GROUP by a.tablespace_name, c."Free"
order by round(100*(SUM(a.bytes)/1024/1024/1024 - round(c."Free"/1024/1024/1024))/(SUM(decode(b.maxextend, null, A.BYTES/1024/1024/1024, b.maxextend*C.BLOCK_SIZE/1024/1024/1024)))) desc;
我想添加以下条件:
and round(100*(SUM(a.bytes)/1024/1024/1024 - round(c."Free"/1024/1024/1024))/(SUM(decode(b.maxextend, null, A.BYTES/1024/1024/1024, b.maxextend*C.BLOCK_SIZE/1024/1024/1024)))) "%_used" < 30
and ROUND((SUM(decode(b.maxextend, null, A.BYTES/1024/1024/1024, b.maxextend*C.BLOCK_SIZE/1024/1024/1024)) - (SUM(a.bytes)/1024/1024/1024 - round(c."Free"/1024/1024/1024))), 2) "Free_GB" < 99
结果,查询如下所示:
select a.tablespace_name "Tab_name",
ROUND(SUM(a.bytes)/1024/1024/1024, 2) "Sum_files_GB",
ROUND(SUM(decode(b.maxextend, null, A.BYTES/1024/1024/1024, b.maxextend*C.BLOCK_SIZE/1024/1024/1024)), 2) "Max_size_GB",
ROUND((SUM(decode(b.maxextend, null, A.BYTES/1024/1024/1024, b.maxextend*C.BLOCK_SIZE/1024/1024/1024)) - (SUM(a.bytes)/1024/1024/1024 - round(c."Free"/1024/1024/1024))), 2) "Free_GB",
round(100*(SUM(a.bytes)/1024/1024/1024 - round(c."Free"/1024/1024/1024))/(SUM(decode(b.maxextend, null, A.BYTES/1024/1024/1024, b.maxextend*C.BLOCK_SIZE/1024/1024/1024)))) "%_used"
from dba_data_files a, sys.filext$ b, (SELECT d.tablespace_name , sum(nvl(c.bytes,0)) "Free",D.BLOCK_SIZE FROM dba_tablespaces d,DBA_FREE_SPACE c where d.tablespace_name = c.tablespace_name(+) group by d.tablespace_name, D.BLOCK_SIZE) c
where a.file_id = b.file#(+)
and a.tablespace_name = c.tablespace_name
and round(100*(SUM(a.bytes)/1024/1024/1024 - round(c."Free"/1024/1024/1024))/(SUM(decode(b.maxextend, null, A.BYTES/1024/1024/1024, b.maxextend*C.BLOCK_SIZE/1024/1024/1024)))) "%_used" < 30
and ROUND((SUM(decode(b.maxextend, null, A.BYTES/1024/1024/1024, b.maxextend*C.BLOCK_SIZE/1024/1024/1024)) - (SUM(a.bytes)/1024/1024/1024 - round(c."Free"/1024/1024/1024))), 2) "Free_GB" < 99
GROUP by a.tablespace_name, c."Free"
order by round(100*(SUM(a.bytes)/1024/1024/1024 - round(c."Free"/1024/1024/1024))/(SUM(decode(b.maxextend, null, A.BYTES/1024/1024/1024, b.maxextend*C.BLOCK_SIZE/1024/1024/1024)))) desc;
但是这个查询给了我错误信息 ORA-00934。那么,如何才能在原始查询中包含上述条件呢?
解决方案
使用having
从句
select a.tablespace_name "Tab_name",
ROUND(SUM(a.bytes)/1024/1024/1024, 2) "Sum_files_GB",
ROUND(SUM(decode(b.maxextend, null, A.BYTES/1024/1024/1024, b.maxextend*C.BLOCK_SIZE/1024/1024/1024)), 2) "Max_size_GB",
ROUND((SUM(decode(b.maxextend, null, A.BYTES/1024/1024/1024, b.maxextend*C.BLOCK_SIZE/1024/1024/1024)) - (SUM(a.bytes)/1024/1024/1024 - round(c."Free"/1024/1024/1024))), 2) "Free_GB",
round(100*(SUM(a.bytes)/1024/1024/1024 - round(c."Free"/1024/1024/1024))/(SUM(decode(b.maxextend, null, A.BYTES/1024/1024/1024, b.maxextend*C.BLOCK_SIZE/1024/1024/1024)))) "%_used"
from dba_data_files a, sys.filext$ b, (SELECT d.tablespace_name , sum(nvl(c.bytes,0)) "Free",D.BLOCK_SIZE FROM dba_tablespaces d,DBA_FREE_SPACE c where d.tablespace_name = c.tablespace_name(+) group by d.tablespace_name, D.BLOCK_SIZE) c
where a.file_id = b.file#(+)
and a.tablespace_name = c.tablespace_name
GROUP by a.tablespace_name, c."Free"
having round(100*(SUM(a.bytes)/1024/1024/1024 - round(c."Free"/1024/1024/1024))/(SUM(decode(b.maxextend, null, A.BYTES/1024/1024/1024, b.maxextend*C.BLOCK_SIZE/1024/1024/1024)))) < 30
and ROUND((SUM(decode(b.maxextend, null, A.BYTES/1024/1024/1024, b.maxextend*C.BLOCK_SIZE/1024/1024/1024)) - (SUM(a.bytes)/1024/1024/1024 - round(c."Free"/1024/1024/1024))), 2) < 99
order by round(100*(SUM(a.bytes)/1024/1024/1024 - round(c."Free"/1024/1024/1024))/(SUM(decode(b.maxextend, null, A.BYTES/1024/1024/1024, b.maxextend*C.BLOCK_SIZE/1024/1024/1024)))) desc;
推荐阅读
- halide - 卤化物 JIT 与生成器的差异
- python - 如何运行 Python 脚本并将其结果传递给 MERN 应用程序中的另一个组件
- visual-studio - 如何在 MSBuild 脚本中检索 AssemblyVersionAttribute
- android - 获取 TextView 的颜色
- php - 无法与 SQL Server 2019 建立连接
- android-studio - 如何将适配器放入 viewpager?
- python - 在我尝试创建用户时使用 django 创建自定义用户后,我的密码不显示或其空白,或者您可以说它没有
- artificial-intelligence - Type of tree for Alpha-Beta Pruning
- google-maps - 如何在谷歌地图中添加来自 Firestore 的标记?
- c - 指向结构的指针作为函数的参数有什么用?