How to Handle NULL Values in SQL Server Queries and Replace Them with 0?

Dealing with NULL Values in SQL Server Queries: Replacing with 0

When working with SQL Server databases, encountering NULL values in query results can hinder data analysis. For instance, consider the following query:

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(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

This query retrieves data from two tables (sysjobs and sysjobhistory) and performs calculations based on the runstatus column. However, if runstatus has NULL values, the query may return unexpected results.

Replacing NULL with 0

To replace NULL values with 0 in SQL Server, use the isnull() function. This function returns the specified value if the input value is NULL; otherwise, it returns the input value.

isnull(myColumn, 0)

In the given query, c.runstatus may have NULL values. To replace these values with 0, modify the query as follows:

Select c.rundate,
    sum(case when isnull(c.runstatus, 0) = 'Succeeded' then 1 end) as Succeeded,
    sum(case when isnull(c.runstatus, 0) = 'Failed' then 1 end) as Failed,
    sum(case when isnull(c.runstatus, 0) = '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(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

By using isnull(), NULL values in c.runstatus are replaced with 0 before performing calculations. This ensures that the query returns accurate results even in the presence of missing data.

The above is the detailed content of How to Handle NULL Values in SQL Server Queries and Replace Them with 0?. For more information, please follow other related articles on the PHP Chinese website!