Allow the result of a column to be determined by the outcome of a group of exclusive conditions.

Syntax Pattern

  <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>
        

Examples

i) 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;
        

ii) 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;
        

Allows a column value to be calculated by a number of expressions, from which the first expression to return a non-NULL value is returned as the output value.

Syntax Pattern

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

Syntax Rules

Examples

  SELECT
      PROJ_NAME AS Projectname,
      COALESCE(e.FULL_NAME,'[< not assigned >]') AS Employeename
  FROM
    PROJECT p
    LEFT JOIN EMPLOYEE e
    ON (e.EMP_NO = p.TEAM_LEADER);

  SELECT
      COALESCE(Phone,MobilePhone,'Unknown') AS "Phonenumber"
  FROM
    Relations;
        

Returns NULL for a sub-expression if it has a specific value, otherwise returns the value of the sub-expression.

Syntax Pattern

  <case abbreviation> ::=
    NULLIF <left paren> <value expression> <comma> <value expression> <right paren>
        

Syntax Rules

NULLIF (V1, V2) is equivalent to the following <case specification>:

  CASE WHEN V1 = V2 THEN NULL ELSE V1 END
        

Example

  UPDATE PRODUCTS
     SET STOCK = NULLIF(STOCK,0)
        

By default, the engine uses an automatic transaction-level system savepoint to perform transaction rollback. When you issue a ROLLBACK statement, all changes performed in this transaction are backed out via a transaction-level savepoint and the transaction is then committed. This logic reduces the amount of garbage collection caused by rolled back transactions.

When the volume of changes performed under a transaction-level savepoint is getting large (10^4-10^6 records affected) the engine releases the transaction-level savepoint and uses the TIP mechanism to roll back the transaction if needed.

Implementing user savepoints in PSQL layer would break the atomicity rule for statements, including procedure call statements. Firebird provides exception handling in PSQL to undo changes performed in stored procedures and triggers. Each SQL/PSQL statement is executed under a system of automatic, internal savepoints, whereby either the entire statement will complete successfully or ALL its changes are rolled back and an exception is raised.

Each PSQL exception handling block is also bounded by automatic system savepoints.

As the engine considers, in turn, each record falling under an explicit lock statement, it returns either the record version that is the most currently committed, regardless of database state when the statement was submitted, or an exception.

Wait behaviour and conflict reporting depend on the transaction parameters specified in the TPB block:

When an UPDATE statement tries to access a record that is locked by another transaction, it either raises an update conflict exception or waits for the locking transaction to finish, depending on TPB mode. Engine behaviour here is the same as if this record had already been modified by the locking transaction.

No special gdscodes are returned from conflicts involving pessimistic locks.

The engine guarantees that all records returned by an explicit lock statement are actually locked and DO meet the search conditions specified in WHERE clause, as long as the search conditions do not depend on any other tables, via joins, subqueries, etc. It also guarantees that rows not meeting the search conditions will not be locked by the statement. It can NOT guarantee that there are no rows which, though meeting the search conditions, are not locked.

The engine locks rows at fetch time. This has important consequences if you lock several rows at once. Many access methods for Firebird databases default to fetching output in packets of a few hundred rows ("buffered fetches"). Most data access components cannot bring you the rows contained in the last-fetched packet, where an error occurred.

The FOR UPDATE clause provides a technique to prevent usage of buffered fetches, optionally with the OF <column-names> to enable positioned updates.

i) (simple)

  SELECT * FROM DOCUMENT WHERE ID=? WITH LOCK
        

ii) (multiple rows, one-by-one processing with DSQL cursor)

  SELECT * FROM DOCUMENT WHERE PARENT_ID=?
    FOR UPDATE WITH LOCK
        

It is now possible to use an aggregate function or expression contained in the GROUP BY clause inside a subquery.

Examples

  SELECT
    r.RDB$RELATION_NAME,
    MAX(r.RDB$FIELD_POSITION),
    (SELECT
       r2.RDB$FIELD_NAME
     FROM
       RDB$RELATION_FIELDS r2
     WHERE
       r2.RDB$RELATION_NAME = r.RDB$RELATION_NAME and
       r2.RDB$FIELD_POSITION = MAX(r.RDB$FIELD_POSITION))
  FROM
    RDB$RELATION_FIELDS r
  GROUP BY
    1
  /* ************ */
  SELECT
    rf.RDB$RELATION_NAME AS "Relationname",
    (SELECT
       r.RDB$RELATION_ID
     FROM
       RDB$RELATIONS r
     WHERE
       r.RDB$RELATION_NAME = rf.RDB$RELATION_NAME)
    AS "ID",
    COUNT(*) AS "Fields"
  FROM
    RDB$RELATION_FIELDS rf
  GROUP BY
    rf.RDB$RELATION_NAME
        

Aggregate functions from different contexts can be used inside an expression.

Examples

  SELECT
    r.RDB$RELATION_NAME,
    MAX(i.RDB$STATISTICS) AS "Max1",
    (SELECT
       COUNT(*) || ' - ' || MAX(i.RDB$STATISTICS)
     FROM RDB$RELATION_FIELDS rf
    WHERE
      rf.RDB$RELATION_NAME = r.RDB$RELATION_NAME) AS "Max2"
  FROM
    RDB$RELATIONS r
    JOIN RDB$INDICES i on (i.RDB$RELATION_NAME = r.RDB$RELATION_NAME)
  GROUP BY
    r.RDB$RELATION_NAME
  HAVING
    MIN(i.RDB$STATISTICS) <> MAX(i.RDB$STATISTICS)
        

Note! This query gives results in FB1.0, but they are WRONG!

Using a singleton SELECT expression inside an aggregate function is supported.

Example

  SELECT
    r.RDB$RELATION_NAME,
    SUM((SELECT
           COUNT(*)
         FROM
           RDB$RELATION_FIELDS rf
         WHERE
           rf.RDB$RELATION_NAME = r.RDB$RELATION_NAME))
  FROM
    RDB$RELATIONS r
    JOIN RDB$INDICES i
      on (i.RDB$RELATION_NAME = r.RDB$RELATION_NAME)
  GROUP BY
    r.RDB$RELATION_NAME
        

Using an aggregate function inside another aggregate function is possible if the inner aggregate function is from a lower context (see example above).

Using the degree number of the output column in the GROUP BY clause 'copies' the expression from the select list (as does the ORDER BY clause). This means that, when a degree number refers to a subquery, the subquery is executed at least twice.

(from the v.1.0 release notes)

Syntax Pattern

  SELECT [FIRST (<integer expr m>)] [SKIP (<integer expr n>)]
        

Retrieves the first m rows of the selected output set. The optional SKIP clause will cause the first n rows to be discarded and return an output set of m rows starting at n + 1. In the simplest form, m and n are integers but any Firebird expression that evaluates to an integer is valid.

Available in SQL and DSQL except where otherwise indicated.

  SELECT SKIP (5+3*5) * FROM MYTABLE;

  SELECT FIRST (4-2) SKIP ? * FROM MYTABLE;

  SELECT FIRST 5 DISTINCT FIELD FROM MYTABLE;
          

  delete from TAB1
    where PK1 in (select first 10 PK1 from TAB1);
          

  ...
  WHERE F1 IN ( SELECT FIRST 5 F2 FROM TABLE2
    ORDER BY 1 DESC )
          

  ...
  WHERE EXISTS (
     SELECT FIRST 5 TABLE2.F2 FROM TABLE2
     WHERE TABLE2.F2 = TABLE1.F1
     ORDER BY 1 DESC )
          

It is now possible to aggregate a SELECT by grouping on the output of a UDF. e.g.

  select
    strlen(rtrim(rdb$relation_name)),
    count(*) from rdb$relations
  group by strlen(rtrim(rdb$relation_name))
  order by 2
        

A side-effect of the changes enabling grouping by UDFs is that, whereas previously you could not call built-in Firebird functions in GROUP BY, now, by creating a dummy UDF wrapper, you can do:

  select count(*)
  from rdb$relations r
  group by bin_or((select count(rdb$field_name)
     from rdb$relation_fields f
     where f.rdb$relation_name = r.rdb$relation_name),1)
        

Internal function, available in SQL and DSQL, implementing the ANSI SQL SUBSTRING() function. It will return a stream consisting of the byte at <pos> and all subsequent bytes up to the end of the string. If the optional FOR <length> is specified, it will return the lesser of <length> bytes or the number of bytes up to the end of the input stream.

The first argument can be any expression, constant or identifier that evaluates to a string.

<pos> must evaluate to an integer. <pos> in the string starts at 1, like other SQL positional elements.

Neither <pos> nor <length> can be query parameters: they must evaluate to constants.

Because <pos> and <length> are byte positions, the identifier of the input string can be a binary blob, or a sub_type 1 text blob with an underlying one-byte-per-character charset. The function currently does not handle text blobs with Chinese (2 byte/char maximum) or Unicode (3 byte/char maximum) character sets.

For a string argument (as opposed to a blob), the function will tackle ANY charset.

Example

  UPDATE ATABLE
    SET COLUMNB = SUBSTRING(COLUMNB FROM 4 FOR 99)
    WHERE ...
        

Please refer also to the later section on External Functions (UDFs) for details of changes and additions to external substring functions in the standard UDF library.