Databases - Delphi/Firebird - Computed Values in Firebird Queries

Computed Values in Firebird Queries

Most sql dialects allow calculated values and function calls in the select statements. In order to use the results in a program, an as clause is used to provide an identifier (the word "as" is optional).

select [some computed value] as ID1 from "MyTable"

String Concatenation | Function Call

String Concatenation

Somtimes, it is useful for a select query to return a single value containing data from several fields - when one string value is added to the end of another, the correct term is "concatenation".

The concatenation operator is language dependent - in Firebird SQL, it is 2 verticle bars.

select "field1" || '  ' || "field2" as r1 from "MyTable"

A problem with this occurs when one of the fields returns a null value - in the case, the result of the concatenation is also null.

This can be "fixed" by using the coalesce function.

To actually use the result in a program, it is important to use the as clause (the word "as" is optional).

select coalesce("field1", 'unknown') || '  ' || coalesce("field2", 'unknown') as r1 from "MyTable"

The following is an example from working Delhpi code - notice that the single quote is the Delphi string delimiter and, therefore, it is doubled to include it in the actual sql string.

s :=
   'SELECT coalesce("Port_Barcode", ''na'')'
 + '  || ''  '' || coalesce("Sample_Holder_Barcode", ''na'')'
 + '  || ''  '' || coalesce("Filter_Holder_Barcode", ''na'') as Config,'
 + '       "ID" '
 + '  FROM "Port_Configurations_tbl" '
 + ' WHERE "Experiment_ID" = ''' + Experiment_ID + ''''
;

Function Call

This is an example function call.

select GEN_ID ( <GeneratorName>,0 ) from RDB$DATABASE

In this case

RDB$DATABASE always returns only a single row
GEN_ID calls the generator function
The returned value is referenced by the name of the function

See this for more information on Firebird generators

Author: Robert Clemenzi - clemenzi@cpcug.org

Computed Values in Firebird Queries

String Concatenation

Function Call

URL: http:// cpcug.org / user / clemenzi / technical / Databases / Delphi / Firebird / Computed_Values.html