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

Create a result table and use it to store the result from a SELECT
Move the processing to LOOP after a SELECT
After the previous step, you may adjust the SELECT statement for the archive
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

Remove DISTINCT from SELECT
Add sorting and deletion of adjacent duplicates after SELECT statement
After the previous step, you may adjust the SELECT statement for the archive
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

Create a table to store the results
Use the table in the SELECT statement
Read the result table into a previously used variable
After the previous step, you may adjust the SELECT statement for the archive
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

To achieve a code that can be adjusted, comment on the part of the condition with IN
You may adjust the SELECT statement for the archive
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
Create a set based on the internal table and import it to Lookup API (see Example after)
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

To get a code that can be adjusted, comment on the part of the condition with the internal table
After the previous step, you may adjust the SELECT statement for the archive
Return SELECT statement to its original version
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

Adjust the SELECT statement by creating a result table and storing the selected data in it. Remove SINGLE from SELECT.
Move adjusted SELECT statement to Start routine to select the required data per package, not per row.
Use the result table in the field routine to read the required row.
After these changes, only one API call is executed instead of e.g. 50.000 (package size).

Example before

Example after

Recommended solution - Loop

Move adjusted SELECT statement above the Loop to select all data at once
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

Browser not supported