Hi,

> There are a few enhancements to the SQL that 1.5 can cope with. Are these
> explained somewhere? I'd like to be able to use the new features. E.g.

CASE in

> the SELECT statement, but I'm not sure of the syntax. Is there a reference
> on-line? And a list of the differences?

The new features COALESCE, NULLIF and CASE are those as described in a
SQL-200n reference. There are two ways to use CASE (searched case and simple
case), below is the explanation :

------------------------------------------------------
Function:
Allow the result of a column to be determined by a the results of a
case expression.

Format:
<case expression> ::=
<case abbreviation>
| <case specification>

<case abbreviation> ::=
NULLIF <left paren> <value expression> <comma> <value expression>
<right paren>
| COALESCE <left paren> <value expression> { <comma> <value
expression> }... <right paren>

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

<simple case> ::=
CASE <value expression>
<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>

<when operand> ::= <value expression>

<else clause> ::= ELSE <result>

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

<result expression> ::= <value expression>


Syntax Rules:


Examples:

A) (simple)
SELECT
o.ID,
o.Description,
CASE o.Status
WHEN 1 THEN 'confirmed'
WHEN 2 THEN 'in production'
WHEN 3 THEN 'ready'
WHEN 4 THEN 'shipped'
ELSE 'unknown status ''' || o.Status || ''''
END
FROM
Orders o

B) (searched)
SELECT
o.ID,
o.Description,
CASE
WHEN (o.Status IS NULL) THEN 'new'
WHEN (o.Status = 1) THEN 'confirmed'
WHEN (o.Status = 3) THEN 'in production'
WHEN (o.Status = 4) THEN 'ready'
WHEN (o.Status = 5) THEN 'shipped'
ELSE 'unknown status ''' || o.Status || ''''
END
FROM
Orders o
------------------------------------------------------


Regards,
Arno Brinkman
ABVisie

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Firebird links :
http://www.firebirdsql.com
http://www.firebirdsql.info
http://www.fingerbird.de/
http://www.comunidade-firebird.org/


Nederlandse firebird nieuwsgroep :
news://80.126.130.81


See you at the First European Firebird Conference May 19-20 in Fulda,
Germany
http://www.firebird-conference.com