FirebirdSQL logo
  • Anglaiskeyboard_arrow_down
 Procedural SQL (PSQL) StatementsAggregate Functions 

GEN_ID()

Increments a sequence (generator) value and returns its new value

Result type

BIGINT — dialect 2 and 3
INTEGER — dialect 1

Syntax
GEN_ID (generator-name, step)
Table 1. GEN_ID Function Parameters
Parameter Description

generator-name

Identifier name of a generator (sequence)

step

An integer expression of the increment

If step equals 0, the function will leave the value of the generator unchanged and return its current value.

The SQL-compliant NEXT VALUE FOR syntax is preferred, except when an increment other than the configured increment of the sequence is needed.

Warning

If the value of the step parameter is less than zero, it will decrease the value of the generator.You should be cautious with such manipulations in the database, as they could compromise data integrity (meaning, subsequent insert statements could fail due to generating of duplicate id values).
Note

In dialect 1, the result type is INTEGER, in dialect 2 and 3 it is BIGINT.

GEN_ID Example

new.rec_id = gen_id(gen_recnum, 1);
See also

NEXT VALUE FOR, CREATE SEQUENCE (GENERATOR)

docnext count = 13

Conditional Functions

COALESCE()

Returns the first non-NULL argument

Result type

Depends on input

Syntax
COALESCE (<exp1>, <exp2> [, <expN> ... ])
Table 1. COALESCE Function Parameters
Parameter Description

exp1, exp2 …​ expN

A list of expressions of compatible types

The COALESCE function takes two or more arguments and returns the value of the first non-NULL argument.If all the arguments evaluate to NULL, the result is NULL.

COALESCE Examples

This example picks the Nickname from the Persons table.If it happens to be NULL, it goes on to FirstName.If that too is NULL, “'Mr./Mrs.'” is used.Finally, it adds the family name.All in all, it tries to use the available data to compose a full name that is as informal as possible.This scheme only works if absent nicknames and first names are NULL: if one of them is an empty string, COALESCE will happily return that to the caller.That problem can be fixed by using [fblangref50-scalarfuncs-nullif].

select
  coalesce (Nickname, FirstName, 'Mr./Mrs.') || ' ' || LastName
    as FullName
from Persons
See also

[fblangref50-scalarfuncs-iif], [fblangref50-scalarfuncs-nullif], CASE

DECODE()

Shorthand “simple CASE”-equivalent function

Result type

Depends on input

Syntax
DECODE(<testexpr>,
  <expr1>, <result1>
  [<expr2>, <result2> ...]
  [, <defaultresult>])
Table 1. DECODE Function Parameters
Parameter Description

testexpr

An expression of any compatible type that is compared to the expressions expr1, expr2 …​ exprN

expr1, expr2, …​ exprN

Expressions of any compatible types, to which the testexpr expression is compared

result1, result2, …​ resultN

Returned values of any type

defaultresult

The expression to be returned if none of the conditions is met

DECODE is a shorthand for the so-called “simple CASE” construct, in which a given expression is compared to a number of other expressions until a match is found.The result is determined by the value listed after the matching expression.If no match is found, the default result is returned, if present, otherwise NULL is returned.

The equivalent CASE construct:

CASE <testexpr>
  WHEN <expr1> THEN <result1>
  [WHEN <expr2> THEN <result2> ...]
  [ELSE <defaultresult>]
END
Caution

Matching is done with the ‘=’ operator, so if testexpr is NULL, it won’t match any of the exprs, not even those that are NULL.

DECODE Examples

select name,
  age,
  decode(upper(sex),
         'M', 'Male',
         'F', 'Female',
         'Unknown'),
  religion
from people
See also

CASE, Simple CASE

IIF()

Ternary conditional function

Result type

Depends on input

Syntax
IIF (<condition>, ResultT, ResultF)
Table 1. IIF Function Parameters
Parameter Description

condition

A true|false expression

resultT

The value returned if the condition is true

resultF

The value returned if the condition is false

IIF takes three arguments.If the first evaluates to true, the second argument is returned;otherwise the third is returned.

IIF could be likened to the ternary “<condition> ? resultT : resultF” operator in C-like languages.

Note

IIF(<condition>, resultT, resultF) is a shorthand for “CASE WHEN <condition> THEN resultT ELSE resultF END”.

IIF Examples

select iif( sex = 'M', 'Sir', 'Madam' ) from Customers
See also

CASE, [fblangref50-scalarfuncs-decode]

MAXVALUE()

Returns the maximum value of its arguments

Result type

Varies according to input — result will be of the same data type as the first expression in the list (expr1).

Syntax
MAXVALUE (<expr1> [, ... , <exprN> ])
Table 1. MAXVALUE Function Parameters
Parameter Description

expr1 …​ exprN

List of expressions of compatible types

Returns the maximum value from a list of numerical, string, or date/time expressions.This function fully supports text BLOBs of any length and character set.

If one or more expressions resolve to NULL, MAXVALUE returns NULL.This behaviour differs from the aggregate function MAX.

MAXVALUE Examples

SELECT MAXVALUE(PRICE_1, PRICE_2) AS PRICE
  FROM PRICELIST
See also

[fblangref50-scalarfuncs-minvalue]

MINVALUE()

Returns the minimum value of its arguments

Result type

Varies according to input — result will be of the same data type as the first expression in the list (expr1).

Syntax
MINVALUE (<expr1> [, ... , <exprN> ])
Table 1. MINVALUE Function Parameters
Parameter Description

expr1 …​ exprN

List of expressions of compatible types

Returns the minimum value from a list of numerical, string, or date/time expressions.This function fully supports text BLOBs of any length and character set.

If one or more expressions resolve to NULL, MINVALUE returns NULL.This behaviour differs from the aggregate function MIN.

MINVALUE Examples

SELECT MINVALUE(PRICE_1, PRICE_2) AS PRICE
  FROM PRICELIST
See also

[fblangref50-scalarfuncs-maxvalue]

NULLIF()

Conditional NULL function

Result type

Depends on input

Syntax
NULLIF (<exp1>, <exp2>)
Table 1. NULLIF Function Parameters
Parameter Description

exp1

An expression

exp2

Another expression of a data type compatible with exp1

NULLIF returns the value of the first argument, unless it is equal to the second.In that case, NULL is returned.

NULLIF Example

select avg( nullif(Weight, -1) ) from FatPeople

This will return the average weight of the persons listed in FatPeople, excluding those having a weight of -1, since AVG skips NULL data.Presumably, -1 indicates “weight unknown” in this table.A plain AVG(Weight) would include the -1 weights, thus skewing the result.

See also

[fblangref50-scalarfuncs-coalesce], [fblangref50-scalarfuncs-decode], [fblangref50-scalarfuncs-iif], CASE