<loop>

<loop> is a special operation that allows one query to iteratively reference the data from each row in the results of another query.

Description

This <loop> construct can also be used to merge, join, and link the results of the iterations into a single table or to concatenate the Macro Language code from each iteration into a single query that is then executed to produce the results. The <loop> construct is designed to facilitate programmatic assignment and retrieval of values from and within 1010data queries. The basic premise is that the <inner> query can access the results of the <outer> query through the variable specified by the with_ attribute (or one of its alternatives).

By default, the final output will only contain the results generated by the last row of the results returned by the <outer> query, however the results of the individual iterations can be merged, joined, or linked together by specifying the desired mode of operation via the mode_ attribute.
Note: Merged, adjoined, and linked tables are constructed as in-memory temporary tables in the 1010data Insights Platform and are thus subject to size limitations.

The query code for each iteration of the <inner> clause can also be concatenated into a single query, which is then executed to produce the results. The contents specified in an optional <preamble> clause can be prepended to the concatenated code before execution.

Syntax

<loop with[rows|cols|table|value]_="[VAR]"
 mode_="last|merge|adjoin|link|concat">
  <outer>
    [1010data_QUERY]
  </outer>
  <inner>
    [1010data_QUERY]
  </inner>
</loop>

Alternate syntax

<loop [VAR]="[STARTING_VALUE]" to_="[TERMINATING_VALUE]" 
 by_="[INCREMENT_INTERVAL]" tally_="[TALLY_VAR]"
 mode_="last|merge|adjoin|link|concat">
  <inner>
    [1010data_QUERY]
  </inner>
</loop>

The alternate syntax can be used when fixed iterations of a query are needed. In this alternate syntax, neither a with_ attribute (or one of its alternatives) nor an <outer> query are specified. The <inner> query is executed the number of times as specified by the given attributes.

The alternate syntax is analogous to the <for> construct. For more information on that construct, see <for>.

Attributes

with_
Accepts an arbitrary variable name. When the <outer> query executes, the first row in the result set is stored as a package value in the variable associated with this attribute. The <inner> query can then access the values contained within that variable. When the <inner> query finishes, the next row of the result set produced by the <outer> query is stored in the variable, and the entire process repeats until all the rows in the result set have all been processed.

The keys of the package are the name of the columns. Values can be accessed using the syntax @[VAR].[COLUMN_NAME].

withrows_
Accepts an arbitrary variable name.

This attribute provides the same functionality as the with_ attribute.

(Available as of version 11.19)

withcols_
Accepts an arbitrary variable name.

After the <outer> query runs, the <inner> query is executed for each column in the result set. The contents of the column are stored as a list value in the variable specified to withcols_ and are available to the <inner> query. The name of the column is stored in the variable associated with the colname_ attribute, if specified.

(Available as of version 11.19)

withtable_
Accepts an arbitrary variable name.

After the <outer> query is executed, the entire result set is placed as a table value in the variable specified by withtable_, and then the <inner> query is executed once.

When the breaks_ attribute is specified, the <inner> query is executed once for each break, with the variable specified by withtable_ set to a table value containing the data for that break.

(Available as of version 11.19)

withvalue_
After the <outer> query is run, the value of the first row of the first column is placed in the variable specified by withvalue_. The <inner> query is executed once.
The attributes row_ and/or col_ may also be used in conjunction with this attribute.
  • If both are specified, a single value is retrieved.
  • If row_ is specified, a single row is retrieved as a package value.
  • If col_ is specified, a single column is retrieved as a list value.

Multiple additional values may be retrieved at the same time using valueN_/rowN_/colN_ syntax, where N is an arbitrary non-empty identifier. See below for descriptions of these attributes.

(Available as of version 11.19)

mode_
<loop> has several different modes in which it may operate to transform data and construct results.

Valid values are:

last
The <inner> query is executed once for every row in the results of the <outer> query, however the final result set contains the data corresponding to the last row of the results of the <outer> query.
merge
The <inner> query is executed once for every row in the results of the <outer> query. The results of each iteration of the <inner> query are merged vertically into a single table.

The results of each iteration of the <inner> query must have the same column names and types for all iterations. However, when pad_="1" is specified, the requirement that column names match is relaxed. Instead, columns where names do not match for a given iteration are padded with N/A values of the appropriate type. (Types returned by each iteration must still match.)

adjoin
The columns resultant from each iteration of the <inner> query are connected horizontally together into a single table. The resulting table will contain a union of all the columns returned by the <inner> query.

The column names returned by each iteration of the <inner> query must be unique, and the results of each iteration must also produce the same number of rows. However, when pad_="1" is specified, the requirement that each iteration must return the same number of rows is relaxed. Instead, each column is padded with N/A values so that its number of rows is equal to the largest number of rows for any one column in the resultant table.

link
The first iteration of the <inner> loop is executed, and each iteration executed afterward will be linked to the results of the first iteration via an exact link on the columns specified by the on_ attribute.

Other columns returned by each iteration must have different names for each iteration, and these names be unique in the new table.

The resulting table, after the last iteration of the <inner> clause executes, contains the linking columns and is a union of all the non-linking columns. Each iteration may return an arbitrary number of rows. The result table will contain one row for each value in the linking columns, with N/As in the other columns for iterations (including the first iteration) where a given value was not present.

concat
The query code for each iteration of the <inner> clause is concatenated into a single query, which is then executed to produce the results.

Each iteration of the <inner> clause corresponds to a row in the results of the <outer> query.

A <preamble> clause may be included in addition to <inner>. The contents of the <preamble> clause are prepended to the concatenated code before execution. For more information, see <preamble>.

The default is last.

pad_
Specifies whether or not to pad merged or adjoined tables.

When mode_="merge" and pad_="1", columns where names do not match for a given iteration are padded with N/A values of the appropriate type.

When mode_="adjoin" and pad_="1", each column is padded with N/A values so that its number of rows is equal to the largest number of rows for any one column in the resultant table.

on_
When mode_="link", this specifies a comma-separated list of column names on which to link the results of each iteration of the inner query to the first iteration. The columns specified in on_ must be present and must have the same names for each iteration.
title_
Specifies a title for the resultant table when mode_ is set to merge, adjoin, or link.
ldesc_
Specifies a long description for the resultant table when mode_ is set to merge, adjoin, or link.
sdesc_
Specifies a short description for the resultant table when mode_ is set to merge, adjoin, or link.
clearcache_
When clearcache_="1", the memory cache for the session is cleared on each iteration before the <inner> query executes.

Use the threshold_ attribute to specify a limit that memory cache must reach before it is cleared.

threshold_
When clearcache_="1", this attribute accepts an integer that specifies the high water mark to reach before clearing the memory cache.

The cache is cleared before the <inner> query executes only if the workspace size exceeds the limit specified in threshold_.

Attributes used with withcols_

The following attributes can be specified when using the withcols_ attribute:

colname_
Accepts an arbitrary variable name. The value of this variable is the name of the column associated with a particular iteration of the <inner> query.

(Available as of version 11.19)

Attributes used with withtable_

The following attributes can be specified when using the withtable_ attribute:

breaks_
Accepts a comma-separated list of column names. When the breaks_ attribute is specified, the <inner> query is executed once for each break, with the variable specified by withtable_ set to a table value containing the data for that break.

(Available as of version 11.19)

Attributes used with withvalue_

The following attributes can be specified when using the withvalue_ attribute:

valueN_
Accepts an arbitrary variable name. The value corresponding to the rowN_ and/or colN_ attributes is stored in the variable associated with the valueN_ attribute. N is a string used as an identifier (e.g., value1_).

More than one value may be stored using multiple valueN_ attributes, where N is a unique identifier for each and must correspond to the naming of the associated rowN_ and/or colN_ attributes (e.g., value1_="[VAR]" row1_="[ROW_NUMBER]" col1_="[COLUMN_NUMBER]" valuefoo_="[VAR]" rowfoo_="[ROW_NUMBER]" colfoo_="[COLUMN_NUMBER]", etc.).

If a value_ attribute is specified, but no corresponding row_ and col_ attributes, the variable associated with the value_ attribute is set to the scalar value in the first row of the first column in the result set of the query (or the empty string if there are no rows in the result set).

This attribute is used in conjunction with the withvalue_ attribute.

(Available as of version 11.19)

rowN_
Specifies a row number within the result set of the query. The value corresponding to the rowN_ and/or colN_ attributes is stored in the variable associated with the valueN_ attribute. N is a string used as an identifier (e.g., row1_).
Together, rowN_ and colN_ specify the location of a scalar value within the result set of a query.
Note: If both rowN_ and colN_ are 0, table metadata is saved as a package to the variable associated with the valueN_ attribute. (Available as of version 11.19)
If rowN_ is provided and colN_ is omitted, all the values in the row will be stored as a package in the variable associated with the valueN_ attribute. The keys of the package are the column names in the result set of the query, and the values can be referenced as @[VAR].[COLUMN_NAME].
Note: If the value of rowN_ is 0, column metadata is saved as a package to the variable associated with the valueN_ attribute. (Available as of version 11.19)

More than one row value may be specified using multiple rowN_ attributes, where N is a unique identifier for each. Note that the naming of a particular rowN_ attribute must correspond to the naming of the associated valueN_ and colN_ attributes (e.g., value1_="[VAR]" row1_="[ROW_NUMBER]" col1_="[COLUMN_NUMBER]" valuefoo_="[VAR]" rowfoo_="[ROW_NUMBER]" colfoo_="[COLUMN_NUMBER]", etc.).

If the value of rowN_ is a negative number, the row that number of rows back from the last row is used. (Available as of version 11.19)

This attribute is used in conjunction with the withvalue_ attribute.

(Available as of version 11.19)

colN_
Specifies either a column name or column number within the result set of the query. The value corresponding to the rowN_ and/or colN_ attributes is stored in the variable associated with the valueN_ attribute. N is a string used as an identifier (e.g., col1_).
Together, rowN_ and colN_ specify the location of a scalar value within the result set of a query.
Note: If both rowN_ and colN_ are 0, table metadata is saved as a package to the variable associated with the valueN_ attribute. (Available as of version 11.19)
If colN_ is provided and rowN_ is omitted, all the values of the column will be stored as a list-value in the variable associated with the valueN_ attribute.
Note: If the value of colN_ is 0, a list of the row numbers is saved as a list-value to the variable associated with the valueN_ attribute. (Available as of version 11.19)

More than one column value may be specified using multiple colN_ attributes, where N is a unique identifier for each. Note that the naming of a particular colN_ attribute must correspond to the naming of the associated valueN_ and rowN_ attributes (e.g., value1_="[VAR]" row1_="[ROW_NUMBER]" col1_="[COLUMN_NUMBER]" valuefoo_="[VAR]" rowfoo_="[ROW_NUMBER]" colfoo_="[COLUMN_NUMBER]", etc.).

If the value of colN_ is a negative number, the column that number of columns back from the last column is used. (Available as of version 11.19)

This attribute is used in conjunction with the withvalue_ attribute.

(Available as of version 11.19)

cntN_
Accepts rows or cols, which specifies whether the number of rows or the number of columns in the result set of the query is stored in the variable associated with the valueN_ attribute.

If a cntN_ attribute is specified, any corresponding rowN_ or colN_ attributes are ignored.

This attribute is used in conjunction with the withvalue_ attribute.

(Available as of version 11.19)

Attributes used with withquery_

The following attributes can be specified when using the withquery_ attribute:

breaks_
Accepts a comma-separated list of column names. When the breaks_ attribute is specified, the <inner> query is executed once for each break, with the variable specified by withquery_ set to a query value containing the data for that break.

The query value is extended on each iteration with the appropriate selections for the corresponding break. The values for the selections are determined initially by extending the <outer> query with a tabulation on the break columns.

(Available as of version 11.19)

Attributes used with the alternate syntax

The following attributes can be specified when using the alternate syntax:

[VAR]
[VAR] is an arbitrary variable name that can be used within the contents of the <inner> loop on each iteration. This variable accepts a numerical value that specifies its initial value.
to_
Accepts a numerical value that specifies the terminating value of the iterated variable. Once [VAR] is greater than this value, the <inner> loop ends.
by_
Accepts a numerical value that specifies the amount to increment the value of [VAR] on each iteration of the <inner> loop.

The default is 1.

tally_
Accepts the name of a variable whose value corresponds to the number of the iteration of the <inner> loop. The value of the variable can be used within the contents of the <inner> loop on each iteration.

The value of the variable is initially set to 1 and is incremented by 1 on each iteration.

Example

In this example, the <outer> loop selects the first row in the Product Master table (pub.demo.retail.prod). The <inner> loop then uses the value in the sku column of the row returned by the <outer> and selects rows in the table pub.demo.retail.item whose sku value is equal to the value of the sku value in the first row of the pub.demo.retail.prod table.

<loop with_="vars">
  <outer>
    <base table="pub.demo.retail.prod"/>
    <sel value="i_=1"/>
  </outer>
  <inner>
    <base table="pub.demo.retail.item"/>
    <sel value="sku='{@vars.sku}'"/>
  </inner>
</loop>

The results of the <loop> shown above are: