Case Statement within a Select

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

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

Clifton M. Bean wrote: > 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? Yes. > 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!) Check the Guide to SQL Syntax manual, page 4-89. Cheers, -- Mark. +----------------------------------------------------------+-----------+ | Mark D. Stock mailto:mdstock@MydasSolutions.com |//////// /| | Mydas Solutions Ltd http://MydasSolutions.com |///// / //| | +-----------------------------------+//// / ///| | |We value your comments, which have |/// / ////| | |been recorded and automatically |// / /////| | |emailed back to us for our records.|/ ////////| +----------------------+-----------------------------------+-----------+