Using Expressions - Firebird

Computed Columns

A useful feature of SQL is its ability to generate runtime output fields using expressions. Firebird supports two kinds of computed output: fields that are created in DML statements and columns that are pre-defined by DDL in tables using the COMPUTED BY keywords and a contextual expression. Usually, such fields are derived from stored data, although they need not be. They can be constants or, more commonly, context variables or values derived from context variables.

Fields Created in Statements

When an output column is created using an expression, it is known as a computed output column. The output value is always read-only, because it is not a stored value. Any expression that performs a comparison, computation, or evaluation and returns a single value as a result can be involved in specifying such a column.

It often happens that an expression uses more than one “formula” to get the result. In our earlier example, both a function (EXTRACT( )) and an operation (concatenation) were used to derive the BIRTHDAY string that was output in the list. Such complex expressions are not unusual at all.

Derived Fields and Column Aliasing

Expressions, subquery output, and constants can be used in Firebird to return derived or “made up” fields in output sets. To enable you to provide runtime names for such fields, Firebird supports the SQL column-aliasing standard, which allows any column to be output using an alias of one or more characters.

For example, the following:

SELECT COL_ID, COLA||','||COLB AS comput_col FROM TABLEA;

returns a column concatenating two column values separated by a comma.

When two columns are concatenated in this fashion in any expression, the output field will be NULL if either of the columns is NULL. This, too, is standard SQL behavior.

In the next example, a scalar subquery on another table is used to create a runtime output field:

A scalar (sub)query is one that returns the value of a single column from a singlerow. Subqueries are discussed Chapter Querying Multiple Tables.

Firebird permits the standard to be relaxed slightly in respect to the AS keyword—it is optional. Omitting it is not recommended, since it can make aliased column names harder to find in source code.

Another part of the standard requires columns that are computed or subqueried in the statement to be explicitly named with an alias. Current versions of the Firebird engine let you omit the alias name of computed or subqueried columns altogether. For example, the following query:

SELECT CAST(CURRENT_DATE as VARCHAR(10))||'-'||REGISTRATION FROM AIRCRAFT;

generates the following output:

Some regard this as a bug, others as a feature. It is convenient for a DBA to make a quick query without bothering with the detail. It is definitely not recommended as a “feature” to exploit in applications. Output columns with no name too often cause bugs and ambiguities in client interfaces. The same difficulties can arise with application interfaces if you use the same alias more than once in a single output.

Constants and Variables As Runtime Output

It is possible to “create” an output column in a SELECT statement using an expression that involves no columns but is just a constant or a context variable and an alias. In this trivial example, the query adds a column to every row, carrying the constant value 'This is just a demo':

Trivial as it appears in this example, it can be a handy way to customize output, especially if used with a CASE expression.

Using Context Variables

Firebird provides a range of variables supplying server snapshot values. As we have already seen, many of these values can be used in expressions that perform calculations. They can also be used in expressions that store them into database columns, either as “hard” values or in COMPUTED BY specifications (see the section “COMPUTED BY Column Definitions”).

Context variables can also be used in an output column expression to return a server value directly to the client or to a PSQL module. To illustrate

Refer to the section “Context Variables” in Chapter About Firebird Data Types for a detailed list of available variables and more examples of use.

Expressions Using CASE( ) and Friends

Firebird provides the SQL-99 standard CASE expression syntax and its two “shorthand” derivative functions, COALESCE( ) and NULLIF( ). A CASE( ) expression can be used to output a constant value that is determined conditionally at runtime according to the value of a specified column in the current row of the queried table.

CASE( )

The CASE( ) function allows the output for a column to be determined by the outcome of evaluating a group of mutually exclusive conditions.

Availability

DSQL, PSQL, ISQL, ESQL, Firebird 1.5 and higher versions. Any platform.

Syntax

Additional Keywords

WHEN ... THEN are keywords in each condition/result clause. At least one condition/result clause is required.

ELSE precedes an optional “last resort” result, to be returned if none of the conditions in the preceding clauses is met.

Arguments

value 1 is the identifier of the column value that is to be evaluated. It can be omitted, in which case each WHEN clause must be a search predicate containing this column identifier.

value 2 is the match part for the search condition: a simple constant or an expression that evaluates to data type that is compatible with the column’s data type.

  • If the column identifier (value 1) is named in the CASE clause, value 2 stands alone in each WHEN clause and will be compared with value 1
  • If the column identifier is not named in the CASE clause, both value 1 and value 2 are included in each WHEN clause as a search predicate of the form (value1=value 2).

result 1 is the result that will be returned in the event that value 1 matches value 2.

result 2 is the result that will be returned in the event that value 1 matches value 3 and so on.

Return Value

The CASE clause returns a single result. If no condition is met and no ELSE clause is specified, the result returned will be NULL.

You should use one form of the syntax or the other. Mixed syntax is not valid. Using a single condition/result clause makes sense only if there is an ELSE clause. However, it is less elegant than the related functions COALESCE( ) and NULLIF( ).

Examples

The following two examples demonstrate how each of the two syntaxes could be used to operate on the same set of predicates.

This is the simple syntax:

This syntax uses search predicates in the WHEN clause:

Related or Similar Functions

Firebird 1.0.x users, see the external functions INULLIF( ) and NVL( ).

COALESCE( )

The COALESCE( ) function allows a column value to be calculated from a series of expressions, from which the first expression to return a non-null value is returned as the output value.

Availability

DSQL, PSQL, ISQL, ESQL, Firebird 1.5 and higher versions. Any platform.

Syntax

Arguments

value 1 is the primary column value or expression that is evaluated. If it is not NULL, it is the value returned.

value 2 is the column value or expression that is evaluated if value 1 resolves to NULL.

value n is the final value or expression that is evaluated if the preceding value in the list resolves to NULL.

Return Value

It returns the first non-null result from the list.

Notes

COALESCE( ) can be used to evaluate a pair of conditions, or a list of three or more conditions.

In the first (simple, binary) syntax COALESCE(value1, value2), the evaluation logic is equivalent to

CASE WHEN V1 IS NOT NULL THEN V1 ELSE V2 END

When there are three or more arguments—COALESCE(value1, value2, ...valuen)—the evaluation logic is equivalent to

The last value in the list should be specified so as to ensure that something is returned.

Example

In the first query, if the join fails to find a match in the Employee table for the TEAM_LEADER in the Project table, the query will return the string "[Not assigned"] in place of the NULL that the outer join would otherwise return as the value for FULL_NAME:

In the next query, evaluation starts at the leftmost position in the list. If no PHONE value is present, the query looks to see whether a MOBILEPHONE value is present. If so, it returns this in PHONENUMBER; otherwise, as a last resort, it returns the string "Unknown":

Related or Similar Functions

Firebird 1.0.x users, see the external functions INVL( ) and SNVL( ).

NULLIF( )

NULLIF( )substitutes NULL for a value if the value resolves to a supplied non-null value; otherwise, it returns the value of the sub-expression.

Availability

DSQL, PSQL, ISQL, ESQL, Firebird 1.5 and higher versions. Any platform.

Syntax

NULLIF (value 1, value 2)

Arguments

value 1 is the column or expression that is to be evaluated.

value 2 is a constant or expression with which value 1 is to be compared. If there is a match, the NULLIF expression will return NULL.

Return Value

The return value will be NULL if value 1 and value 2 resolve to a match. If there is no match, then value 1 is returned.

NULLIF( ) is a shorthand for the CASE expression

CASE WHEN (value_1 = value_2) THEN NULL ELSE value_1 END

Example

This statement will cause the STOCK value in the PRODUCTS table to be set to NULL on all rows where it is currently stored as 0:

UPDATE PRODUCTS SET STOCK = NULLIF(STOCK, 0)

Related or Similar Functions

Firebird 1.0.x users, see INULLIF( ) and SNULLIF( ).

COMPUTED BY Column Definitions

In table specifications, you can create columns, known as computed columns , that store no “hard” values, but instead store an expression that computes a value whenever the column is referred to by a query. The expression defining the column usually involves the values of one or more other columns in the current row or a server context variable. The following is a simple illustration:

It is also possible to use a subquery expression to obtain the runtime value of such a column, a feature that needs to be designed with care to avoid undesirable dependencies. For more information about computed columns, see Chapter Tables.

Search Conditions

The ability to construct “formulae” to specify search conditions for selecting sets, locating rows for updates and deletes, and applying rules to input is a fundamental characteristic of a query language. Expressions pervade SQL because they provide the algebra for inserting context into abstract stored data and delivering it as information. Expressions also play an important role in stripping context from input data.

WHERE Clauses

A WHERE clause in a statement sets the conditions for choosing rows for an output set or for targeting rows for searched operations (UPDATE, DELETE). Almost any column in a table can be targeted for a search expression in a WHERE clause. If a data item can be operated on by SQL, it can be tested. Recall that a search predicate is an assertion. Simple or complex assertions are the formulae we construct to specify the conditions that must be true for every row in the set that the main clause of our query is to operate on.

Reversal of Operands

The simplified syntax of predicates for the WHERE clause specifies only that it take the form WHERE value operator value In other words, according to syntax, both of these assertions are valid:

For predicate types involving the equivalence symbol operators (= and <>) and for a few others, such as LIKE, the SQL parser understands both ways and treats them as equivalent. Other operators will throw exceptions or undetected errors if the left and right sides of a predicate are reversed.

In the case of the “reversible” types, the placement of the operands in predicates is a question of style. However, in the author’s experience, the readabilityof complex SQL predicates in client code and PSQL sources is inversely proportional to the number of predicates presented with the tested value placed to the right of the operator, as in the second example.

For example, a nest of predicates of the form

<expression-or-constant> = COLUMNX

makes hard labor out of troubleshooting and code review, compared with

COLUMNX = <expression-or-constant>

It is worth any perceived extra “effort” to make the structures of all expressions consistent.

Arrays, BLOBs, and Strings

An ARRAY type cannot be used in a search predicate at all, because SQL has no way to access the data stored in arrays. Expressions targeting BLOB columns are quite limited. A BLOB cannot be compared for equivalence with another BLOB, nor with any other data type. A text BLOB can be targeted using STARTING WITH and, with limitations, the LIKE and CONTAINING predicators.

Strings can be tested with any comparison operator, although some, such as >, <, >=, and <=, are not often useful.

Ordering and Grouping Conditions

When an output field is created from an expression, it can be used for setting the conditions for ordering or grouping the set. However, the syntax rules for expressions in ORDER BY and GROUP BY are different, and there are differences between versions 1.0.x and 1.5.

ORDER BY with Expressions

A field created using an expression at runtime cannot be referred to as a condition in an ORDER BY clause by its alias. It can be used by referring to its degree in the set—that is, its position across the row, counting 1 as the position of the leftmost output field.

For example, the following:

produces a list that is ordered by the concatenated field.

In v.1.5 and later, in sets other than UNIONs, the ordering condition can be an expression that returns a value that can be meaningfully ordered. For example, the ORDER BY clause in the following statement calls a function that returns the length of a description column and uses it to order the output from longest to shortest:

GROUP BY with Expressions

In SQL, a GROUP BY statement is used to collect sets of data and organize or summarize them hierarchically.

In all versions of Firebird, you can form a hierarchical grouping based on a call to an external function (UDF), but in v.1.0.x you cannot use the degree number of an output column as a grouping condition. Firebird 1.5 added the capability to GROUP BY degree and also by certain other types of expressions, including subquery expressions.

Other changes in Firebird 1.5 tightened some grouping rules, removing support for certain illegal grouping syntaxes that are permitted in Firebird 1.0.x.

The rules and interactions of grouping and ordering by expression are sometimes complicated to implement. The topics are covered in detail in Chapter Ordered and Aggregated Sets.

The following v.1.5 example queries the Membership table and outputs a statistic showing how many members joined in each month, by month:

CHECK Expressions in DDL

The use of expressions is not restricted to DML, as we have already seen from their use in table definitions for computed columns. Any time you define a CHECK constraint for a table, column, or domain, you will use expressions. By nature, a CHECK constraint performs a check on one or more values; that is, it tests a predicate. Here is an example where the member’s cell phone number is checked to make sure that, if it is present, it begins with a zero:

Expressions in PSQL

PSQL, the procedure language for triggers and stored procedures, makes extensive use of expressions for flow control. PSQL provides IF (<predicate>) THEN and WHILE(<predicate>) DO structures. Any predicate that can be tested as a search condition can also be a predicate for program flow condition.

An important function of triggers is to test incoming new values, using expressions, and to use other expressions to transform or create values in the current row or in rows in related tables. For example, this common trigger checks whether a value is null and, if so, calls a function to create a value for it:

For detailed information about writing triggers and procedures, refer to Part Seven.


All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Firebird Topics