SQL Table Functions
SQL Table Functions are built-in table functions that can be used in SQL statements where other table expressions, such as a table or a subquery, are expected.
The result set of a table function is a temporary table that exists during SQL statement execution.
This table can be queried further, joined with other tables, or used in any other way that a regular table can be used.
Table functions resemble the user-defined table functions that are created using the CREATE TABLE statement.
VALUESBETWEEN
VALUESBETWEEN generates a table with a single column called VAL containing all integer values from lower_bound to upper_bound, including the lower_bound and the upper_bound.
This function can be used for generating test data.
Example
SELECT
val AS id,
MOD(val * 89, 50) AS intcol,
DATE '2020-01-01' + MOD(val * 71, 365) AS datecol,
CASE MOD(val, 3)
WHEN 0 THEN 'abc'
WHEN 1 THEN 'defghi'
ELSE NULL
END AS strcol
FROM
valuesbetween(1, 10);ID INTCOL DATECOL STRCOL
--- ------- ---------- -------
1 39 2020-03-12 defghi
2 28 2020-05-22 <null>
3 17 2020-08-01 abc
4 6 2020-10-11 defghi
5 45 2020-12-21 <null>
6 34 2020-03-02 abc
7 23 2020-05-12 defghi
8 12 2020-07-22 <null>
9 1 2020-10-01 abc
10 40 2020-12-11 defghiDATAAFFINITYMAPPING
DATAAFFINITYMAPPING returns a table with a mapping of PARTITION to NODEID for any table.
This provides information about the NODEIDs where each partition will be processed if the table is processed as a distributed query.