من قبل
Mohamed Ramy Tmar , Senior Scheduler Team Lead , Jacobs Engineering SA
You can use ISNULL() to test for null values and replace them with0
Such as
select a.audit_dt,
isnull(a.inforce_count,0) as inforce_count,
isnull(b.submitted_count,0) as submitted_count,
insull(c.exception_count,0) as exception_count,
isnull(d.cancelled_count,0) as cancelled_count
from .....
let me explain with an example why the isnull isn't giving you what you think it should
declare @T1 table (id int, blah varchar(10))
declare @T2 table (id int, blah varchar(10))
insert into @T1 select1,'T1 one'
insert into @T1 select2,'T1 two'
insert into @T2 select1,'T2 one'
insert into @T2 select null,'T2 null'
select
T1.id ,
T1.blah ,
T2.id ,
T2.blah
from (select [@T1].id, [@T1].blah from @T1) T1
full join
(select isnull([@T2].id,0) as id, [@T2].blah from @T2) T2
on T1.id = T2.id
The query above has the isnull() but the result set still shows null on the second row as this is a row produced by the outer join, where there is no corresponding record in T2 for the value of id =1. The inner isnull() function is not invoked.
id blah id blah
----------- ---------- ----------- ----------1 T1 one1 T2 one2 T1 two NULL NULL
NULL NULL0 T2 null
By moving the isnull() to the outer layer of the query, we are applying the function to the full result set instead:
select
T1.id ,
T1.blah ,
isnull(T2.id ,0) as id,
T2.blah
from (select [@T1].id, [@T1].blah from @T1) T1
full join
(select [@T2].id as id, [@T2].blah from @T2) T2
on T1.id = T2.id
id blah id blah
----------- ---------- ----------- ----------1 T1 one1 T2 one2 T1 two0 NULL
NULL NULL0 T2 null
من قبل
Shahzad Khan , Sr. Software Developer , Mar Incorporated
COALESCE function operates on NULL. In this example COALESCE function test NULL value for first parameter, Column. Since Column has NULL value, it will return0, the value of second parameter.
