January 28, 2009 at 9:00 pm
I have a production database with a significant number of columns, and I am curious if there is a way to pull results from only rows that have non-NULL values, without specifying each specific column in the where clause?
February 6, 2009 at 2:04 pm
You getting back Not Null is really not important because only aggregate functions ignore nulls because the aggregates are created for scalar value but a SELECT returns table. But you just add COUNT(*) to all aggregate operations so all rows can be counted before the aggregates are applied.
Now there is OUTER JOIN null which Not Null or Null is not relevant because it defaults to a mathematical null because the null comes from one of the tables smaller than the other and you must use ISNULL to get the correct value. You should know the WHERE clause is not aware of Nulls and in the current JOIN operations the WHERE clause is just a filter as the AND operator. You will find all these informations in good ANSI SQL books.
Kind regards,
Gift Peddie
February 19, 2009 at 5:14 pm
Question: Do you want to ignore a row unless ALL columns have values?
If so, you are probably going to have to list all the columns. The easy way to do this is using SQL Server Management Studio to script a SELECT of the table in question. That will give you the full list of column names. Then a little search and replace magic would let you create the following
WHERE columnA+columnB+columnC.... columnZZZ is not NULL
The presence of any null value in that expression will cause the whole expression to be null, and therefore ignored. However, it is most definitely going to do a table scan in the execution plan.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
February 20, 2009 at 6:55 am
Bob Hovious (2/19/2009)
Question: Do you want to ignore a row unless ALL columns have values?If so, you are probably going to have to list all the columns. The easy way to do this is using SQL Server Management Studio to script a SELECT of the table in question. That will give you the full list of column names. Then a little search and replace magic would let you create the following
WHERE columnA+columnB+columnC.... columnZZZ is not NULL
The presence of any null value in that expression will cause the whole expression to be null, and therefore ignored. However, it is most definitely going to do a table scan in the execution plan.
You could create a script that generates this kind of code for each table using the INFORMATION_SCHEMA views, but it would probably take as long as doing it manually. It would be an interesting exercise.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
February 20, 2009 at 7:21 am
I'm sure that is how SQL Mgmt Studio scripts SELECTs, UPDATEs, and INSERTs.
This isn't the first question that has asked if there isn't a way to avoid listing a lot of column names. But using SQL Mgmt Studio to script a SELECT, followed by a little cut-and-paste search-and-replace is pretty quick and straightforward.
I could knock out a script/proc/function that would return a string based on the schema, but what other functionality could that script provide besides just putting '+' between the column names, instead of a comma?
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
February 20, 2009 at 7:51 am
Bob,
I don't disagree. I was thinking that you would use the INFORMATION_SCHEMA.COLUMNS table to only add columns to the WHERE clause that allow nulls. The only way something like that would be productive would be if you had to create queries like this for every table in your database that has nullable columns
If I really thought it would be useful I probably would have written it already.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply