Convert NULL to 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

Convert NULL to Zero

Author

Topic

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2012-03-21 : 22:17:20

Hi Forumer's

I just want the result of this query is zero instead of NULL values.
Kindly please help me to fix my codes. It's either IS NULL or Coalesce.

I got an error using this codes.

ROUND(coalesce(SUM(CASE WHEN v.lvl=3 THEN v.Total END),0) / coalesce(SUM(CASE WHEN v.lvl=3 THEN v.OrderQty END),0),0) AS ThreeProportionalLT,
ROUND(coalesce(SUM(CASE WHEN v.lvl=6 THEN v.Total END),0) / coalesce(SUM(CASE WHEN v.lvl=6 THEN v.OrderQty END),0),0) AS SixProportionalLT

Note:
I made an adjustment on my Query and its working, but for future requirements, may i know the correct syntax in case like these query. thanks.

Thank you in advance.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-03-22 : 00:05:04

you might get divide by zero with that. NULLIF will check for 0 and return NULL value. After that just use ISNULL or COALESCE on the result of the division


ROUND(
	ISNULL (
		COALESCE (SUM(CASE WHEN v.lvl=3 THEN v.Total    END), 0) 
	/ 	NULLIF   (SUM(CASE WHEN v.lvl=3 THEN v.OrderQty END), 0)
	        ), 0)
      ,0) 
AS ThreeProportionalLT

KH
[spoiler]Time is always against us[/spoiler]

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2012-03-22 : 00:54:16

Thanks for the reply..I got an error..

Msg 174, Level 15, State 1, Line 66
The isnull function requires 2 argument(s).

CTE2 AS
(
Select 
	v.ITEMID,
	i.ITEMNAME,
	AVG(CASE WHEN v.lvl=3 THEN v.LEADTIME ELSE 0 END) AS ThreeMonths,
	AVG(CASE WHEN v.lvl=6 THEN v.LEADTIME ELSE 0 END) AS Sixmnths,
    ROUND(ISNULL(COALESCE (SUM(CASE WHEN v.lvl=3 THEN v.Total END), 0)/ NULLIF(SUM(CASE WHEN v.lvl=3 THEN v.OrderQty END),0)),0),0) AS ThreeProportionalLT,
	ROUND(ISNULL(COALESCE (SUM(CASE WHEN v.lvl=6 THEN v.Total END), 0)/ NULLIF(SUM(CASE WHEN v.lvl=6 THEN v.OrderQty END),0)),0),0) AS SixProportionalLT
From LeadTable v
left Outer Join Dynamics.dbo.INVENTTABLE i
On v.ITEMID = i.ITEMID
Where v.ITEMID not in ('HTC1006',
'HTC1076',
'HTC1079',
'HTC1146')
Group by Itemid, Itemname

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2012-03-22 : 01:03:38

Thank you very much kthan. its working now. i just removed the extra open parentheses.

Select
v.ITEMID,
i.ITEMNAME,
AVG(CASE WHEN v.lvl=3 THEN v.LEADTIME ELSE 0 END) AS ThreeMonths,
AVG(CASE WHEN v.lvl=6 THEN v.LEADTIME ELSE 0 END) AS Sixmnths,
ROUND(ISNULL(COALESCE (SUM(CASE WHEN v.lvl=3 THEN v.Total END), 0)/ NULLIF(SUM(CASE WHEN v.lvl=3 THEN v.OrderQty END),0),0),0) AS ThreeProportionalLT,
ROUND(ISNULL(COALESCE (SUM(CASE WHEN v.lvl=6 THEN v.Total END), 0)/ NULLIF(SUM(CASE WHEN v.lvl=6 THEN v.OrderQty END),0),0),0) AS SixProportionalLT
From LeadTable v
left Outer Join Dynamics.dbo.INVENTTABLE i
On v.ITEMID = i.ITEMID
Where v.ITEMID not in ('HTC1006',
'HTC1076',
'HTC1079',
'HTC1146')

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-03-22 : 02:34:15

welcome

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources