Clever Geek Handbook

How to get rows whose column values ​​are not null

I want to get the rows of a table so that no column value is zero. No hardcoding of column values. I have hundreds of column names, therefore.

enter image description here

The output should only be line 2, since this entire line has values ​​for all columns. I don't want to point out that all column names are non-zero. This should be done programmatically. Even if I add a new column, it should work without changing the query. This is my vision.

-2


source to share


5 answers


I found something, but that means using CURSOR

DECLARE @ColumnName VARCHAR(200)
DECLARE @ColumnCount INT
DECLARE @sql VARCHAR(400)

CREATE TABLE #tempTable (Id INT)

DECLARE GetNonNullRows CURSOR 
FOR 
    SELECT c.NAME, (SELECT COUNT(*) FROM sys.columns col WHERE col.object_id = c.OBJECT_ID)  FROM sys.tables AS t
    JOIN sys.columns AS c ON t.object_id = c.object_id
    WHERE t.name = 'SomeTable' AND t.type = 'U'

OPEN GetNonNullRows
FETCH NEXT FROM GetNonNullRows INTO @ColumnName, @ColumnCount
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @sql = 'SELECT st.UniqueId FROM SomeTable AS st WHERE ' + CONVERT(varchar, @ColumnName) + ' IS NOT NULL'    
    INSERT INTO #tempTable
    EXEC (@sql)

FETCH NEXT FROM GetNonNullRows INTO @ColumnName, @ColumnCount
END 

CLOSE GetNonNullRows
DEALLOCATE GetNonNullRows

SELECT * FROM SomeTable AS st1
WHERE st1.UniqueId IN (SELECT Id FROM #tempTable AS tt
GROUP BY Id
HAVING COUNT(Id) = @ColumnCount)


DROP TABLE #tempTable

Let me explain this a little.



First, I create a cursor that iterates over all the columns in one table. For each column, I am creating a sql script to search the table for non-null values ​​for the selected column. For those rows that meet the criteria, I take its unique identifier and put it in the temp table, and this task I use for all columns.

In the end, only the ID whose count is the same as the number of columns is your result set, because only rows that have the same number of phenomena, such as the number of columns in a table, can be rows with all nonzero values ​​in all columns.

+2


source


try using IS NOT NULL 

SELECT * FROM table WHERE field_name IS NOT NULL


For more information, check out the mysql manual on working with null values.

0


source


Try the following:

SELECT * FROM mytable WHERE column IS NOT NULL
0


source


try it

SELECT * 
FROM your_table_name
where coalesce(column_1, column_2, column_3, ...., column_n) is not null
0


source


SQL alone cannot express such a concept.

You have to dynamically build the SQL query according to the table definition using some procedural language. In Oracle, you can use the dictionay USER_TAB_COLUMNS view to create a list of columns.

0


source






More articles:


All Articles