You are here

SQL: Case Expression

case expression

CASE EXPRESSION

In this post we will discuss CASE statement. This statement performs some kind of an action or controls the flow of the code.

  • The CASE expression has two forms- the simple form and the searched form. CASE returns a value, and hence is an expression.

Simple CASE

  • The simple form compares an input expression to multiple possible scalar when expressions, returns the result expression associated with the first match.
  • If there’s no match and an ELSE clause is specified, the else expression is returned.
  • If there’s no ELSE clause, the default is ELSE NULL is returned.

Syntax:
CASE input_expression
When when_exp THEN ‘result_exp’
ELSE else_exp
END

Searched CASE

  • The searched form of the CASE expression is more flexible. Instead of comparing an input expression to multiple possible expressions, it uses predicates in the WHEN clauses, and the first predicate that evaluates to true determines which when expression is returned.
  • If none is true, the CASE expression returns the else expression.

Syntax:
CASE
When boolean_expression THEN  ‘result_exp
             ELSE else_exp
END

Example:

–create table
create table tblcase(id int NOT NULL, ename varchar(50),size varchar(50) NULL, Disdate date NULL);
–insert records
insert into tblcase(id, ename, size, Disdate) values(1,’Jay’,’M’,’20171204′),(2,’Piya’,’S’,NULL),(3,’Piyu’,’L’,’20160928′),(4,’jiya’,’XL’,NULL);
–retrieve records
select * from tblcase;
output:

Id

ename size Disdate
1 Jay M

2017-12-04

2

Piya S NULL
3 Piyu L

2016-09-28

4

Jiya

XL

NULL

Simple CASE:

select ename, size,
CASE size
when ‘S’ then ‘small’
when ‘M’ then ‘medium’
when ‘L’ then ‘large’
when ‘XL’ then ‘extra-large’
ELSE ‘n/a’
END as productsize
from tblcase;
output:

ename

size productsize
Jay M

medium

Piya

S small
Piyu L

large

Jiya

XL

extra-large

Searched CASE:

select ename, Disdate,
CASE
when Disdate IS NULL then ‘On sale’
ELSE ‘sale is over’
END as salestatus
from tblcase;
output:

ename

size productsize
Jay 2017-12-04

sale is over

Piya

NULL On sale
Piyu 2016-09-28

sale is over

Jiya

NULL

On sale

Keep Visiting TechwithR.com for more upcoming top technical articles and also see my new blog Mag4info. and for kotlin tutorial also see my blog kotlin category.

You can also by SQL 70-761 book write by me from Amazon.
You can also by Kotlin Book write by me from Amazon.

Leave a Reply

Top