Knoppi Active Member Licensed User Longtime User Nov 8, 2018 #1 Is it possible to do a SQL query that returns only the lines that have a NULL value without having to specify each column? somthing like this Code: Copy SELECT * IS NULL FROM table1 and NOT this (the database has 30+ columns) Code: Copy SELECT * FROM table1 WHERE col1 IS NULL OR col2 IS NULL please only pure SQL-querys not compounded with code
Is it possible to do a SQL query that returns only the lines that have a NULL value without having to specify each column? somthing like this Code: Copy SELECT * IS NULL FROM table1 and NOT this (the database has 30+ columns) Code: Copy SELECT * FROM table1 WHERE col1 IS NULL OR col2 IS NULL please only pure SQL-querys not compounded with code
S sorex Expert Licensed User Longtime User Nov 8, 2018 #2 it depends on your structure and what the fields contain. I would use a record for each "col" field for that record id instead of putting it all into 1 record. if you use an int type and use 0 instead of NULL you can solve it like this Code: Copy select * from table1 where (col1+col2+col3)>0 which is easier to read and edit. Is this for MSSQL or SQLite? Upvote 0
it depends on your structure and what the fields contain. I would use a record for each "col" field for that record id instead of putting it all into 1 record. if you use an int type and use 0 instead of NULL you can solve it like this Code: Copy select * from table1 where (col1+col2+col3)>0 which is easier to read and edit. Is this for MSSQL or SQLite?
Knoppi Active Member Licensed User Longtime User Nov 8, 2018 #3 this does not work for me because the columns are different types (INTEGER,TEXT and REAL) and there are more than 30 columns whose names are more complex than in my example Upvote 0
this does not work for me because the columns are different types (INTEGER,TEXT and REAL) and there are more than 30 columns whose names are more complex than in my example
S sorex Expert Licensed User Longtime User Nov 8, 2018 #4 keeping it flexible without stored procedures is kind of hard to do. the only way I can think of is (1) retrieving the column names by using sql fetch code or (2) put the names in an array and build up the query statement by going through the column names. if you add or remove fields you don't have to do anything in case 1 and just add/remove a column name in the array for case 2. with the split method I mentioned above you just need a "select count(*) from attributes where parentId=xxx and attribValue is not null" if the count is 0 nothing has entered. Upvote 0
keeping it flexible without stored procedures is kind of hard to do. the only way I can think of is (1) retrieving the column names by using sql fetch code or (2) put the names in an array and build up the query statement by going through the column names. if you add or remove fields you don't have to do anything in case 1 and just add/remove a column name in the array for case 2. with the split method I mentioned above you just need a "select count(*) from attributes where parentId=xxx and attribValue is not null" if the count is 0 nothing has entered.
