ISNULL in CASE and SUM 1

[Cpreston]

Hi

In 2 rows of codes I have it is bringing back NULL values. I ma going to sue this query for an Excel sheet eventually, so want to display 0 instead of null. In most of the code I have it working but in these 2 lines I cannot get it working, would someone help with the syntax. I have tried the ISNULL in may places on the of both the CASE WHEN and the SUM but I cant seem to get it working. Any ideas please guys. Thanks

CASE WHEN [W/DaysBeforeZeroStock]<= 0 THEN 0 ELSE [W/DaysBeforeZeroStock] END AS [W/DaysBeforeZeroStock],

SUM((dbo.[148-vwMinMaxStockLevel_Feed v2 p2].[On WO])) AS M3onWO,

 

[Cpreston]

Just an update I have the SUM working now

SUM(ISNULL(dbo.[148-vwMinMaxStockLevel_Feed v2 p2].[On WO],0)) AS M3onWO,

I am still stuck on the CASE WHEN line though, any help appreciated. Thanks

 

[bborissov]

There is no need to put ISNULL() in SUM() because by default SUM() (and any other aggregate functions) skip NULL values:
This:

SUM(ISNULL(dbo.[148-vwMinMaxStockLevel_Feed v2 p2].[On WO],0)) AS M3onWO,

should be equal to this:

SUM([dbo].[148-vwMinMaxStockLevel_Feed v2 p2].[On WO]) AS M3onWO,

About CASE - Everything looks fine there. What is the error?

Borislav Borissov
VFP9 SP2, SQL Server

 

[Cpreston]

The case one brings back NULL in some rows. I want the NULL to be 0, but cannot work out how to put the isnull in so it returns 0.

The SUM one was returning NULL until I put the ISNULL in, could be our poor data.

Anyway that is what is wrong with the CASE, thanks for the reply

 

[bborissov]

Then it should be:

CASE WHEN ISNULL([W/DaysBeforeZeroStock], 0) <= 0 THEN 0 ELSE [W/DaysBeforeZeroStock] END AS [W/DaysBeforeZeroStock],

And if in ALL records dbo.[148-vwMinMaxStockLevel_Feed v2 p2].[On WO] is NULL then yes, it will give you NULL as a result.


Borislav Borissov
VFP9 SP2, SQL Server

 

[Cpreston]

Thanks bborissov

I thought I had tried that format, but obviously ahd not. It works great, thanks for all the replys