Contents

Supported Lookups

Example of a supported SELECT statement

SELECT */list_of_fields (aggregation_functions-SUM, MIN, MAX, COUNT) (AS)
INTO (CORRESPONDING FIELDS OF) TABLE result_table
FROM DSO_active_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
This SELECT statement can be adjusted by DataTiering. 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 DataTiering

Recommended solution

Remove DISTINCT from SELECT
Add sorting and deletion of adjacent duplicates after SELECT statement
This statement can be adjusted by DataTiering. Adjusted SELECT 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 previously used variable
This statement can be adjusted by DataTiering. Adjusted SELECT 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 DataTiering

Recommended solution

To achieve a code that can be adjusted, comment the part of the condition with IN
Once the code is adjusted, you may uncomment the changes in the SELECT statement
Create a set based on the internal table and import it to lookup API
SELECT statement is in the original state, as it was before the adjustment
After the adjustment, the generated code must be adjusted

Example before

Example after

5. SELECT with dynamic WHERE condition

Internal table containing WHERE condition is used in SELECT
This select isn't supported by Lookup API or by DataTiering

Recommended solution

To get a code that can be adjusted, comment the part of the condition with the internal table
Adjust the code with DataTiering
Return SELECT statement to its original version
Loop through internal table, add its content to WHERE condition used in Lookup API

Example before

Example after

6. SELECT SINGLE

Selects 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 stored the selected data into it. Remove SINGLE from SELECT.
Move adjusted SELECT statement to Start routine to select 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 by a DTP).

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 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 it is crucial. to create more performance optimized solution
Adjusted SELECT statements can be only then adjusted by DataTiering

9. NATIVE SQL

Recommended solution

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

Browser not supported