Help with CASE syntax

SQL Apprentice Question
Can anyone tell me what is wrong with the following syntax?

CASE {Invoicing Transaction Amounts History:Item Number}
WHEN like 'GP%'
THEN {Invoicing Transaction Amounts History:Extended Price}
ELSE 0
END


Celko Answers
>> Can anyone tell me what is wrong with the following syntax? <<


well, you made it up :)

The CASE expression is an *expression* and not a control statement;
that is, it returns a value of one datatype. SQL-92 stole the idea and
the syntax from the ADA programming language. Here is the BNF for a
:


::= |


::=
CASE
...
[]
END


::=
CASE
...
[]
END


::= WHEN THEN


::= WHEN THEN


::= ELSE


::=


::=


::= | NULL


::=


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 ) expression. I recommend always giving the ELSE
clause, so that you can change it later when you find something
explicit to return.


The is defined as a searched CASE expression
in which all the WHEN clauses are made into equality comparisons
against the . 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


The second WHEN clause is always UNKNOWN.


The SQL-92 Standard defines other functions in terms of the CASE
expression, which makes the language a bit more compact and easier to
implement. For example, the COALESCE () function can be defined for
one or two expressions by


1) COALESCE () is equivalent to ()


2) COALESCE (, ) is equivalent to


CASE WHEN IS NOT NULL
THEN
ELSE END


then we can recursively define it for (n) expressions, where (n >= 3),
in the list by


COALESCE (, , . . ., n), as equivalent to:


CASE WHEN IS NOT NULL
THEN
ELSE COALESCE (, . . ., n)
END


Likewise, NULLIF (, ) is equivalent to:


CASE WHEN =
THEN NULL
ELSE END


It is important to be sure that you have a THEN or ELSE clause with a
datatype that the compiler can find to determine the highest datatype
for the expression.


A trick in the WHERE clause is use it for a complex predicate with
material implications.


WHERE CASE
WHEN
THEN 1
WHEN
THEN 1
...
ELSE 0 END = 1