Garrettfromhp Garrettfromhp - 1 year ago 82
SQL Question

SQL- Selecting NULL columns only

I need to write a query which will look at a table, and select from it any/all columns(s) that have ALL NULL rows.

I would prefer to not have to say "SELECT (each column name)", but would rather say "SELECT (from whole table, those columns) WHERE (rows are all NULL).

I know about CASE and WHEN statements, but my understanding is that columns must be specifically referenced in the select statement. I'll be using this query on many different tables, with many different dimensions and column names, so I would like the query to be versatile.

I'm using SQL in Toad with a Vertica db.

This is what my tables look similar to:


This table contains the columns i need the query. The columns i need to query are such as "owner_id", NOT TYPE_NAME, NULLABLE, etc. Each row in THIS table, is actually a column with such and such many values, and it is these values I need to run my query against.

'Simply' put: If a column has ALL NULL rows, I need to identify and/or select it.

Any Suggestions? %Solved^^^%

I found this example of a way to loop through columns, but despite my research, I'm still having trouble making sense of it all.

Such as: how is col_names difference from column_name? Same for the text in the FROM statement and WHERE. Should those not both the table name? Then what does the found_rows() signify?

SELECT column_name
WHERE table_name = 'tbl_name'
ORDER BY ordinal_position;

select FOUND_ROWS() into num_rows;

SET i = 1;
the_loop: LOOP

IF i > num_rows THEN
CLOSE col_names;
LEAVE the_loop;

FETCH col_names
INTO col_name;

//do stuff

SET i = i + 1;
END LOOP the_loop;

Answer Source

As I understand you want to Identify which columns have NULL values on all rows and dont want to type all columns, right?

If thats so, this is how I would do:

1) Build a function that takes the as an input parameter and does: --FYI: this is a pseudo code. I dont have mysql here to get the correct syntax

select sum(
            case when CAST(<column_name> as varchar(5200)) is not null then 1 else 0 end --5200 seems to be your longer column
--the idea is to cast the column as anythig; if it is null, it gets a 0.
--If the sum across the rows is 0, it means that all the rows are null

2) Loop through the column names and call the function