IDS Forum

RE: Case Statement within a Select

Posted By: Erasmus, Marius
Date: Thursday, 20 November 2003, at 3:16 a.m.

In Response To: Case Statement within a Select (Clifton M. Bean)

Hi Clifton,

Try this. I got it from the online documentation of 9.21 which is in PDF
format.
The copy & Paste doesn't look very good. Hope this helps

CASE
Use the CASE statement when you need to take one of many branches
depending on the value of an SPL variable or a simple expression. The CASE
statement is a fast alternative to the IF statement.

You can use the CASE statement to create a set of conditional branches
within
an SPL routine.
Both the WHEN and ELSE clauses are optional, but you must supply one or
the other. If you do not specify either a WHEN clause or an ELSE clause, you
receive a syntax error.

How the Database Server Executes a CASE Statement
The database server executes the CASE statement in the following way:
n The database server evaluates the value_expr parameter.
n If the resulting value matches a literal value specified in the
constant_expr parameter of a WHEN clause, the database server
executes the statement block that follows the THEN keyword in that
WHEN clause.
n If the value resulting from the evaluation of the value_expr parameter
matches the constant_expr parameter in more than one WHEN clause,
the database server executes the statement block that follows the
THEN keyword in the first matching WHEN clause in the CASE
statement.
n After the database server executes the statement block that follows
the THEN keyword, it executes the statement that follows the CASE
statement in the SPL routine.
n If the value of the value_expr parameter does not match the literal
value specified in the constant_expr parameter of any WHEN clause,
and if the CASE statement includes an ELSE clause, the database
server executes the statement block that follows the ELSE keyword.
n If the value of the value_expr parameter does not match the literal
value specified in the constant_expr parameter of any WHEN clause,
and if the CASE statement does not include an ELSE clause, the
database server executes the statement that follows the CASE
statement in the SPL routine.
n If the CASE statement includes an ELSE clause but not a WHEN
clause, the database server executes the statement block that follows
the ELSE keyword.
SPL Statements 3-9

Computation of the Value Expression in CASE
The database server computes the value of the value_expr parameter only one
time. It computes this value at the start of execution of the CASE
statement. If
the value expression specified in the value_expr parameter contains SPL
variables and the values of these variables change subsequently in one of
the
statement blocks within the CASE statement, the database server does not
recompute the value of the value_expr parameter. So a change in the value of
any variables contained in the value_expr parameter has no effect on the
branch taken by the CASE statement.
Valid Statements in the Statement Block
The statement block that follows the THEN or ELSE keywords can include any
SQL statement or SPL statement that is allowed in the statement block of an
SPL routine. For further information on the statement block of an SPL
routine,
see "Statement Block" on page 4-298.
Example of CASE Statement
In the following example, the CASE statement initializes one of a set of SPL
variables (named j, k, l, and m) to the value of an SPL variable named x,
depending on the value of another SPL variable named i:
CASE i
WHEN 1 THEN
LET j = x;
WHEN 2 THEN
LET k = x;
WHEN 3 THEN
LET l = x;
WHEN 4 THEN
LET m = x;
ELSE
RAISE EXCEPTION 100; --illegal value
END CASE

-----Original Message-----
From: Clifton M. Bean [mailto:cmbean@sbcglobal.net]
Sent: 20 November 2003 08:58 AM
To: ids@iiug.org
Subject: Case Statement within a Select [2198]

I remember seeing this in either a training manual or a 7.31 manual four =
to five years ago. Did this ability make it into the 9.x product line?

If someone has the syntax for this select option, I would appreciate a =
copy/paste of the syntax and an written documentation on the command. =
(A link to the manual on the IBM Informix Website would be even better!)

Thank you in advance.
Clifton

Messages In This Thread

• Case Statement within a Select
  Clifton M. Bean -- Thursday, 20 November 2003, at 1:58 a.m.
  • RE: Case Statement within a Select
    Erasmus, Marius -- Thursday, 20 November 2003, at 3:16 a.m.
  • Re: Case Statement within a Select
    Mark D. Stock -- Thursday, 20 November 2003, at 4:40 a.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.