COMPUTE()

COMPUTE() is used to "wrap" a computation in 1010data Macro Language as a SQL expression.

Description

COMPUTE() is a column-valued function that can be used anywhere as part of a SQL expression (such as SELECT, WHERE, GROUP BY, or ORDER BY). COMPUTE() is more versatile than MACRO(), which is a table-valued function that wraps a 1010data XML query, and must appear in a FROM or JOIN clause of a SQL SELECT statement.

As with MACRO(), COMPUTE() requires expert knowledge of SQL and a basic understanding of 1010data Macro Language. It is alo important to to understand how SQL types map onto underlying 1010data types. Since SQL type cannot typically be inferred from a 1010data expression, the SQL type must be specified explicitly and must be compatible with the actual underlying data type returned by the expression.

Syntax

COMPUTE([SQL_TYPE], [1010DATA_EXPR])

Attributes

SQL_TYPE
A valid SQL type name.
Currently supported SQL types include:
  • INTEGER
  • BOOLEAN
  • DOUBLE
  • BIGINT
  • VARCHAR
  • TEXT
  • DATE
  • TIME
  • TIMESTAMP
  • INTERVALDTS

INTEGER, BOOLEAN, DATE, TIME, and INTERVALYTM types require an expression that returns an valid integer value (and valid date/time/interval if those types are used). DOUBLE, TIMESTAMP, and INTERVALDTS require an expression that returns an appropriate float value. BIGINT requires an expression that returns a bigint value, and VARCHAR or TEXT require an expression that returns a string value.

1010DATA_EXPR
A literal string containing a 1010data Macro Language expression.
Note: $$ is a PostgreSQL-style dollar quoting syntax. This allows the 1010data expression to be written without needing to escape the single quote character that would otherwise need to be used to delimit the Macro Language code string.

Example

The following SQL statement is valid and returns a SQL date value.

SELECT COMPUTE(DATE,'logindate(-5)') AS foo;

The following SQL statement is also valid, because in 1010data dates are represented as integers.

SELECT COMPUTE(INTEGER,'logindate(-5)') AS foo;
The following SQL statement is not valid, because the expression returns an integer, not a string.
SELECT COMPUTE(TEXT,'logindate(-5)') AS foo;

The following code performs a 1010data expression on a column of data. The code assumes (correctly) that the dist column in the SQL table public.solar corresponds to the dist column in the corresponding 1010data table default.test.solar.

SELECT name,
COMPUTE(DOUBLE,$$ dist*0.62137 $$) AS dist_mi
FROM public.solar
WHERE type='planet'
ORDER BY dist_mi;