How to remove nulls from an right outer jon query??

[spiff2002]

Let's say I have to small tables

customer

id name
1 cust1
2 cust2
3 cust3
4 cust4

Sales
inv# custid date sale
1 1 2004-12-03 $203.56
2 1 2004-12-04 $405.67
3 2 2004-12-04 $304.68
4 3 2004-12-05 $200.00

If I want to run a sales report for the month of December 2004
I run this Query

Select customer.id, customer.name sum(sales.sale) from Sales inner join customer on customer.id=sales.custid
where year(sales.date)='2004' an month(sales.date)='12'
group by
customer.id,
customer.name

and the end result should be this
id name sales
1 Cust1 609.23
2 Cust2 304.68
3 cust3 200.00

Now if I want to include cuatomer with no sales the I would have to change my iiner join to a right join like this


Select customer.id, customer.name sum(sales.sale) from customer right outer join Sales on customer.id=sales.custid
where year(sales.date)='2004' an month(sales.date)='12'
group by
customer.id,
customer.name

and the end result should be this
id name sales
1 cust1 609.23
2 cust2 304.68
3 cust3 200.00
4 cust4 NULL

This may be very simple to answer but how can I replace the Null result with a 0 or if the column was a varchar type column how can i replace it with an empty character '' ?????

I'm running MS SQL server 2000

Thank you in advance

 

[SQLSister]

Look in BOL at the ISNull function.

Questions about posting. See faq183-874

 

[hmckillop]

A couple of things with your query, in your example you would need a LEFT OUTER JOIN, as you want all the rows in the first table in this case the customers,
secondly your where clause will automatically exclude any values where there are no sales as it doesnt have a Date value for the DATE column and as such wont meet your WHERE criteria

And as SQl Sister says you would need something like is null.

I created an example based on what you had but changed to left join and removed the WHERE ( you could include a NULL comparison here if you wanted matches for all customers as shown in commented out section)

create table customers
( custid int primary key,
	customername varchar(10)
)
go
create table sales
(
	invId int primary key,
	custid int,
	dateofsale datetime,
	salevalue money
)
go
INSERT into customers values(1      , 'cust1')
INSERT into customers values(2      , 'cust2')
INSERT into customers values(3      , 'cust3')
INSERT into customers values(4      , 'cust4')

INSERT INTO SALES Values (1        ,1          , '2004-12-03', 203.56)
INSERT INTO SALES Values (2        ,1          , '2004-12-03', 405.67)
INSERT INTO SALES Values (3        ,2          , '2004-12-03', 304.68)
INSERT INTO SALES Values (4        ,3          , '2004-12-03', 200.00)




Select cu.custid, cu.customername, case when sum(sales.salevalue) is null then 0 else sum(sales.salevalue) end
from customers cu
left outer join Sales on cu.custid=sales.custid
where (year(sales.dateofsale)='2004' or year(sales.dateofsale) IS NULL) 
	and (month(sales.dateofsale)='12' OR month(sales.dateofsale) IS NULL)
group by 
cu.custid, 
cu.customername

"I'm living so far beyond my income that we may almost be said to be living apart

 

[spiff2002]

Thank you all. The isnull function did it.