Expressions - Firebird

Storing data in its plainest, most abstract state is what databases do. The retrieval language—in Firebird’s case, this is usually SQL—combines with the database engine to provide an armory of ready-made formulae into which actual data can be substituted at runtime to transform these pieces of abstract data into information that is meaningful to humans.

To take a simple example, a table, Membership, has the columns FIRST_NAME, LAST_NAME, and DATE_OF_BIRTH. To get a list of members’ full names and birthdays, we can use a statement containing expressions to transform the stored data:

SELECT FIRST_NAME ||' '||LAST_NAME AS FULL_NAME, EXTRACT(MONTH FROM DATE_OF_BIRTH)||'/'||EXTRACT (DAY FROM DATE_OF_BIRTH) AS BIRTHDAY FROM MEMBERSHIP WHERE FIRST_NAME IS NOT NULL AND LAST_NAME IS NOT NULL ORDER BY 2;

At the time we send this request to the server, we do not know what the stored values are. However, we know what they should be like (their semantic values and data types), and that is sufficient for us to construct expressions to retrieve a list that is meaningful in the ways we want it to be.

In this single statement we make use of three different kinds of SQL expressions:

  • For the first field, FULL_NAME, the concatenation operator (in SQL, the doublepipe || symbol) is used to construct an expression that joins two database fields into one, separated by a space.
  • For the second field, BIRTHDAY, a function is used to extract first the month and then the day of the month from the date field. In the same expression, the concatenation operator is again used, to tie the extracted numbers together as a birthday, the day and month separated by a slash.
  • For the search condition, the WHERE clause uses another kind of expression, a logical predicate , to test for eligible rows in the table. Rows that failed this test would not be returned in the output.

In the example, expressions were used

  • To transform data for retrieval as an output column.
  • To set search conditions in a WHERE clause for a SELECT statement. The same approach can be used also for searched UPDATE and DELETE statements.

Other contexts in which expressions can be used include

  • To set validation conditions in CHECK constraints
  • To define COMPUTED BY columns in CREATE TABLE and ALTER TABLE definitions
  • To transform or create input data in the process of storing them in a table using INSERT or UPDATE statements
  • To decide the ordering or grouping of output sets
  • To set runtime conditions to determine output
  • To condition the flow of control in PSQL modules

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

Firebird Topics