ALLITEMSQUERY ER function
The ALLITEMSQUERY function runs as a joined SQL query. It returns a new flattened Record list value that consists of a list of records that represent all items that match the specified path.
Syntax
ALLITEMSQUERY (path)
Arguments
path: Record list
The valid path of a data source of the Record list data type. It must contain at least one relation.
Return values
Record list
The resulting list of records.
Usage notes
The specified path must be defined as a valid data source path of a data source element of the Record list data type. It must also contain at least one relation. Data elements such as the path String and Date should raise an error in the Electronic reporting (ER) expression builder at design time.
When this function is applied to data sources of the Record list data type that refer to an application object that can be directly called by using SQL (for example, an table, entity, or query), it runs as a joined SQL query. Otherwise, it runs in memory as the ALLITEMS function.
Example
You define the following data sources in your model mapping:
- A CustInv data source of the Table records type that refers to the CustInvoiceTable table
- A FilteredInv data source of the Calculated field type that contains the expression
FILTER (CustInv, CustInv.InvoiceAccount = "US-001") - A JourLines of the Calculated field type that contains the expression
ALLITEMSQUERY ( FilteredInv.'<Relations'.CustInvoiceJour.'<Relations'.CustInvoiceTrans)
When you run the model mapping to call the JourLines data source, the following SQL statement is run:
SELECT ... FROM CUSTINVOICETABLE T1 CROSS JOIN CUSTINVOICEJOUR T2 CROSS JOIN
CUSTINVOICETRANS T3 WHERE...