Post reply

CASE function for Null values

  • April 16, 2009 at 2:35 am

    #131288

    Hi All,

    The following query

    select Category, count(*) as TotalNumber from

    (select

    Case ITPolicyName

    when NULL then 'No Policy Assigned'

    ELSE ITPolicyName

    END as Category

    from table1) as Temp

    group by Category

    order by TotalNumber desc

    is not returning 'No Policy Assigned' as category for Null values in SQL 2005 table.

    Please advise.

    BR,

    Parthi

  • April 16, 2009 at 2:39 am

    #978655

    Sorry experts I solved the problem by

    select Category, count(*) as TotalNumber from

    (select

    Case

    when ITPolicyName is NULL then 'No Policy Assigned'

    ELSE ITPolicyName

    END as Category

    from table1) as Temp

    group by Category

    order by TotalNumber desc

    BR,

    Parthi

  • April 16, 2009 at 2:57 am

    #978662

    SELECT ISNULL(ITPolicyName, 'No Policy Assigned') AS Category,

    COUNT(*) AS TotalNumber

    FROM table1

    GROUP BY ISNULL(ITPolicyName, 'No Policy Assigned')

    ORDER BY 2

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply