Edit

ALLITEMSQUERY ER function

  • Article

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...

Additional resources

List functions