ISNULL function issue - 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

SQL Server 2005 Forums

Transact-SQL (2005)

ISNULL function issue

Author

Topic

skybvi
Posting Yak Master

193 Posts

Posted - 2012-02-29 : 11:44:04

Hi,
i have declared the @DT5, @DT6 variables.

In the below query, I am still getting NULL values in Last_year_sales column.

SELECT
c.DEP_DESCR as Department,
SUM(ISNULL(SLS_AMT, 0)) as Last_year_Sales

FROM FrontOff.dbo.DAY_PLU_SALES

JOIN FrontOff.dbo.PLU B
ON DAY_PLU_SALES.ITM_ID = B.ITM_ID

left JOIN FrontOff.dbo.DEP c ON
b.STR_HIER_ID=c.STR_HIER_ID

WHERE DT > @DT5
AND DT < @DT6

GROUP BY c.DEP_DESCR
ORDER BY c.DEP_DESCR.

Regards,
Sushant
DBA
Virgin Islands(U.K)

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-02-29 : 11:56:16

Where is SLS_AMT coming from? Is it from DEP?

No, you're never too old to Yak'n'Roll if you're too young to die.

skybvi
Posting Yak Master

193 Posts

Posted - 2012-02-29 : 12:06:19

quote:
Originally posted by webfred

Where is SLS_AMT coming from? Is it from DEP?

No, you're never too old to Yak'n'Roll if you're too young to die.

from DAY_PLU_SALES

Regards,
Sushant
DBA
Virgin Islands(U.K)

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-02-29 : 12:14:22

try to put the ISNULL to the outer side like this:

ISNULL(SUM(SLS_AMT),0) as Last_year_Sales

No, you're never too old to Yak'n'Roll if you're too young to die.

X002548
Not Just a Number

15586 Posts

Posted - 2012-02-29 : 12:36:41

Where does DT come from

You should ALWAYS label your columns, even if they ARE unique

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

skybvi
Posting Yak Master

193 Posts

Posted - 2012-02-29 : 14:25:04

Ok,
I found the faults...
If iam doing a left join, so the rows whcih are left out, thery will be NULL...
So, how to put them as 0 instead of NULL.

Regards,
Sushant
DBA
Virgin Islands(U.K)

X002548
Not Just a Number

15586 Posts

Posted - 2012-02-29 : 15:28:23

can you please post the DDL of the tables

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-02-29 : 15:38:18

COALESCE the SUM don't SUM the COALESCE.

skybvi
Posting Yak Master

193 Posts

Posted - 2012-03-01 : 07:53:31

quote:
Originally posted by Lamprey

COALESCE the SUM don't SUM the COALESCE.

That was it!

Thanks to all.

Regards,
Sushant
DBA
Virgin Islands(U.K)

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-03-01 : 08:53:54

quote:
Originally posted by webfred

try to put the ISNULL to the outer side like this:

ISNULL(SUM(SLS_AMT),0) as Last_year_Sales

No, you're never too old to Yak'n'Roll if you're too young to die.

That was it...

No, you're never too old to Yak'n'Roll if you're too young to die.

skybvi
Posting Yak Master

193 Posts

Posted - 2012-03-01 : 13:47:53

quote:
Originally posted by webfred

quote:
Originally posted by webfred

try to put the ISNULL to the outer side like this:

ISNULL(SUM(SLS_AMT),0) as Last_year_Sales

No, you're never too old to Yak'n'Roll if you're too young to die.

That was it...

No, you're never too old to Yak'n'Roll if you're too young to die.

that I tried, but didn't work ...
Thanks.

Regards,
Sushant
DBA
Virgin Islands(U.K)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources