To find number of null values in each columns of a table

Database

To find number of null values in each columns of a table

Hi ,

I want to count all the null values of all the columns of a table.

SEL ColumnName  from  DBC.Columns where tablename='table_name' and DatabaseName='database_name';

The above will provide all the column names.

SEL COUNT(*)-count(col_name) from table_name;

The above will provide number of null values for one single column.

But I need a query which will provide for all the column of a table like below format.

ColumnName           null_values

==========      ===========

col1                             23                                                 

col2                              2                                                  

col3                             0                                                   

col4                            34                                                  

Kindly help me if it is possible.

Tags (1)
7 REPLIES
Senior Apprentice

Re: To find number of null values in each columns of a table

SEL 'col1', COUNT(*)-count(col1) from table_name
union all
SEL 'col2', COUNT(*)-count(col2) from table_name
union all
SEL 'col3', COUNT(*)-count(col3) from table_name
...
Enthusiast

Re: To find number of null values in each columns of a table

You can try with

sel 'col1', sum(case when col1 is null then 1 else 0 end)  from tablename;

or you can omit the else part too.

Re: To find number of null values in each columns of a table

Hi Dnoeth,

Thanks for response.

Yes I got your point.But table which have 100 columns I have to write 100 times.

I was wondering whether I can find a way so that using dbc.columns view I can get in one single query without writing so many UNION all.

Supporter

Re: To find number of null values in each columns of a table

maybe this gives a hint.

https://www.wolframcloud.com/objects/243ed5e8-84b7-4c78-b0d0-9ce967325025

add your databasename and tablename - hit submit

this will generate an SQL which generates the SQL you want.

So copy the result of the SQL and execute it...

Supporter

Re: To find number of null values in each columns of a table

you can make also easily a macro out of this...

Re: To find number of null values in each columns of a table

Thanks ulrich,

It is working for me.Meantime I have tried a TD procedure and that was a complex method but was working fine.

Here is the code to create that procedure::

 =====================================================================================

--create the below table in your data base

CREATE MULTISET TABLE database_name.NULL_COUNT_TABLE ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT,
DEFAULT MERGEBLOCKRATIO
(
Column_Name VARCHAR(100) TITLE 'Column Name',
Total_Null_Count INTEGER TITLE 'Total Null values'
)
PRIMARY INDEX XNUPI_NULL_COUNT_TABLE ( Column_Name );

=====================================================================================

--compile the below procedure(use your data base name in place of database_name and user name in place of user_name)
--call this procedure
--1st para meter is table name and 2nd one is data base name
--call user_name.COUNT_NULL_VALUE('table_name','database_name');

=====================================================================================
REPLACE PROCEDURE user_name.COUNT_NULL_VALUE
(
IN in_table_Name VARCHAR(50) ,
IN in_Database_Name VARCHAR(50)

)
DYNAMIC RESULT SETS 1
BEGIN

------------Common variables used in Procedure-----------------
DECLARE SqlStr_COUNT_NULL_VALUE VARCHAR(31825) DEFAULT ' ';
DECLARE lv_column_name VARCHAR(100);
DECLARE lv_total_row INTEGER;
DECLARE lv_null_count INTEGER;

------------Common variables used in Procedure-----------------

DECLARE Rst_set_SqlStr_COUNT_NULL_VALU CURSOR WITH RETURN ONLY FOR STMT1 ;

DELETE FROM database_name.NULL_COUNT_TABLE ;
INSERT INTO database_name.NULL_COUNT_TABLE (Column_Name) SELECT ColumnName from DBC.Columns where tablename=:in_table_Name and DatabaseName=:in_Database_Name;
SELECT COUNT(*) INTO lv_total_row FROM database_name.NULL_COUNT_TABLE ;

WHILE(lv_total_row>0)
DO
SEL CLMN_NAME INTO lv_column_name FROM( SEL COLUMN_NAME AS CLMN_NAME,rank() over (order by COLUMN_NAME desc) AS RNK from database_name.NULL_COUNT_TABLE)K WHERE K.RNK=lv_total_row;
DELETE FROM database_name.NULL_COUNT_TABLE WHERE Column_Name=:lv_column_name;

SET SqlStr_COUNT_NULL_VALUE ='INSERT INTO database_name.NULL_COUNT_TABLE (Total_Null_Count,COLUMN_NAME) SELECT COUNT(*)-COUNT('||lv_column_name||') , '''||lv_column_name||''' FROM '||in_Database_Name||'.'||in_table_Name||' GROUP BY 2;';
CALL dbc.SysExecSQL (:SqlStr_COUNT_NULL_VALUE);

SET lv_total_row=lv_total_row-1;
END WHILE;

SET SqlStr_COUNT_NULL_VALUE ='select column_name,Total_Null_Count from database_name.NULL_COUNT_TABLE WHERE Total_Null_Count <>0 ORDER BY 1 ;';

PREPARE STMT1 FROM SqlStr_COUNT_NULL_VALUE;
OPEN Rst_set_SqlStr_COUNT_NULL_VALU;
DEALLOCATE PREPARE STMT1;

END;

=====================================================================================

sample results:::::


call ACSALES_USER.COUNT_NULL_VALUE('GC_VARIABLES','ACSALES_APP');

*** Procedure has been executed.
*** Warning: 3212 The stored procedure returned one or more result sets.
*** Total elapsed time was 4 seconds.

*** ResultSet# 1 : 6 rows returned by "ACSALES_USER.COUNT_NULL_VALUE".

Column Name Total Null values
---------------------------------------------------------------------------------------------------- -----------------
Var1_Desc 25
Var1_Val 25
Var2_Desc 41
Var2_Val 49
Var3_Desc 65
Var3_Val 45

BTEQ -- Enter your SQL request or BTEQ command:
Enthusiast

Re: To find number of null values in each columns of a table

Hi Jayant,

I want search a specific data value on column in a database. That data value may exist any column of the table. (consider Database is having 100 tables and each table having 100 columns. Requirement is to search the string of the columns of that database). Ex: String have to search is 'INDIA'

Can you please let me know how to search the specific data value on a column of the given database or if you have any SP to do the same?

Thanks,