how to check out null in select statement

[andypower]

hello i m developing application in vb 6 and sql server 7
i want to retrive sum of amount from my salesvoucher table so my query is

select sum(amount) from Salesvoucher

whene there r no data in my table then it returns NULL and need to check that . i m checking that in following ways
curAmount=iif(ISNULL(rsSales(0))=true,0,rsSales(0))

but i dont want to check out this explicitly for that i used case in select statement but still it returns NULL so Plz give me some
suggetion on query or tell me any other way to do it

my case query
select case sum(Amount) when NULL then 0 else sum(Amount) end as Amount from salesvoucher

plz help me because i need it in all my procedure and function

 

[miq]

HI,

Try this

select Amount = (case   when sum(Amount) IS NULL then 0 end) from salesvoucher

B.R,
miq

 

[vongrunt]

Check for ISNULL() or even COALESCE() function. CASE can do the same, for a price of longer and repeatable (btw. ELSE part is missing) SQL command.

------
Math problems? Call 0800-[(10x)(13i)^2]-[sin(xy)/2.362x]

 

[r937]

when ELSE is missing (permissible in the syntax), it is NULL

COALESCE(SUM(amount),0) would be better here



rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)