Dynamic SQL Query to ignore null values based on null value in cell

Question

I have a dynamic SQL query with different column names and tables at runtime.

I am looking to get the SQL query to ignore reading data based on if a row contains Null value in any cell.

SELECT rsv_intension_rsvt 
FROM resolve_table_kb 
where rsv_intension_rsvt is not null;

I am aware of using IS NOT NULL.

But the problem is that I wouldn't know the query syntax (i.e. columns name so that IS NOT NULL can be used).

Is there a dynamic solution that can be used for the SQL query to read/collect rows only when all cells in a selected row are NOT NULL.

Thanks

score 0 · Accepted Answer · answered May 07 '14 at 15:08

No, there's no select where * is not null-type query. You have to test every field individually:

SELECT ...
FROM ...
WHERE field1 is not null AND field2 is not null AND .... AND fieldN is not null

You could try a COALESCE() operation, perhaps, but that's still ugly:

WHERE COALESCE(field1, field2, ..., fieldN, 'allarenull') <> 'allarenull'

but then you STILL have to list all of the fields in the table.

score 0 · Answer 2 · edited May 23 '17 at 12:05

0

I believe you will need to use a stored procedure or multiple joins(which might not be the healthiest solution) to solve this as Marc B indicated. You can also check the following question that addresses the same issue you are asking about.

edited May 23 '17 at 12:05

Community

1
1

answered May 07 '14 at 15:25

stripthesoul

362
1
8

score 0 · Answer 3 · edited Jun 20 '20 at 09:12

I am not sure what you mean by dynamic, but if you mean that you actually build your queries at runtime, then you can get all available column names of all columns (that are nullable or not) for a given table in a given database, then you can simply put them together for your desired query.

In MySQL you can do that like this:

SELECT
        COLUMN_NAME
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE
        table_name = '<TABLE_NAME>'
        AND IS_NULLABLE = 'YES'
        AND TABLE_SCHEMA='<DB_NAME>';

In SQL-Server you can do it like this

(provided you already selected the correct database) (tables reference, columns reference)

SELECT
        Cols.name
    FROM sys.all_columns AS Cols
    INNER JOIN sys.tables AS Tabs ON Cols.object_id = Tabs.object_id
    WHERE
        Tabs.name = '<TABLE_NAME>'
        AND Cols.is_nullable = 1;

And in Oracle you can do it like this:

(having already selected the appropriate database)

SELECT
    COLUMN_NAME
  FROM ALL_TAB_COLUMNS
  WHERE
    TABLE_NAME = '<TABLE_NAME>'
    AND NULLABLE = 'Y';

Not sure if this is a general Oracle-thing (as I am working mostly with SQL-Server), but when I tried this out with a fiddle, the table name I had to specify was always in uppercase letters.

Dynamic SQL Query to ignore null values based on null value in cell

3 Answers3

In MySQL you can do that like this:

In SQL-Server you can do it like this

And in Oracle you can do it like this:

Linked