CODEWITHSUNDEEP
sqlsql-server
sds
sds

Reputation: 23

How do I SELECT dummy column dynamically if one or few columns do not exist in the table?

The SQL query I am trying is like this:- 

select
  (select count(*) from ABC where AAA is not null) as AAA,
  (select count(*) from ABC where BBB is not null) as BBB,
  (select count(*) from ABC where CCC is not null) as CCC,
  (select count(*) from ABC where DDD is not null) as DDD;

Now the problem is that the table ABC exists in several schemas and in each schema it has different structure. Say in schema S1, the table abc has only two columns - AAA and CCC. In schema S2 it has all the four columns - AAA, BBB, CCC, DDD. So, I have taken the super structure of the table and created my query as above. Now what I want is that if the table in certain schema has only two columns than also it should display the 'NOT NULL' count for all four columns, showing NA for the columns not present.
How should I achieve this?

NOTE: There are 50+ such tables with non-uniform structure across different schemas. So, I have to edit the SQL again and again for around 250 schemas for several tables.

Upvotes: 0

Views: 1613

Answers (2)

Erich Kitzmueller
Erich Kitzmueller

Reputation: 36987

Note: This answer is based on JamieA's answer, but it required several changes to make it work on Oracle and I didn't want to totally edit his answer

This one works on Oracle.

SELECT 
  case when exists(select 1 from user_tab_columns
                 where table_name='ABC' and column_name='AAA') 
       then (select to_char(count(*)) FROM ABC WHERE AAA IS NOT NULL )
       else 'NA' end  AS AAA,
  case when exists(select 1 from user_tab_columns
                 where table_name='ABC' and column_name='BBB')
       then (select to_char(count(*)) FROM ABC WHERE BBB IS NOT NULL )
       else 'NA' end  AS BBB,
  case when exists(select 1 from user_tab_columns
                 where table_name='ABC' and column_name='CCC')
       then (select to_char(count(*)) FROM ABC WHERE CCC IS NOT NULL )
       else 'NA' end  AS CCC,
  case when exists(select 1 from user_tab_columns
                 where table_name='ABC' and column_name='DDD')
       then (select to_char(count(*)) FROM ABC WHERE DDD IS NOT NULL )
       else 'NA' end  AS DDD
FROM
  ( SELECT 1 AS AAA, 1 as BBB, 1 as CCC, 1 as DDD from dual
  );

Upvotes: 1

JamieA
JamieA

Reputation: 2013

try the below. I am assuming that varchar(10) is big enough to hold your row count. 

 SELECT 
  ( SELECT CASE WHEN NOT exists (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'ABC' AND COLUMN_NAME = 'DDD') then 'NA' else CAST (COUNT(*) as varchar(10)) end 
    FROM abc AS t1
    WHERE aaa  IS NOT NULL
  ) AS AAA,
    ( SELECT CASE WHEN NOT exists (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'ABC' AND COLUMN_NAME = 'DDD') then 'NA' else CAST (COUNT(*) as varchar(10)) end
    FROM abc AS t1
    WHERE bbb  IS NOT NULL
  ) AS BBB,
    ( SELECT CASE WHEN NOT exists (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'ABC' AND COLUMN_NAME = 'DDD') then 'NA' else CAST (COUNT(*) as varchar(10)) end
    FROM abc AS t1
    WHERE CCC  IS NOT NULL
  ) AS CCC,
    ( SELECT CASE WHEN NOT exists (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'ABC' AND COLUMN_NAME = 'DDD') then 'NA' else CAST (COUNT(*) as varchar(10)) end
    FROM abc AS t1
    WHERE DDD  IS NOT NULL
  ) AS DDD
FROM
  ( SELECT 1 AS AAA,1 as BBB, 1 as CCC, 1 as DDD
  ) AS dummy ;

Upvotes: 2

Related Questions