Introduction
In SAP BW/4HANA, there are 2 types of transformation:
- SAP HANA Runtime
- ABAP Runtime
SAP HANA Runtime allows us to write HANA routines (Start, End, Expert or Field routines) to transform data similar to ABAP runtime where we can write ABAP routines.
Compare to ABAP, HANA routines have different ways to perform lookup. In ABAP we are used to write code to look up on internal table to populate values of fields in SOURCE_PACKAGE or RESULT_PACKAGE. And our code typically would look like:
LOOP AT RESULT_PACKAGE ASSIGNING <RESULT_FIELDS>
READ TABLE lt_data INTO ls_data WITH KEY A = B.
IF sy-subrc = 0.
<result_fields>-Field1 = ls_data-Field1.
ENDIF.
ENDLOOP.
Since HANA routines follow different set of syntax & statements, unlike ABAP there is no READ statement in HANA. To perform similar lookup in HANA we will discuss different ways available.
A simple routine would like as below:
outTab = SELECT * FROM :inTab;
Here, outTab is target structure / table variable and inTab is source structure / table variable.
Filter can be applied to the simple statement just by adding WHERE clause:
outTab = SELECT * FROM :inTab WHERE OPERAND = 'DEMAND';
With the above code only records that have OPERAND value as 'DEMAND' is filtered and moved to target table outTab.
Lookup:
Lookup Table: /BI0/PUCCONTRACT
Lookup Fields: UCCONTRACT , CACONT_ACC
Different ways to lookup fields from another internal tables are:
1. Lookup using Join:
inTab is joined (LEFT OUTER JOIN) with table /BI0/PUCCONTRACT with ucinslalla as key field. This way we get access to all the fields that are present in table /BI0/PUCCONTRACT.
Note: we can derive other fields based on conditional logic, string operation, constant, etc.
outTab = SELECT
T1.DATEFROM,
T1.UCINSTALLA,
T1.UCOPERAND,
T1.UC_SEASON,
T1.RECORDMODE,
T1."/BIC/ZBILLDOC",
T1."/BIC/ZWERT1",
(CASE WHEN T1."/BIC/ZSTRING1" IS NULL THEN 'X' ELSE T1."/BIC/ZSTRING1" END) AS "/BIC/ZSTRING1",
(CASE WHEN T1."/BIC/ZSTRING2" IS NULL THEN '' ELSE LEFT(T1."/BIC/ZSTRING2",3) END) AS "/BIC/ZSTRING2",
T1."/BIC/ZSTRING3",
T1."/BIC/ZSTRING4",
T1."/BIC/ZERSAT",
T1."/BIC/ZWERT2",
'99991231' AS DATETO,
1 AS FC_COUNT,
T1.UCRATEFAKT,
T1.UCRATE_TY,
T1.UNIT,
T1.VAR_VR,
T1.CURRENCY,
T2.CACONT_ACC,
T2.UCCONTRACT,
T1.RECORD,
T1.SQL__PROCEDURE__SOURCE__RECORD
FROM :inTab AS T1
LEFT OUTER JOIN "/BI0/PUCCONTRACT" AS T2 ON T1.UCINSTALLA = T2.UCINSTALLA;
2. Lookup using nested Loop
Iterate on target table variable (outTab using index i) and then iterate lookup table variable (caTab using index j), assign lookup fields where the key UCINSTALLA matches in both tables.
Note: Nested loops are useful when there are multiple records in lookup table for every record in target table. Nested loop is performance intensive.
DECLARE i, j, lv_count, lv_cacount INTEGER;
-- Direct assignment of inTab to outTab
outTab = SELECT * FROM :inTab;
-- Counting number of records in outTab using RECORD_COUNT function
lv_count = RECORD_COUNT(:outTab);
-- Lookup SELECT to get CACONT_ACC & UCCONTRACT from table /BI0/PUCCONTRACT for each UCINSTALLA in outTab into table variable caTab
caTab = SELECT UCINSTALLA, CACONT_ACC, UCCONTRACT FROM "/BI0/PUCCONTRACT"
WHERE UCINSTALLA IN ( SELECT DISTINCT UCINSTALLA FROM :outTab) ORDER BY UCINSTALLA;
SELECT COUNT(*) INTO lv_cacount FROM :caTab; -- Counting number of records in caTab using query
-- Initialise both varibales i & j to 0.
i = 0; j = 0;
FOR i IN 1..lv_count DO -- Can use RECORD_COUNT(:outTab) directly instead of variable lv_count
FOR j in 1..:lv_cacount DO
-- If ucinstalla of outTab matches with ucinstalla of caTab then assign lookup fields uccontract & cacont_acc
IF :outTab.ucinstalla[i] = :caTab.ucinstalla[j] THEN
outTab.uccontract[i] = :caTab.uccontract[j];
outTab.cacont_acc[i] = :caTab.cacont_acc[j];
END IF;
END FOR;
END FOR;
3. Lookup using SEARCH function
SEARCH function is similar to READ statement in ABAP. Finds the search value and returns the position (index) in table variable.
DECLARE i, j, lv_count, lv_cacount INTEGER;
-- Direct assignment of inTab to outTab
outTab = SELECT * FROM :inTab;
-- Counting number of records in outTab using RECORD_COUNT function
lv_count = RECORD_COUNT(:outTab);
-- Lookup SELECT to get CACONT_ACC & UCCONTRACT from table /BI0/PUCCONTRACT for each UCINSTALLA in outTab into table variable caTab
caTab = SELECT UCINSTALLA, CACONT_ACC, UCCONTRACT FROM "/BI0/PUCCONTRACT"
WHERE UCINSTALLA IN ( SELECT DISTINCT UCINSTALLA FROM :outTab) ORDER BY UCINSTALLA;
SELECT COUNT(*) INTO lv_cacount FROM :caTab; -- Counting number of records in caTab using query
-- Initialise both varibales i & j to 0.
i = 0; j = 0;
FOR i IN 1..RECORD_COUNT(:outTab) DO -- Using RECORD_COUNT function directly without variable
j = :caTab.SEARCH(ucinstalla, :outTab.ucinstalla[i]);
IF j <> 0 THEN -- if search fails j will have value 0, which may cause error. So always check for value of j
outTab.uccontract[i] = :caTab.uccontract[j];
outTab.cacont_acc[i] = :caTab.cacont_acc[j];
ELSE
outTab.cacont_acc[i] = '';
outTab.uccontract[i] = '';
END IF;
END FOR;
Conclusion:
Based on the requirement the lookup can be performed in HANA routine similar to ABAP routine. It is important to choose the right approach:
Direct relationship exists -> use JOIN
Multiple rows exists in lookup table for each row in target table -> use Nested loop
Single row exists in lookup table for each row in target table -> use SEARCH function
Bluesky
