Dynamic SQL query to ignore null values ​​based on a null value in a cell

Question

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

I am looking to force an SQL query to ignore read data based on whether the row contains Null in any cell.

SELECT rsv_intension_rsvt FROM resolve_table_kb where rsv_intension_rsvt is not null; 

I know the use of IS NOT NULL .

But the problem is that I don’t know the syntax of the query (i.e. the column name so that IS NOT NULL can be used).

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

thanks

Answer 1

No, there is no query like select where * is not null . You must check each field individually:

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

Perhaps you can try the COALESCE() operation, but this is still ugly:

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

but then you must STILL list all the fields in the table.

Answer 2

I believe that you will need to use a stored procedure or several connections (this may not be the healthiest solution) to solve this problem like Marc B. You can also check out the following question , which address the same issue you are asking for.

Answer 3

I'm not sure what you mean by dynamic , but if you mean that you are actually creating your queries at runtime, then you can get all available column names of all columns (which can be nullified or not) for this table in this database, you can simply compose them for the desired query.

In MySQL you can do it 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

(if you have already selected the correct database) ( link table , columns )

 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 :

(already selecting 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 (since I work mainly with SQL server), but when I tried this with a violin, the name of the table that I had to indicate was always in capital letters.