Select Query in SQL + All Column Values

Question

I have a table Table1 with columns id1, id2, id3, all columns are NULL

I can enter a null value or a value for all columns in the rows.

My question is: I need to select rows whose all column values ​​should not be zero.

thanks

---

There are about 300 columns in the table. I cannot execute the is null property for all columns in where .

Answer 1

You need to do this:

 SELECT * FROM yourtable WHERE column1 IS NOT NULL AND column2 IS NOT NULL AND column3 IS NOT NULL AND .... 

Answer 2

The answer to using a "function" to check for null values ​​is correct. The syntax is database dependent. If ISNULL () does not exist in your database, try:

SELECT * FROM Table1 WHERE id1 NOT NULL, and id2 is not NULL, and id3 is not NULL

And there is no way to shorten this, even if your table has 300 fields.

Answer 3

I do not understand why this question is causing negative reviews. This question can be extended to people who have inherited a large table from a non-programmer in the community (I know from previous experience), as well as if the table is unknown, to lower this, because its columns “300” are meaningless IMO.

Answer 4

It’s best to either rethink the design of your tables, breaking them down if necessary.

otherwise, it’s best to do it programmatically — grab the table metadata, pass through the columns, and dry-create SQL from there. Most coding languages ​​have access to table metadata, otherwise this requires a second SQL.

But it’s best to think about how I should make a table.

Answer 5

Do you want to select rows where none of the columns is zero?

  SELECT id1, id2, id3 
 FROM Table1 
 WHERE id1 IS NOT NULL AND id2 IS NOT NULL AND id3 IS NOT NULL

Answer 6

Sorry, I could be a little thicker here. Are you trying to return rows that received SOMETHING in one of the columns (except for the id column)?

You cannot do

  create vw_View_Fields1to5 as 
   select id from employees 
   where name is not null or description is not null or field3 is not null 
   or field4 is not null or field5 is not null;
 create vw_View_Fields6to10 as 
   select id from employees 
   where field6 is not null or field7 is not null or field8 is not null 
   or field 9 is not null or field10 is not null;
 (etc)

 select id from vw_View_Fields1to5
 union 
 select id from vw_View_Fields6to10 .... (etc)

You need to take DISTINCT or something to cut lines that fall into more than one view, of course.

If you want rows that have NOTHING in any column other than id to switch ' or blah is not null ' to be , and blah is null '(etc.).

Does that make sense ... or am I missing something ?:-)

EDIT: Actually, I think the UNION process will result in different lines appearing anyway (unlike UNION ALL), but I could be wrong - I haven't really tried this ... (yet!)

Answer 7

you can try the CLR stored procedure (if you are using SQL Server) or move this logic to another level of your application using C # or any other language you use.

Another option is to create the query dynamically, combine the WHERE clause and EXECute clause of your dynamically generated query.

Answer 8

Are you just reading the data or want to try updating the rows?

I'm just wondering if there is something you can do by creating half a dozen views, each of which is based on the fact that 50 columns are not NULL, and then link them to some EXISTS or UNION proposition?

Can you tell us a little more about what you want to do with your result set?

Answer 9

For the first time, regardless of what George is, an engram or robsoft. However, for later material, you can, if possible, modify the table and add another column called CSELECTFLAG, and initially update this to Y for all columns that have values, and N for others. Every time there is an insert, it needs to be updated. This will help make your subsequent requests faster and easier.