1.1

Only one DISTINCT aggregate expression per SelectExpression on page 526 is allowed. For example, the
following query is not valid:
SELECT AVG (DISTINCT flying_time), SUM (DISTINCT miles)
FROM Flights
The expression can contain multiple column references or expressions, but it cannot contain another aggregate
or subquery. It must evaluate to an SQL-92 numeric data type. You can therefore call methods that evaluate to
SQL-92 data types. If an expression evaluates to NULL, the aggregate skips that value.
The resulting data type is the same as the expression on which it operates (it will never overflow). The following
query, for example, returns the INTEGER 1, which might not be what you would expect:
SELECT AVG(c1)
FROM (VALUES (1), (1), (1), (1), (2)) AS myTable (c1)
CAST the expression to another data type if you want more precision:
SELECT AVG(CAST (c1 AS DOUBLE PRECISION))
FROM (VALUES (1), (1), (1), (1), (2)) AS myTable (c1)
BIGINT function
The BIGINT function returns a 64-bit integer representation of a number or character string in the form of an
integer constant.
Syntax
BIGINT (CharacterExpression | NumericExpression )
An expression that returns a character string value of length not greater than the maximum
length of a character constant. Leading and trailing blanks are eliminated and the resulting
CharacterExpression
string must conform to the rules for forming an SQL integer constant. The character
string cannot be a long string. If the argument is a CharacterExpression, the result is the
same number that would occur if the corresponding integer constant were assigned to
a big integer column or variable.
An expression that returns a value of any built-in numeric data type. If the argument is
a NumericExpression, the result is the same number that would occur if the argument
NumericExpression
were assigned to a big integer column or variable. If the whole part of the argument is
not within the range of integers, an error occurs. The decimal part of the argument is
truncated if present.
The result of the function is a big integer. If the argument can be null, the result can be null; if the argument is
null, the result is the null value.
Example
Using the EMPLOYEE table, select the EMPNO column in big integer form for further
processing in the application:
SELECT BIGINT (EMPNO) FROM EMPLOYEE
CASE expressions
Use the CASE expressions for conditional expressions in SQLFire.
vFabric SQLFire User's Guide542
vFabric SQLFire Reference