(DTERP-2302) Supported Standard Lookups

Owned by SNP
Feb 10, 2023
5 min read

Table of Contents:

Supported Lookups

Example of a supported SELECT statement

SELECT */list_of_fields  (aggregation_functions-SUM, MIN, MAX) (AS)

  INTO (CORRESPONDING  FIELDS OF) TABLE result_table

  FROM  table

  (FOR ALL ENTRIES  IN fae_table)

  WHERE  (fae_table_cond AND  ) where_cond-BETWEEN,OR,IN,().

Example of a supported WHERE condition

manager  = fae_table-manager  AND  (city = ‘FRANKFURT’  OR city = ‘BERLIN’)

Example of a not supported WHERE condition 

manager  = fae_table-manager  OR manager  = ‘PETER’ AND  (city = ‘FRANKFURT’  OR  (country = ‘SVK’  AND  date = ‘01012014’)  )

Not supported Lookups

1. SELECT - ENDSELECT

  • Selects data and stores the result into a structure or variables

  • Contains processing executed for each row

Recommended solution
  1. Create a result table and use it to store the result from a SELECT
  2. Move the processing to LOOP after a SELECT
  3. After the previous step, you may adjust the SELECT statement for the archive
  4. Once the adjustment is executed, the SELECT can be returned to its original version

Example before

Example after

2. SELECT DISTINCT

  • DISTINCT used in SELECT ensures only unique combinations of field values are selected
  • This SELECT is not supported by Lookup API and archive
Recommended solution
  1. Remove DISTINCT from SELECT
  2. Add sorting and deletion of adjacent duplicates after SELECT statement
  3. After the previous step, you may adjust the SELECT statement for the archive

  4. The SELECT statement can be reverted to its original version.

Example before

Example after

3. SELECT aggregation function INTO variable

  • The result of an aggregation function is stored in a variable.
Recommended solution
  1. Create a table to store the results 
  2. Use the table in the SELECT statement
  3. Read the result table into a previously used variable
  4. After the previous step, you may adjust the SELECT statement for the archive

  5. The SELECT statement can be reverted to its original version.

Example before

Example after

4. SELECT with WHERE containing IN table

  • An internal table is used in WHERE condition IN
  • This SELECT is neither supported by Lookup API nor archive
Recommended solution
  1. To achieve a code that can be adjusted, comment on the part of the condition with IN
  2. You may adjust the SELECT statement for the archive
  3. Once the code is adjusted by Data Tiering, you may uncomment the part of the condition with IN. The SELECT statement is in the original state, as it was before the adjustment
  4. Create a set based on the internal table and import it to Lookup API (see Example after)

  5. Add to the generated code a variable as an exporting parameter (marked red)

Example before

Example after

5. SELECT with dynamic WHERE condition

  • Internal table containing WHERE condition is used in SELECT
  • This SELECT statement isn't supported by Lookup API or by archive
Recommended solution
  1. To get a code that can be adjusted, comment on the part of the condition with the internal table
  2. After the previous step, you may adjust the SELECT statement for the archive
  3. Return SELECT statement to its original version
  4. Loop through an internal table, add its content to WHERE condition used in Lookup API

Example before

Example after

6. SELECT SINGLE

  • Selects the first row matching the selection and stores it into a structure – not supported by API
  • API requires a table for results, all rows matching the selection are returned
Recommended solution - Field routine
  1. Adjust the SELECT statement by creating a result table and storing the selected data in it. Remove SINGLE from SELECT.
  2. Move adjusted SELECT statement to Start routine to select the required data per package, not per row.
  3. Use the result table in the field routine to read the required row.
  4. After these changes, only one API call is executed instead of e.g. 50.000 (package size).

Example before

Example after

Recommended solution - Loop
  1. Move adjusted SELECT statement above the Loop to select all data at once
  2. Use the result table in Loop directly to read the required data

Example before

Example after

7. SELECT with ORDER BY:

  • During data selection, both active and archived data is read.
  • Sorting data from different sources is not supported by Lookup API.
Recommended solution

To guarantee that result is properly sorted, separate the data sorting logic from the SELECT.

Example before

Example after

8. JOIN

  • Join combines data of multiple tables – this is not supported by Lookup API 
  • Without knowing the business logic behind it, it’s complicated to understand which data is selected
Recommended solution
  • To adjust such a SELECT, the select must be separated into single SELECTs, each selecting data from one table
  • More manual work is required since JOIN logic needs to be simulated after SELECTs execution
  • Straightforward separation of the SELECT and the simulation of the JOIN condition doesn’t have to be the best solution in terms of performance
  • Knowing the business logic behind is crucial to create a more performance-optimized solution
  • Adjusted SELECT statements can be only then adjusted by archive

9. NATIVE SQL

Recommended solution
  • Create supported Open SQL statement
  • Understanding the business logic is necessary because Native SQL is used for a specific reason (performance, indexes, operations)
  • When you create an Open SQL statement, all these circumstances should be taken into account