SQL can be used as a numeric calculator.
Following is a list of commonly used functions that can be used in numeric calculations (Click on a function to open the Firebird Reference Manual):
- cast() - CAST converts an expression to the desired datatype or domain. If the conversion is not possible, an error is raised.
- ceiling() - Returns the smallest whole number greater than or equal to the argument.
- floor() - Returns the largest whole number smaller than or equal to the argument.
- iif() - Takes three arguments. If the first evaluates to true, the second argument is returned; otherwise the third is returned.
- maxvalue() - 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.
- minvalue() - 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.
- pi() - Returns an approximation of the value of pi.
- round() - Rounds a number to the nearest integer. If the fractional part is exactly 0.5, rounding is upward for positive numbers and downward for negative numbers. With the optional scale argument, the number can be rounded to powers-of-ten multiples (tens, hundreds, tenths, hundredths, etc.) instead of just integers.
Using Firebird as a Calculator
You do not need to use tables and fields in order to perform calculations in Firebird. You can run a calculation using the select [CALCULATION] from RDB$DATABASE format as in the example below.
If a null value is passed into a calculation, the result will always be null.
The result of a calculation will be of the same data type as the values that were passed into the calculation - eg. 6/4 = 1 and cast(6 as float) / cast(4 as float) = 1.5