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

Assume a UDF called factorial is created in the schema, 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;
/

Assume also the existence of a table in the database with the following values:

I             I2
-----------------  ---------------------

          1          100
          2          200
          3          120

The following query is compiled and executed on the sample table.

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.