SELECT
Syntax
The following text is an abridged form of the SELECT reference documentation. For complete SELECT statement documentation, see the SQL/MX Reference Manual.SELECT [[ANY N ] | [FIRST N ]] [ALL | DISTINCT] select-list
select-list is:
* | select-sublist [,select-sublist]...
select-sublist is:
corr.* | [corr.] single-col [[AS]name] | [col-expr [[AS]name]
Considerations for Select List
-
A col-expr is a single column name or a derived column. A derived column is a SQL value expression; its operands can be numeric, string, datetime, or interval literals, columns, built-in functions (including aggregate functions) defined on columns, user defined functions defined on columns, CASE expressions, or CAST expressions. Any single columns named in col-expr must be from tables or views specified in the FROM clause. Using a UDF within a subquery is not supported.
-
The return value of aggregate built-in functions can also be passed as an argument to a UDF provided that the types are compatible.
-
Passing the return value of a UDF to another UDF or to a built-in function or using the return value of a UDF in an arithmetic expression is not yet supported.
-
If a UDF referenced in a query is altered (for example, dropped and recreated) after the query is compiled, then the query must be recompiled. Recompilation can occur automatically if the AUTOMATIC_RECOMPILATION CQD is 'ON'.
Example Statement
cat.sch
.
SET SCHEMA CAT.SCH;
CREATE FUNCTION factorial(n in INTEGER) RETURN INTEGER
LANGUAGE PLMX
IS
rslt PLS_INTEGER;
loopCount PLS_INTEGER;
begin
rslt := 1;
loopCount := n;
while loopCount > 1 loop
rslt := rslt * loopCount;
loopCount := loopCount - 1;
end loop;
return rslt;
end factorial;
/
I I2
----------------- ---------------------
1 100
2 200
3 120
SELECT factorial(i) from datatable;
Example Output
(EXPR)
-----------------
1
2
6
--- 3 row(s) selected.-
Example Statement
Assume the factorial UDF and the datatable table from the previous example. The following query is compiled and executed:
SELECT i as “i", factorial(i) as “factorial(i)” from datatable;
Example Output
i factorial(i)
---------------- ---------------------
1 1
2 2
3 6
--- 3 row(s) selected.
Example Statement
Assume the factorial UDF and the datatable table from the previous examples. The following query is compiled and executed:
SELECT factorial(max(i)) as "factorial(max(i))" from datatable;
Example Output
factorial(max(i))
--------------------------------
6
--- 1 row(s) selected.