Make Null equal zero - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Make Null equal zero

Author

Topic

Vack
Aged Yak Warrior

530 Posts

Posted - 2011-10-24 : 12:41:30

For Week 1 thru Week12 how do I make those values zero if they are null?

SELECT     TOP (100) PERCENT dbo.GLSReport.comp_item_no, dbo.iminvloc_sql.qty_on_hand, dbo.iminvloc_sql.qty_on_ord, dbo.GLSReport.PastDue, dbo.GLSReport.Week1, 
                      dbo.GLSReport.Week2, dbo.GLSReport.Week3, dbo.GLSReport.Week4, dbo.GLSReport.Week5, dbo.GLSReport.Week6, dbo.GLSReport.Week7, dbo.GLSReport.Week8,
                       dbo.GLSReport.Week9, dbo.GLSReport.Week10, dbo.GLSReport.Week11, dbo.GLSReport.Week12
FROM         dbo.imitmidx_sql INNER JOIN
                      dbo.GLSReport ON dbo.imitmidx_sql.item_no = dbo.GLSReport.comp_item_no INNER JOIN
                      dbo.iminvloc_sql ON dbo.imitmidx_sql.item_no = dbo.iminvloc_sql.item_no
WHERE     (dbo.iminvloc_sql.loc = '01') AND (dbo.imitmidx_sql.item_note_4 = 'COMPOSITE ONE')
ORDER BY dbo.GLSReport.comp_item_no

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2011-10-24 : 12:47:20

change to a left join and look for the nulls

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Vack
Aged Yak Warrior

530 Posts

Posted - 2011-10-24 : 12:53:21

How do you look for nulls?

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-10-24 : 12:54:09

use the COALESCE or ISNULL function.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-24 : 12:54:15

use ISNULL(field,0)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Vack
Aged Yak Warrior

530 Posts

Posted - 2011-10-24 : 12:56:43

thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-24 : 13:37:33

wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

craigwg
Posting Yak Master

154 Posts

Posted - 2011-10-24 : 15:37:18

I'd use a case statement:

CASE WHEN dbo.GLSReport.Week1 is null THEN 0 ELSE dbo.GLSReport.Week1 END

And just repeat that for each column in your select statement.

Craig Greenwood

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-25 : 00:17:48

quote:
Originally posted by craigwg

I'd use a case statement:

CASE WHEN dbo.GLSReport.Week1 is null THEN 0 ELSE dbo.GLSReport.Week1 END

And just repeat that for each column in your select statement.

Craig Greenwood

thats same as COALESCE

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jassi.singh
Posting Yak Master

122 Posts

Posted - 2011-10-25 : 02:46:06

you can use isnull function or case statement to get rid of null values

Please mark answer as accepted if it helped you.

Thanks,
Jassi Singh

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2011-10-25 : 13:13:14

OR... OR....

You could use the ISNULL function.

OR!!

Perhaps a case statement.

MAYBE!!!
COALESCE will help?

Mark this as redundant if you see it as such!

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2011-10-25 : 14:42:03

>>Mark this as redundant if you see it as such!

Be One with the Optimizer
TG

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-10-25 : 16:26:08

<celko>Case is an expression or function</celko>
:)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-26 : 00:57:51

quote:
Originally posted by DonAtWork

OR... OR....

You could use the ISNULL function.

OR!!

Perhaps a case statement.

MAYBE!!!
COALESCE will help?

Mark this as redundant if you see it as such!

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources