question

aqaros avatar image
aqaros asked

How do I get 0 instead of null in this query?

declare @P1 varchar(100) = 'BANCA PIS' select a.audit_dt, a.inforce_count, b.submitted_count, c.exception_count, d.cancelled_count from (select CONVERT(varchar, process_dt, 101) AS audit_dt, COUNT(process_dt) inforce_count from tbl_emc_outgoing where emc_app_type_cd=@P1 and MONTH(process_dt)=MONTH(getdate()) and YEAR(process_dt)=YEAR(getdate()) group by CONVERT(varchar, process_dt, 101) ) a join ( select CONVERT(varchar, process_dt, 101) audit_dt, status, COUNT(process_dt) submitted_count from tbl_emc_outgoing where emc_app_type_cd=@P1 and MONTH(process_dt)=MONTH(getdate()) and YEAR(process_dt)=YEAR(getdate()) and status='submitted' group by CONVERT(varchar, process_dt, 101), status ) b on a.audit_dt=b.audit_dt left outer join (select CONVERT(varchar, process_dt, 101) audit_dt, status, COUNT(process_dt) exception_count from tbl_emc_outgoing where emc_app_type_cd=@P1 and MONTH(process_dt)=MONTH(getdate()) and YEAR(process_dt)=YEAR(getdate()) and status='exception' group by CONVERT(varchar, process_dt, 101), status ) c on b.audit_dt=c.audit_dt left outer join (select CONVERT(varchar, process_dt, 101) audit_dt, status, COALESCE ( COUNT(process_dt),0) cancelled_count from tbl_emc_outgoing where emc_app_type_cd=@P1 and MONTH(process_dt)=MONTH(getdate()) and YEAR(process_dt)=YEAR(getdate()) and status='cancelled' group by CONVERT(varchar, process_dt, 101), status ) d on c.audit_dt=d.audit_dt order by a.audit_dt desc ![alt text][1] [1]: /storage/temp/687-sqlresult.gif
nullformattingresults
sqlresult.gif (10.2 KiB)
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley avatar image
Kev Riley answered
You can use [`ISNULL()`][1] to test for null values and replace them with 0 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 select 1,'T1 one' insert into @T1 select 2,'T1 two' insert into @T2 select 1,'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 one 1 T2 one 2 T1 two NULL NULL NULL NULL 0 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 one 1 T2 one 2 T1 two 0 NULL NULL NULL 0 T2 null [1]: http://msdn.microsoft.com/en-us/library/ms184325.aspx
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

apply the isnull() to the outer most reference - otherwise you may be getting null values due to outer joins, but that is 'after' you have applied the isnull() in the subquery. In this case think of isnull() as a presentation layer feature - remove it from any deep queries, and apply it to the final result set.
1 Like 1 ·
I have tried putting isnull function as below ( select CONVERT(varchar, process_dt, 101) audit_dt, status, isnull(COUNT(process_dt),0) submitted_count from tbl_emc_outgoing where emc_app_type_cd=@P1 and MONTH(process_dt)=MONTH(getdate()) and YEAR(process_dt)=YEAR(getdate()) and status='submitted' group by CONVERT(varchar, process_dt, 101), status ) b on a.audit_dt=b.audit_dt left outer join Why is it not working is this context?
0 Likes 0 ·
eghetto avatar image
eghetto answered
Use ISNULL: DECLARE @P1 VARCHAR(100) = 'BANCA PIS' SELECT a.audit_dt ,a.inforce_count ,b.submitted_count ,ISNULL(c.exception_count, 0) AS exception_count -- <-- ISNULL ! ,ISNULL(d.cancelled_count, 0) AS cancelled_count -- <-- ISNULL ! FROM (SELECT CONVERT(VARCHAR, process_dt, 101) AS audit_dt ,COUNT(process_dt) inforce_count FROM tbl_emc_outgoing WHERE emc_app_type_cd = @P1 AND MONTH(process_dt) = MONTH(GETDATE()) AND YEAR(process_dt) = YEAR(GETDATE()) GROUP BY CONVERT(VARCHAR, process_dt, 101)) a JOIN (SELECT CONVERT(VARCHAR, process_dt, 101) audit_dt ,status ,COUNT(process_dt) submitted_count FROM tbl_emc_outgoing WHERE emc_app_type_cd = @P1 AND MONTH(process_dt) = MONTH(GETDATE()) AND YEAR(process_dt) = YEAR(GETDATE()) AND status = 'submitted' GROUP BY CONVERT(VARCHAR, process_dt, 101) ,status) b ON a.audit_dt = b.audit_dt LEFT OUTER JOIN (SELECT CONVERT(VARCHAR, process_dt, 101) audit_dt ,status ,COUNT(process_dt) exception_count FROM tbl_emc_outgoing WHERE emc_app_type_cd = @P1 AND MONTH(process_dt) = MONTH(GETDATE()) AND YEAR(process_dt) = YEAR(GETDATE()) AND status = 'exception' GROUP BY CONVERT(VARCHAR, process_dt, 101) ,status) c ON b.audit_dt = c.audit_dt LEFT OUTER JOIN (SELECT CONVERT(VARCHAR, process_dt, 101) audit_dt ,status ,COALESCE(COUNT(process_dt), 0) cancelled_count FROM tbl_emc_outgoing WHERE emc_app_type_cd = @P1 AND MONTH(process_dt) = MONTH(GETDATE()) AND YEAR(process_dt) = YEAR(GETDATE()) AND status = 'cancelled' GROUP BY CONVERT(VARCHAR, process_dt, 101) ,status) d ON c.audit_dt = d.audit_dt ORDER BY a.audit_dt DESC
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Mrs_Fatherjack avatar image
Mrs_Fatherjack answered
The field you wish to return 0 rather than NULL change to isnull(FIELDName,0) this will convert that field to a 0 if it's NULL.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

aqaros avatar image
aqaros answered
Why this statement not working here ,COALESCE(COUNT(process_dt), 0) cancelled_count ?
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

What is the exact error message?
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.