![]() |
SQL-J Language Reference
|
Reference Manual |
Built-In FunctionsA built-in function is an expression in which an SQL-J keyword or special operator executes some operation. Cloudscape lets you execute any number of operations by invoking Java methods in the prescribed manner (see Method Invocation). Java method names are case-insensitive. Built-in functions, by contrast, do not use the method invocation syntax, but instead use keywords or special built-in operators. Built-ins are SQL92Identifiers and are thus case-insensitive. Standard Built-In Functions
Some built-in functions use SQL-J keywords, but, because of their relationship to Java, are described in Java Expressions. You can make any static Java method look and act like a built-in function by creating an alias for it. For more information, see CREATE METHOD ALIAS statement. Aggregates (Set Functions)This section describes aggregates (also described as set functions in ANSI SQL-92 and as column functions in some database literature). They provide a means of evaluating an expression over a set of rows. Whereas the other built-in functions operate on a single expression, aggregates operate on a set of values and reduce them to a single scalar value. Built-in aggregates can calculate the minimum, maximum, sum, count, and average of an expression over a set of values as well as count rows. You can also create your own aggregates to perform other set functions such as calculating the standard deviation. The built-in aggregates can operate on the data types shown in Table 1-3. Whether user-defined aggregates are allowed on Java data types depends on how you define them. Aggregates are permitted only in the following:
If an aggregate appears as a SelectItem in a SelectExpression, all expressions in SelectItems in the SelectExpression must be either aggregates or grouped columns (see GROUP BY clause). (The same is true if there is a HAVING clause without a GROUP BY clause.) This is because the ResultSet of a SelectExpression must be either a scalar (single value) or a vector (multiple values), but not a mixture of both. (Aggregates evaluate to a scalar value, and the reference to a column can evaluate to a vector.) For example, the following query mixes scalar and vector values and thus is not valid:
-- not valid Aggregates are not allowed on outer references (correlations). This means that if a subquery contains an aggregate, that aggregate cannot evaluate an expression that includes a reference to a column in the outer query block. For example, the following query is not permitted because SUM operates on a column from the outer query: SELECT c1 A cursor declared on a ResultSet that includes an aggregate in the outer query block is not updatable. |
|
![]() Cloudscape Version 3.6 For information about Cloudscape technical support, go to: www.cloudscape.com/support/.Copyright © 1998, 1999, 2000 Informix Software, Inc. All rights reserved. |