i have a problem in SQL SERVER 2005 isnull function
i have query lk this
select isnull(sum(a.Totalreceipt),0)
from GRNrmdayconsolidated a
where convert(varchar(10),a.ReceiptDate,120)='2008-008-28'
group by a.itemcode,a.vendorid
If there r no records with this date it wil retum nothing
bt same query if i remove the group by clause
select isnull(sum(a.Totalreceipt),0)
from GRNrmdayconsolidated a
where convert(varchar(10),a.ReceiptDate,120)='2008-008-28'
when there is no record it wil return 0
again in the same query if i remove the sum
select isnull((a.Totalreceipt),0)
from GRNrmdayconsolidated a
where convert(varchar(10),a.ReceiptDate,120)='2008-008-28'
It wil nt return any thing
What is the problem in frist and last query and how isnull function works
|
| Author: Venkatesan Prabu .J 31 Aug 2008 | Member Level: Diamond | Rating:  Points: 6 |
I am not clear about your needs. Any way, let me explain some key concepts in your query,
Below query will fetch the sum of your receipt on the specific date.
If the sum value is null , then 0 will be replaced, in the below query you will get multiple rows grouped under itemcode and vendor id
select isnull(sum(a.Totalreceipt),0)
from GRNrmdayconsolidated a
where convert(varchar(10),a.ReceiptDate,120)='2008-008-28'
group by a.itemcode,a.vendorid
In this code you will get a single row with sum of the values on the specific date.
select isnull(sum(a.Totalreceipt),0)
from GRNrmdayconsolidated a
where convert(varchar(10),a.ReceiptDate,120)='2008-008-28'
Here you will get the exact data on the specific date.
select isnull((a.Totalreceipt),0)
from GRNrmdayconsolidated a
where convert(varchar(10),a.ReceiptDate,120)='2008-008-28'
Regards,
Venkatesan prabu .J
http://venkattechnicalblog.blogspot.com/
|