SQLTeam.com | Weblogs | Forums

How can I use CASE syntax to test rule?


I have three fields with values in my table, account_number, income, and income_level. I need to test if the income_level is assigned correctly. The rule is if income>50,000, income_level=hight, 30,000-50,000 medium, <30,000 low. I need to returns account nbr that has incorrect income_level. How can I use CASE sentence to implement it?

account_nbr income income_level
1 100,000 high
2 2,000 low
3 15,000 medium


Yup... A CASE expression is exactly what you want to use...

	income_level = CASE	
						WHEN x.income < 30000 THEN 'Low'
						WHEN x.income < 50000 THEN 'Medium'
						ELSE 'High'