<< Reserved words and keywords | FB 2.0 Language Reference | Data types and subtypes >>

Miscellaneous language elements

-- (single-line comment)

Available in: DSQL, PSQL

Added in: 1.0

Changed in: 1.5

Description

A line starting with "--" (two dashes) is a comment and will be ignored. This also makes it easy to quickly comment out a line of SQL.

In Firebird 1.5 and up, the "--" can be placed anywhere on the line, e.g. after an SQL statement. Everything from the double dash to the end of the line will be ignored.

Example

 -- a table to store our valued customers in:
 create table Customers (
    name varchar(32),
    added_by varchar(24),
    custno varchar(8),
    purchases integer -- number of purchases
 )

Notice that the second comment is only allowed in Firebird 1.5 and up.

back to top of page

Shorthand casts

Available in: DSQL, ESQL, PSQL

Added in: IB

Description

When converting a string literal to a DATE, TIME or TIMESTAMP, Firebird allows the use of a shorthand "C-style" cast. This feature already existed in InterBase 6, but was never properly documented.

Syntax

 datatype 'date/timestring'

Examples

 update People set AgeCat = 'Old'
   where BirthDate < date '1-Jan-1943'

 insert into Appointments
   (Employee_Id, Client_Id, App_date, App_time)

 values
   (973, 8804, date 'today' + 2, time '16:00')

 new.lastmod = timestamp 'now';

See also:
CAST

back to top of page

CASE construct

Available in: DSQL, PSQL

Added in: 1.5

Description

A CASE construct returns exactly one value from a number of possibilities. There are two syntactic variants:

• The simple CASE, comparable to a Pascal case or a C switch.
• The searched CASE, which works like a series of if ... else if ... else if clauses.

back to top of page

Simple CASE

Syntax

 CASE <expression>
    WHEN <exp1> THEN result1
    WHEN <exp2> THEN result2
    ...
    [ELSE defaultresult]
 END

When this variant is used, <expression> is compared to <exp1>, <exp2> etc., until a match is found, upon which the corresponding result is returned. If there is no match and there is an ELSE clause, defaultresult is returned. If there is no match and no ELSE clause, NULL is returned.

The match is determined with the "=" operator, so if <expression> is NULL, it won't match any of the <expN>s, not even those that are NULL.

The results don't have to be literal values: they may also be field or variable names, compound expressions, or NULL literals.

Example

 select name,
        age,
        case upper(sex)
           when 'M' then 'Male'
           when 'F' then 'Female'
           else 'Unknown'
        end,
        religion
 from people

back to top of page

Searched CASE

Syntax

 CASE
    WHEN <bool_exp1> THEN result1
    WHEN <bool_exp2> THEN result2
    ...
   [ELSE defaultresult]
 END

Here, the <bool_expN>s are tests that give a ternary Boolean result: true, false, or NULL. The first expression evaluating to TRUE determines the result. If no expression is TRUE and there is an ELSE clause, defaultresult is returned. If no expression is TRUE and there is no ELSE clause, NULL is returned.

As with the simple CASE, the results don't have to be literal values: they may also be field or variable names, compound expressions, or NULL literals.

Example

 CanVote = case
              when Age >= 18 then 'Yes'
              when Age < 18 then 'No'
              else 'Unsure'
           end;

See also:
Firebird Null Guide: Conditional statements and loops

back to top of page
<< Reserved words and keywords | FB 2.0 Language Reference | Data types and subtypes >>