Using filters in queries with virtual tables





In order to simplify coding typical queries, the platform provides a set of virtual tables that can be treated as parametrized queries. The query language provides two filter definition methods: in the WHERE query statement and using a virtual table parameter. This section covers the differences between the two methods.

Let us consider two queries to the same accumulation register that select the current balance of a specified product. Suppose the register has the following structure:

  • Dimension: Item
  • Resource: Quantity

1. A query with a filter specified using a query language statement looks like this:

SELECT 
    QuantityBalance
FROM
    AccumulationRegister.Inventory.Balance()
WHERE
    Item = &Item

2. A query with a filter specified using a virtual table parameter looks like this:

SELECT
    QuantityBalance
FROM
    AccumulationRegister.Inventory.Balance(, Item = &Item)

Both queries produce the same result. But when the first query is executed, balances are calculated for all the products and only then the required balance is selected. The second query transfers the filter condition directly to the virtual table and when the query to the virtual table is executed, only the balance for a single product is acquired. So if a restriction is defined by means of query language, first a complete selection from a virtual table is retrieved and only then a filter is applied. And if a query condition is passed to a virtual table, the resulting selection is limited by that condition. Therefore, we recommend that you pass as many filter conditions as possible in virtual table parameters.

For some virtual tables, the filter method impacts not only performance but query execution logics as well. Let us consider the SliceLast virtual table of an information register on the example of the CurrencyExchangeRates information register, which has the following structure:

  • Dimension: Currency
  • Attribute: Source
  • Resource: ExchangeRate

where the Source attribute can have the following values: "Internet" and "Printed media".

The goal is to get the latest currency exchange rates with the values gathered from the printed media.

1. A query with a filter created using a query language statement looks like this:

SELECT
    Currency, ExchangeRate
FROM
    InformationRegister.CurrencyExchangeRates.SliceLast()
WHERE
    Item = &Item 

2. A query with a filter specified using a virtual table parameter looks like this:

SELECT
    Currency, ExchangeRate
FROM
    InformationRegister.CurrencyExchangeRates.SliceLast(, Source = &Source)

Although these queries only differ by the method used to define the filter, their results are different (unlike the results of queries to the accumulation register). The result of the first query does not include the currencies that do not have their latest exchange rates set by the printed media.

In the second query the latest exchange rates are selected for all the currencies that have had their exchange rates set by the printed media at least once. So if a register stores the following data:

Period

Currency

Exchange rate

Source

03/19/2014

USD

31.00

printed media

03/19/2014

EUR

34.00

printed media

03/20/2014

USD

31.10

Internet

The first query returns:

Currency

Exchange rate

EUR

34.00

The second query returns:

Currency

Exchange rate

USD

31.00

EUR

34.00


Comments
0
Add comment