Make value zero if null in Select Statement

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

Make value zero if null in Select Statement

Author

Topic

Vack
Aged Yak Warrior

530 Posts

Posted - 2011-11-18 : 13:40:13

In the code below how to I make CYJanSales and CYFebSales zero if they are NULL?

select slspsn_no, cus_no,
 sum(case when left(inv_dt,4)= year(getdate()) and 
right(left(inv_dt,6),2)= 01 then tot_sls_amt End) as CYJanSales,
sum(case when left(inv_dt,4)= year(getdate()) and 
right(left(inv_dt,6),2)= 02 then tot_sls_amt End) as CYFebSales



from Last3OEhdrhst
group by slspsn_no, cus_no
order by slspsn_no

Vack
Aged Yak Warrior

530 Posts

Posted - 2011-11-18 : 13:48:08

I was able to figure this out.

isnull(sum(case when left(inv_dt,4)= year(getdate()) and
right(left(inv_dt,6),2)= 01 then tot_sls_amt End),0)

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2011-11-18 : 13:50:30

select slspsn_no, cus_no,
sum(ISNULL(case when left(inv_dt,4)= year(getdate()) and
right(left(inv_dt,6),2)= 01 then tot_sls_amt End,0)) as CYJanSales,
sum(ISNULL(case when left(inv_dt,4)= year(getdate()) and
right(left(inv_dt,6),2)= 02 then tot_sls_amt End,0)) as CYFebSales
from Last3OEhdrhst
group by slspsn_no, cus_no
order by slspsn_no

--------------------------
http://connectsql.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-19 : 01:48:59

quote:
Originally posted by Vack

I was able to figure this out.

isnull(sum(case when left(inv_dt,4)= year(getdate()) and
right(left(inv_dt,6),2)= 01 then tot_sls_amt End),0)

no need of isnull check like this. you can just do like


select slspsn_no, cus_no,
 sum(case when left(inv_dt,4)= year(getdate()) and 
right(left(inv_dt,6),2)= 01 then tot_sls_amt else 0 End) as CYJanSales,
sum(case when left(inv_dt,4)= year(getdate()) and 
right(left(inv_dt,6),2)= 02 then tot_sls_amt else 0 End) as CYFebSales



from Last3OEhdrhst
group by slspsn_no, cus_no
order by slspsn_no

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources