Post reply

Case statement in where clause (Help)

  • May 13, 2012 at 10:58 pm

    #257885

    hi all..i need to write a query whereby using case statement in where clause...can anyone guide me? what i want is in my table i need to base on one column(co_role) to determine my where clause..eg:-

    delete from co_employee where (co_salary > 5000 or co_transfer = 'Y') and co_department = 'HR' and co_role = 'Manager'

    delete from co_employee where co_class = 'Contract' and co_salary > 2000 and co_department = 'HR' and co_role = 'Staff'

    so i was thinking to write in this way but its not working:-

    delete from co_employee where case (co_role) when 'Manager' then ((co_salary > 5000 or co_transfer = 'Y') and co_department = 'HR') when 'Staff' then (co_class = 'Contract' and 'co_salary > 2000 and co_department = 'HR')

    else null end

    thanks

  • May 14, 2012 at 12:57 am

    #1486951

    Have you considered taking the two WHERE clauses from your two DELETEs and combining them with an OR instead of trying to use CASE?

    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • May 14, 2012 at 6:59 am

    #1487070

    The CASE function cannot return a Boolean value. You can either rewrite your query to return a pseudo-Boolean value and then check whether that value is equal to "true" or you can rewrite the query to return a specific value which is used for the comparison. For example,

    WHERE Co_Salary > CASE Co_Role WHEN 'Manager' THEN 5000 WHEN Contract THEN 2000 END

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

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

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