`MACRO()`

MACRO() embeds 1010data Macro Language in SQL statements.

Description

MACRO() is a table-valued function that wraps a 1010data XML query. The Macro Language XML query is used in the same manner as a subquery in the SQL query, as a table-valued expression in a FROM or JOIN clause. When using MACRO(), it is recommended to supply an alias (such as AS a), especially in JOIN expressions.

The 1010data code can be the ultimate source of the data, which is then further processed by the surrounding SQL query. It is also possible to run macro code on the result of a SQL query. To do this, you would use a subselect as the first argument in MACRO(). The second argument is the quoted macro code, followed optionally by a true/false flag to control whether column names or column labels are used when reimporting the data to SQL.

Syntax

MACRO($$ [1010DATA_EXPR] $$, [USE_COLUMN_NAMES])

Attributes

1010DATA_EXPR: A 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.

USE_COLUMN_NAMES: Whether to use column names or column labels in the SQL column result set. The default value is false (use 1010data column labels). 1010data column labels more closely match SQL column names, in that the naming rules are less strict. To use column names, use true as the second parameter in MACRO().

Example

The following examples use MACRO() in a SELECT statement to perform a 1010data Macro Language query.

The following example uses the Macro Language query as the source of the data:

SELECT * FROM MACRO($$ <base table="pub.demo.weather.stations"/>
  <sel value="state='CA'"/>
  <willbe name="city" value="splice(name state;', ')" label="Station City"/>
  $$) As a 
ORDER BY "Station City";

The next example shows how to run Macro Language code on the result of a SQL query. First, the SQL query joins two tables and selects a date range. Next, we run the <retail:basket_affinity> operation on the results of the SQL query. Because the Macro Language code will need to refer to columns from the SQL query, whose underlying 1010data column names will not usually agree with the SQL column names, we use the special block code variable @sqlmap_ to provide a mapping from SQL column names to 1010data column names.

SELECT * FROM MACRO(
   (SELECT "Trans ID" AS transid,
           "Department Desc" AS department,
           "Extended Sales" AS sales
     FROM "retaildemo.retail.sales_detail"
     JOIN "retaildemo.retail.products" USING ("SKU")
     WHERE "Date" BETWEEN DATE '1/1/2018' AND DATE '1/31/2018'),
   $$
   <retail:basket_affinity basket_id="{@sqlmap_.transid}" 
     prod_col1="{@sqlmap_.department}" prod_col2="{@sqlmap_.department}" 
     avgcol="{@sqlmap_.sales}"/>
   $$,
   true);