Post reply

Case statment:while runing the below sql stmt i get execption :Case statment: Msg 102, Level 15, State 1, Line 7:

  • March 23, 2009 at 4:35 am

    #201422

    DECLARE @pAnnualReviewFlag SMALLINT

    SET @pAnnualReviewFlag = 0

    SELECT Count(1) AS totalrows

    FROM v_request_info

    WHERE Coalesce(annual_review_type_id,0) IN (CASE @pAnnualReviewFlag

    WHEN 1 THEN (1 ,5)

    ELSE (2 ,6)

    END)

  • March 23, 2009 at 4:44 am

    #964310

    try this

    WHERE

    (@pAnnualReviewFlag = 1

    AND

    (Coalesce(annual_review_type_id,0) IN (1,5))

    OR

    (@pAnnualReviewFlag != 1

    AND

    (Coalesce(annual_review_type_id,0) IN (2,6))

    ----------------------------------------------
    Try to learn something about everything and everything about something. - Thomas Henry Huxley

    :w00t:
    Posting Best Practices[/url]
    Numbers / Tally Tables[/url]

    SQL-4-Life

  • March 23, 2009 at 4:51 am

    #964315

    May I ask about the values of annual_review_type_id? If you can avoid using a function in your WHERE clause, your life will be much easier. Because the function evaluates for each row instead of for the whole set.

    Reasons why I would avoid using Christopher's solution (no offense, Christopher) is because he adds it into the WHERE clause twice over where you're currently only using it once.

    And if you can avoid using it even the once, you're better off.

    So, why exactly do you need to use COALESCE?

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • March 23, 2009 at 5:03 am

    #964318

    THANKS

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

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