isnull Help - 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

isnull Help

Author

Topic

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2012-10-17 : 10:15:42

Hey guys

This is my query,

however i am getting null values within my data ,

Do you know how i can replace null with zero ?

Declare @date varchar(10)
set @Date = (select dateadd(MM,-2,max(hst_date_processed))
from FDMS.dbo.Fact_Financial_History)

SELECT DISTINCT hst_merchnum as Fdmsaccountno,
[Standard],[CP],[Contactless],[Secure eCom],[TBA],[CHIP],[MOTO],[Non Secure eCom],
ISNULL([Standard],0)
+ISNULL([CP],0)
+ISNULL([Contactless],0)
+ISNULL([Secure eCom],0)
+ISNULL([TBA],0)
+ISNULL([CHIP],0)
+ISNULL([MOTO],0)
+ISNULL([Non Secure eCom],0) as 'Grand Total'

FROM
(select Fact_Financial_History.hst_merchnum,
Dim_Interchange_Tier_2.Qualification_2,
SUM(Fact_Financial_History.hst_sales_amt) AS [sales]
FROM
Dim_Outlet INNER JOIN Fact_Financial_History ON Dim_Outlet.FDMSAccountNo_First9 = Fact_Financial_History.hst_merchnum
INNER JOIN Dim_Interchange_Tier_2 ON Fact_Financial_History.Plan_Key = Dim_Interchange_Tier_2.Plan_Code

Where Dim_Outlet.MCC_Code in ('4814','4816','5967','7273','7841','7995','9754','9399','9754','5122','5912','5993')
and Dim_Interchange_Tier_2.Scheme =('mastercard')
and (hst_date_processed >= @date)
group by
Fact_Financial_History.hst_merchnum,
Dim_Interchange_Tier_2.Qualification_2

)as p
pivot
( MAX([Sales]) FOR Qualification_2 in
([Standard],[CP],[Contactless],[Secure eCom],[TBA],[CHIP],[MOTO],[Non Secure eCom])) as pvt

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-17 : 10:57:45

For each column where you don't want to see null, add a COALESCE or ISNULL such as:

SELECT DISTINCT COALESCE(hst_merchnum,0)           AS Fdmsaccountno,
       COALESCE([Standard],0) as Standard,
       ....

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources