SQL to retrieve record count for each column of a table with blank, not null, null and distinct count in oracle sql

[Member 14229943]

I am trying retrieve record count for each column of a table with blank, not null, null and distinct count in oracle sql with query below.. however I am encountering below.. unable to find the missing paranethesis 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: missing right parenthesis
00907. 00000 - "missing right parenthesis"
*Cause:
*Action:
Error at Line: 9 Column: 58

Please can u help

[Lost User]

Start with the first left parenthesis and find its matching right one. Then go on to the next left, and so on until you find the missing one. It should not take more than a couple of minutes. You would also probably be better using less complex queries.

[Member 14229943]

I am still unable to resolve

[Lost User]

Maybe you should rewrite this query into its separate clauses so you can see the breaks more clearly. You have a number of parts where you are concatenating text and variable fields so it may be that you have unbalanced quote characters.