Case in a where clause?

  • Hi

    Can someone let me know what I;m doing wrong here?

    I thought this was correct but keep getting a syntax error

    Thanks

    WHERE([Admission Date] IS NOT NULL)
       and
         Case when @Parameter = 'A'
         then
          ([Close Date] IS NULL)
           AND ([Discharge Date] IS NULL)
       else
         ([Close Date] IS not NULL)
           AND ([Discharge Date] IS not NULL)
       end
    ;

  • CASE must evaluate to a single value.  For this, you need to use "standard" WHERE clause and/ors:

    WHERE([Admission Date] IS NOT NULL)
    and
    (@Parameter = 'A' and [Close Date] IS NULL and [Discharge Date] IS NULL)
    or
    ((@Parameter is null or @Parameter <> 'A') and [Close Date] IS not NULL and [Discharge Date] IS not NULL)
    ;

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • jbalbo - Tuesday, February 5, 2019 12:12 PM

    Hi

    Can someone let me know what I;m doing wrong here?

    I thought this was correct but keep getting a syntax error

    Thanks

    WHERE([Admission Date] IS NOT NULL)
       and
         Case when @Parameter = 'A'
         then
          ([Close Date] IS NULL)
           AND ([Discharge Date] IS NULL)
       else
         ([Close Date] IS not NULL)
           AND ([Discharge Date] IS not NULL)
       end
    ;

    This should get you what you are looking for
    WHERE ([Admission Date] IS NOT NULL)
    AND CASE
      WHEN @Parameter = 'A' AND ([Close Date] IS NULL) AND ([Discharge Date] IS NULL) THEN 1
      WHEN @Parameter <> 'A' AND ([Close Date] IS NOT NULL) AND ([Discharge Date] IS NOT NULL) THEN 1
      ELSE 0
      END = 1

  • ScottPletcher - Tuesday, February 5, 2019 12:35 PM

    CASE must evaluate to a single value.  For this, you need to use "standard" WHERE clause and/ors:

    WHERE([Admission Date] IS NOT NULL)
    and
    (@Parameter = 'A' and [Close Date] IS NULL and [Discharge Date] IS NULL)
    or
    ((@Parameter is null or @Parameter <> 'A') and [Close Date] IS not NULL and [Discharge Date] IS not NULL)
    ;

    Thanks Scott I totally missed that I dont know why, This works great but so doesn't  DesNorton.
    Nice to know both 

    Thanks again So much

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

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