Syntax to use IS NOT NULL with IN? ...perhaps another way?

[SyntaxTerror]

Maybe I'm thinking of this wrongly, but I have a situation where, if the values in 6 different columns are ALL null, I don't want to display that row.

For example, column 1 is InventoryID, then columns 2-7 will be Week1Quantity, Week1Quantity, Week3Quantity, etc.

If all 6 weeks of quantity data contain NULLs, then I don't want the row for that inventoryID to show up.

I was trying something like WHERE IN (wk1, wk2, etc.) IS NOT NULL or WHERE NOT (wk1, wk2, etc.) IS NULL

Halp!

 

[bborissov]

WHERE (wk1 + wk2 + wk3...+wkN) IS NOT NULL

When you did some whatever operation with NULL value the result will be always NULL.

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[SyntaxTerror]

Good to know! I was thinking that, for example, if you add 5 and NULL, you'd get 5, but NULL is apparently more like a black hole than a zero.

This means I can't use your example, though. ...because if I use your example, the result will be NULL even if some of the weeks had quantities.

I only want to filter out the row if ALL of the quantity columns for that row have a value of NULL. For example...

Inventory ID Wk1QTY Wk2QTY Wk3QTY
1001 8 2 3 <-DO NOT FILTER OUT
1002 5 6 NULL <-DO NOT FILTER OUT
1003 NULL NULL NULL <-FILTER OUT

 

[SyntaxTerror]

I screwed up the column header spacing in the above example, but you get the idea.

 

[gmmastros]

The only way I can think of is to use an OR, like this:

Select * 
From   YourTable
Where  [!]([/!]Wk1Qty Is NOT NULL
       Or Wk2Qty Is NOT NULL
       Or Wk3Qty Is Not NULL[!])[/!]

Whenever you use an OR condition in a where clause, you should use parenthesis. You don't have to, but you should get in to the habit of doing it this way because you will run in to problems if you add more conditions.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

This also works, but I don't think it is as clear as the OR version.

Where  [!]Not ([/!]Wk1Qty Is NULL
       And Wk2Qty Is NULL
       And Wk3Qty Is NULL[!])[/!]

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[IanWaterman]

I think you need to use and

WHERE not(wk1 is null and wk2 is null and wk3... and wkN IS NULL)

Ian

 

[gmmastros]

I should also point out that this query would be a bit easier if your table was properly normalized. I encourage you to do some research on [google]database normalization[/google].

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SyntaxTerror]

I didn't expect OR to work, and it didn't, but AND didn't either. Hm...

SELECT * FROM #temp_pos 
WHERE SiteID='A01-01'
AND InventoryID='10081'
AND NOT (Wk1POqty IS NULL
	AND Wk2POqty IS NULL
	AND Wk3POqty IS NULL
	AND Wk4POqty IS NULL
	AND Wk5POqty IS NULL
	AND Wk6POqty IS NULL)
ORDER BY siteid asc

Result:

SiteID  InventoryID  Wk1POqty  Wk2POqty  Wk3POqty  Wk4POqty  Wk5POqty  Wk6POqty
A01-01     10081      NULL       NULL       5        5         NULL       NULL

 

[SyntaxTerror]

Thanks about the normalization info. It might be a bit beyond my understanding at this point, but I'll look into it.

As I think about this, I'm realizing it'd probably be better if I just made all of the NULLs into 0s anyway so I can make mathematical calculations on the rows without the totals becoming NULL and just filter out the rows with a sum of 0.

 

[PWise]

if you treat nulls and zeros (0) the same you could use
where isnull(wk1,0)+isnull(wk2,0)+isnull(wk3,0)......<>0

 

[PWise]

I should also point out that this query would be a bit easier if your table was properly normalized. I encourage you to do some research on database normalization.

Perhaps this is a pivot table

 

[SyntaxTerror]

Thanks PWise. I think I need to actually just create an update for the table where I overwrite the NULLs with 0s. There is no reason for them to remain NULL.

My hardest part as I'm learning this stuff is translating concepts into syntax; I just don't know how to phrase what I want half the time.

I think I could use a case statement for each column to set the nulls to 0, but that would be pretty cumbersome. Any shortcuts to set all nulls to 0 based on the code I showed above?

 

[SyntaxTerror]

Sorry for any confusion. I'm pulling this information from a temp table I created (My script is HUGE... Many calculations... Had to dump the calculations somewhere so I could work with the results)

 

[IanWaterman]

The result you got using AND looked correct

SiteID  InventoryID  Wk1POqty  Wk2POqty  Wk3POqty  Wk4POqty  Wk5POqty  Wk6POqtyA01-01     10081      NULL       NULL       5        5         NULL       NULL

What is wrong with it, we have misuderstood what you are trying to achieve.

Ian

 

[SyntaxTerror]

I'm so sorry Ian. You're right. I don't know what I was thinking... Didn't get any sleep last night. Yes, that works great.

I'm still leaning toward updating the NULLs to be 0s though. Is there a really quick way to do this? I know you normally need to specify the columns and conditions for each column, but I didn't know if you could somehow use * to achieve something like...

UPDATE temp
SET *=0
FROM #temptable as temp
WHERE * IS NULL

I know that wouldn't work, but see what I mean?

 

[IanWaterman]

Just use the isnull() function

isnull(wk1,0)

Ian

 

[markros]

You need to use

select * from ...
where NOT (Qty1 IS NULL and Qty2 IS NULL and Qty3 IS NULL etc)

PluralSight Learning Library

 

[gmmastros]

To update all your NULLS to 0:

Update #temp_pos
Set    Wk1POqty = IsNull(Wk1POqty, 0),
       Wk2POqty = IsNull(Wk2POqty, 0),
       Wk3POqty = IsNull(Wk3POqty, 0),
       Wk4POqty = IsNull(Wk4POqty, 0),
       Wk5POqty = IsNull(Wk5POqty, 0),
       Wk6POqty = IsNull(Wk6POqty, 0)

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SyntaxTerror]

Thank you gmmastros! ...and everyone else! You all are great