Set Returning Functions

Applies to: ✅ Data 360 SQL ✅ Tableau Hyper API

This section describes functions that return multiple rows. Set returning functions can be called in the FROM clause of a SQL query:

  • unnest - Expands the elements of an array.
  • external - Reads data stored in an external file format from one or multiple external locations.
  • generate_series (Numerical) - Generates a series of numerical values from start to stop with a given step size.
  • generate_series (Time-based) - Generates a series of date or timestamp values from start to stop with a given interval step size.

In SQL, all row sets are unordered by default. Due to implementation details such as parallelization, rows might be processed and output in any order.

Sometimes, the order of rows generated by a set returning function is important, though. In such cases, the WITH ORDINALITY clause can be used to add an additional output column, assigning a sequential number to the generated rows:

Results:

dayordinality
Mon1
Tue2
Wed3
Thu4
Fri5

The WITH ORDINALITY clause is supported by most, but not all, set returning functions.