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

Replacing NULL Values with 0 in SQL Server Queries

When working with SQL queries, it's common to encounter NULL values in certain columns, which can complicate data handling and analysis. To replace NULL values with a specific value, such as 0, in a SQL Server query, you can utilize the ISNULL function.

The ISNULL function takes two arguments: a potentially null column and a replacement value. In your case, you want to replace NULL values in the first three columns (Succeeded, Failed, Cancelled) with 0. Here's how you can achieve that:

SELECT ISNULL(column_name, 0) AS column_alias, ...

For example, to replace NULL values in the first three columns of your query with 0, you would modify it as follows:

SELECT ISNULL(c.rundate, 0) AS rundate,
       ISNULL(sum(case when c.runstatus = 'Succeeded' then 1 end), 0) AS Succeeded,
       ISNULL(sum(case when c.runstatus = 'Failed' then 1 end), 0) AS Failed,
       ISNULL(sum(case when c.runstatus = 'Cancelled' then 1 end), 0) 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 modified query uses the ISNULL function to ensure that even if the first three columns contain NULL values, they will be replaced with 0 before being used in calculations or displayed in the results.

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