CASE SQL Query Syntax for SQL Server and Oracle
CASE function accepts multiple parameters with conditions. If the condition returns true the corresponding value is returned. There are two ways to write the CASE Statements.
W3Courses was launched in 2003 and has been providing information on technology, business, jobs, and health. W3Courses is continuously adding articles to deliver quality information.
SQL, Oracle, CASE, SQL Server
Method 1
Simple Case Statement with one expressions
CASE input_expression
WHEN value_1 THEN result_1
WHEN value_2 THEN result_2
...
WHEN value_n THEN result_n
ELSE result
END
Example
CASE statusid
WHEN 1 THEN 'Not Started'
WHEN 2 THEN 'In Progress'
WHEN 3 THEN 'Complete'
ELSE 'Invalid'
END
Method 2
Case statement with different conditions and/or expressions
CASE
WHEN condition_1 THEN result_1
WHEN condition_2 THEN result_2
...
WHEN condition_n THEN result_n
ELSE result
END
Example 1
You can use different operators to define conditions
CASE
WHEN statusid <= 2 THEN 'InComplete'
WHEN statusid = 3 THEN 'Complete'
ELSE 'Invalid'
END
Example 2
You can use different expressions to define conditions
CASE
WHEN projectstatusid IS NULL THEN 'Not Approved'
WHEN statusid <= 2 THEN 'InComplete'
WHEN statusid = 3 THEN 'Complete'
ELSE 'Invalid'
END
Conditions are evaluated in the order listed. Once a condition returns a true value, the CASE statement returns the result and not evaluate the conditions any further. So, choose a correct order to list your conditions.
Author
W3Courses was launched in 2003 and has been providing information on technology, business, jobs, and health. W3Courses is continuously adding articles to deliver quality information.
Comments
Tags
SQL, Oracle, CASE, SQL Server
Become a Contributor
- 1150 reads