How often do you get some bad data and land up with that awesome unexpected 0 creeping into your long running queries.
An example of working out a percentage of a value within a group in sql
The simplest way of solving this was to use the NullIf Key. This returns NULL if the value is the the same as the second expression passed to the function. this can be COALESCEd to 1 if found.
SELECT NULLIF(0,0) will return NULL
SELECT NULLIF(1,0) will return 1.
This is a great way to check if a value is 0. If we get a NULL, then we can now default that number to 1 and avoid the Divide by Zero error.
select COALESCE(NULLIF(0,0),1) will return 1.
This is a simple and syntactically nice way of updating a query without horrible CASE WHEN statements and WHERE clauses on non indexed values.
More References....
http://msdn.microsoft.com/en-us/library/ms177562.aspx
http://blog.sqlauthority.com/2007/06/22/sql-server-explanation-and-comparison-of-nullif-and-isnull/
Chz
Subscribe to:
Post Comments (Atom)
Hi, I have an issue below is the code I am working on, the issue is that I have checked and there are no zero's in the data for it to even complain about. Please assist if or where possible.
ReplyDeleteRegards
Darren
SELECT IVR.I_VENDV AS Vendor_Number
, BIACREP.ACAWNA AS Vendor_Name
, BIACREP.ACWQS1 AS Vendor_Status
, IVR.I_ITEMV AS Item_Number
, ICI.I_DESC AS Item_Description
, IGI.I_PALT_LAYERS AS Hi, IGI.IG_SPCK AS Ship_Pack_Quantity
, IGI.I_PALT_SIZE AS Pallet_Quantity
, IGI.I_PALT_LAYER_CASES AS Ti
, CONVERT(DECIMAL(8, 2), IVR.IV_QPCK / IVR.I_MPCK) AS Buying_Quantity
, IVR.IV_QPCK
, IVR.I_MPCK
, CONVERT(DECIMAL(8, 2), IVR.I_MPCK / IGI.IG_SPCK) AS Vendor_Pack
, IVR.I_MPCK
, IGI.IG_SPCK
FROM LVD.IVR AS IVR INNER JOIN
LVD.IGI AS IGI ON IVR.I_ITEMV = IGI.I_ITEMG INNER JOIN
LVD.ICI AS ICI ON ICI.I_ITEMC = IVR.I_ITEMV INNER JOIN
LVD.BIACREP AS BIACREP ON IVR.I_VENDV = BIACREP.ACA2CD
WHERE (IVR.I_VENDV IN (600224,600226,600227)) AND (IGI.I_PALT_LAYER_CASES = 0)