时间戳

首页 > 解决方案 > Oracle SQL 检索表中所有列的空值、空值、非空值、不同值

sql - Oracle SQL 检索表中所有列的空值、空值、非空值、不同值

问题描述

我从上一篇文章中选择了这段代码，并尝试调整 sql 以计算列的空白记录数.. 但是我遇到了下面的错误.. 我无法解决错误.. 请你帮忙

select owner, table_name, column_name,
to_number(xmlquery('/ROWSET/ROW/C/text()'
passing xmltype(dbms_xmlgen.getxml(
'select count(distinct "' || column_name || '") as c '
|| 'from "' || owner || '"."' || table_name || '"'))
returning content)) as distinct_count,
to_number(xmlquery('/ROWSET/ROW/C/text()'
passing xmltype(dbms_xmlgen.getxml(
'select count(case when (' || column_name || ' = ' ' ) then 0 end) as c '
|| 'from "' || owner || '"."' || table_name || '"'))
returning content)) as null_count,
to_number(xmlquery('/ROWSET/ROW/C/text()'
passing xmltype(dbms_xmlgen.getxml(
'select count(case when "' || column_name || '" is not null then 1 end) as c '
|| 'from "' || owner || '"."' || table_name || '"'))
returning content)) as notnull_count
from all_tab_columns
where owner = 'JAMES'
and table_name = 'TEST'
and data_type in ('NUMBER', 'DATE', 'TIMESTAMP', 'CHAR', 'VARCHAR2',
'NCHAR', 'NVARCHAR2');

ORA-00907：缺少右括号 00907. 00000 -“缺少右括号” *原因：
*操作：第 9 行错误，第 58 列

标签： sqloracleora-00907

解决方案

似乎不需要一个空长度的字符串（''），并在带引号的字符串中为每个字符串提供一个多引号。因此，您需要将Line: 9 Column: 58 处的字符串转换为代码中的'' '''' 

select owner,
       table_name,
       column_name,
       to_number(xmlquery('/ROWSET/ROW/C/text()' passing
                          xmltype(dbms_xmlgen.getxml('select count(distinct "' ||
                                                     column_name ||
                                                     '") as c ' || 'from "' ||
                                                     owner || '"."' ||
                                                     table_name || '"'))
                          returning content)) as distinct_count,
       to_number(xmlquery('/ROWSET/ROW/C/text()' passing
                          xmltype(dbms_xmlgen.getxml('select count(case when (' ||
                                                     column_name || ' = '''' ) then 0 end) 
                                                                             as c '||
                                                                      --^^^^ these quotes
                                                     'from "' || owner ||
                                                     '"."' || table_name || '"'))
                          returning content)) as null_count,
       to_number(xmlquery('/ROWSET/ROW/C/text()' passing
                          xmltype(dbms_xmlgen.getxml('select count(case when "' ||
                                                     column_name ||
                                                     '" is not null then 1 end) as c ' ||
                                                     'from "' || owner ||
                                                     '"."' || table_name || '"'))
                          returning content)) as notnull_count
  from all_tab_columns
 where owner = 'JAMES'
   and table_name = 'TEST'
   and data_type in ('NUMBER','DATE','TIMESTAMP','CHAR','VARCHAR2','NCHAR','NVARCHAR2');

推荐阅读