Posted By: Anonymous
I have developed a query, and in the results for the first three columns I get
NULL. How can I replace it with
Select c.rundate, sum(case when c.runstatus = 'Succeeded' then 1 end) as Succeeded, sum(case when c.runstatus = 'Failed' then 1 end) as Failed, sum(case when c.runstatus = 'Cancelled' then 1 end) as Cancelled, count(*) as Totalrun from ( Select a.name,case when b.run_status=0 Then 'Failed' when b.run_status=1 Then 'Succeeded' when b.run_status=2 Then 'Retry' Else 'Cancelled' End as Runstatus, ---cast(run_date as datetime) cast(substring(convert(varchar(8),run_date),1,4)+'/'+substring(convert(varchar(8),run_date),5,2)+'/' +substring(convert(varchar(8),run_date),7,2) as Datetime) as RunDate from msdb.dbo.sysjobs as a(nolock) inner join msdb.dbo.sysjobhistory as b(nolock) on a.job_id=b.job_id where a.name='AI' and b.step_id=0) as c group by c.rundate
When you want to replace a possibly
null column with something else, use IsNull.
SELECT ISNULL(myColumn, 0 ) FROM myTable
This will put a 0 in myColumn if it is null in the first place.