[quoted text, click to view]

The simple CASE expression; here is the BNF for a <case
specification>:

<case specification> ::= <simple case> | <searched case>

<simple case> ::=
CASE <case operand>
<simple when clause>...
[<else clause>]
END

<searched case> ::=
CASE
<searched when clause>...
[<else clause>]
END

<simple when clause> ::= WHEN <when operand> THEN <result>

<searched when clause> ::= WHEN <search condition> THEN <result>

<else clause> ::= ELSE <result>

<case operand> ::= <value expression>

<when operand> ::= <value expression>

<result> ::= <result expression> | NULL

<result expression> ::= <value expression>

The searched CASE expression is probably the most used version of the
expression. The WHEN ... THEN ... clauses are executed in left to
right order. The first WHEN clause that tests TRUE returns the value
given in its THEN clause. And, yes, you can nest CASE expressions
inside each other. If no explicit ELSE clause is given for the CASE
expression, then the database will insert a default ELSE NULL clause.
If you want to return a NULL in a THEN clause, then you must use a
CAST (NULL AS <datatype>) expression. I recommend always giving the
ELSE clause, so that you can change it later when you find something
explicit to return.
The <simple case expression> is defined as a searched CASE expression
in which all the WHEN clauses are made into equality comparisons
against the <case operand>. For example

CASE iso_sex_code
WHEN 0 THEN 'Unknown'
WHEN 1 THEN 'Male'
WHEN 2 THEN 'Female'
WHEN 9 THEN 'N/A'
ELSE NULL END

could also be written as:

CASE
WHEN iso_sex_code = 0 THEN 'Unknown'
WHEN iso_sex_code = 1 THEN 'Male'
WHEN iso_sex_code = 2 THEN 'Female'
WHEN iso_sex_code = 9 THEN 'N/A'
ELSE NULL END

There is a gimmick in this definition, however. The expression

CASE foo
WHEN 1 THEN 'bar'
WHEN NULL THEN 'no bar'
END

becomes

CASE WHEN foo = 1 THEN 'bar'
WHEN foo = NULL THEN 'no_bar' -- error!
ELSE NULL END

Hi...

i have:

SELECT
DATEDIFF(yyyy, birthdate, GETDATE()) AS Age,
case
when DATEDIFF(yyyy, birthdate, GETDATE())<=17 then 'Teen'
when DATEDIFF(yyyy, birthdate, GETDATE())>17 and DATEDIFF(yyyy,
birthdate, GETDATE())<=20 then 'Young'
else 'Other'
end as ClasifAge

A time ago, a topic was set on how aliasescouln't be used here...

But is it more time consuming this kind of programming?
If i had to put 10 different situations on the CASE, will that mean that it
will calculate about 20 times the DATEDIFF function?

Is there a smarter way to do this??

thanks

Andrew,

[quoted text, click to view]


You can make your statement more readable by doing the age
calculation just once in a derived table. The execution plans for
both versions of the query are identical.
In both cases the scalar expression is only computed once per row.

create table t1 (birthdate datetime)
insert t1 values ('19260612')
insert t1 values ('19520410')
insert t1 values ('19460824')
insert t1 values ('19810308')
insert t1 values ('19950511')
insert t1 values ('19831008')

SELECT
DATEDIFF(yyyy, birthdate, GETDATE()) AS Age,
case
when DATEDIFF(yyyy, birthdate, GETDATE())<=17 then 'Teen'
when DATEDIFF(yyyy, birthdate, GETDATE())>17 and
DATEDIFF(yyyy,
birthdate, GETDATE())<=20 then 'Young'
else 'Other'
end as ClasifAge
from t1

select Age, case
when Age <= 17 then 'Teen'
when Age > 17 and Age <= 20 then 'Young'
else 'Other'
end as ClasifAge
from (select datediff(yyyy, birthdate, getdate())
from t1) t1(Age)
go
drop table t1

Linda