CASE sql查询以选择加密

Question

我有以下查询

select distinct i.host_name, d.name, 
case 
when ts.encrypted = 'YES'
then 'ENCRYPTED' 
else 'NO'
end as ENCRYPTED 
from dba_tablespaces ts, v$encrypted_tablespaces et, v$tablespace t, v$instance I, v$database d
where t.ts#=et.ts# (+) 
and ts.tablespace_name = t.name

如果数据库上的所有表空间都是加密的，我希望得到这样的结果

ENCRYPTED

host_name	名称
host1000数据库	加密

使用带有条件SUM聚合函数的简单分组查询

select   i.host_name, d.name, 
case 
when sum( case when ts.encrypted = 'NO'
then 1 end)  > 0 then 'NO' else 'ENCRYPTED' end as encrypted
from dba_tablespaces ts, v$encrypted_tablespaces et, v$tablespace t, v$instance I, v$database d
where t.ts#=et.ts# (+) 
and ts.tablespace_name = t.name
group by i.host_name, d.name