Unable to get 0 in place of a NULL

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

SQL Server 2008 Forums

Transact-SQL (2008)

Unable to get 0 in place of a NULL

Author

Topic

McBeef
Starting Member

14 Posts

Posted - 2012-08-23 : 15:53:48

So I have a view qryBoxNum with 3 columns: fkJobID, BuildDate, and Box. Most potential date and job combinations won't have a record and if there no record, the Box for that combination should be regarded as being 0. I thought this would be a simple use ISNULL when using this view but when I run this statement:

SELECT ISNULL(qryBoxNum.Box,0) FROM qryBoxNum WHERE fkJobID = 219 AND BuildDate = '03/08/2012'

I get back 6 but when I run the statement below, expecting zero I get back nothing:

SELECT ISNULL(qryBoxNum.Box,0) FROM qryBoxNum WHERE fkJobID = 219 AND BuildDate = '08/08/2012'

So how do I fix this statement?

chadmat
The Chadinator

1974 Posts

Posted - 2012-08-23 : 16:14:09

If there is no result for that query, you will get nothing back, not NULL.

-Chad

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2012-08-23 : 16:14:37

You are using different dates in the WHERE clause. Did you mean to? Is there data for each date?

=================================================
We are far more concerned about the desecration of the flag than we are about the desecration of our land. -Wendell Berry

chadmat
The Chadinator

1974 Posts

Posted - 2012-08-23 : 16:17:16

NULL is not the same as no results.

-Chad

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-23 : 17:50:06

looks like you need to generate a master list using cross join between dates and jobs and them use it to left join to view

ie like


SELECT v1.builddate,v2.fkjobid,COALESCE(v3.Box,0)
FROM (SELECT distinct fkjobid from view)v1
CROSS JOIN (SELECT distinct builddate from view)v2
LEFT JOIN view v3
ON v3.fkjobid = v1.fkjobid
AND v3.builddate = v2.builddate

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

McBeef
Starting Member

14 Posts

Posted - 2012-08-23 : 20:12:30

My mistake. It looks like I need to query the Jobs table and do a left outer join on qryBoxNum.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-23 : 21:38:09

quote:
Originally posted by McBeef

My mistake. It looks like I need to query the Jobs table and do a left outer join on qryBoxNum.

if you want one record per jobs yes thats way to do

but if you want record per job per day you need a cross join to date column table as well

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

McBeef
Starting Member

14 Posts

Posted - 2012-08-24 : 14:40:31

If its of any interest, this seems to work:

SELECT ISNULL(qryBoxNum.Box,0) AS Box FROM Jobs LEFT OUTER JOIN qryBoxNum ON qryBoxNum.fkJobID = Jobs.JobID AND BuildDate = CONVERT(VARCHAR(10), GETDATE(), 101) WHERE JobID = 219

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-24 : 15:17:08

quote:
Originally posted by McBeef

If its of any interest, this seems to work:

SELECT ISNULL(qryBoxNum.Box,0) AS Box FROM Jobs LEFT OUTER JOIN qryBoxNum ON qryBoxNum.fkJobID = Jobs.JobID AND BuildDate = CONVERT(VARCHAR(10), GETDATE(), 101) WHERE JobID = 219

so you had a master table for box?

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources