Load Lookup Arrays in SQR

How to improve the performance of the SQR using Load Lookup Arrays?

For Simple database related validation, we will fire a SQL to check the sanity of data. If this data validation is to be fired for each and every row of data, then it is better to go for Load-Lookup Arrays. This is like Prompt table validation in the case of Online Pages.

In this way, we can reduce the database hits and thereby increasing the Performance of the SQR Program.

For Eg: If we want to validate the Earnings Codes present in the file, we can use these arrays to validate the Earning Code data.

Generally we will use the below SQL to validate the ERNCD data

SELECT PET.ERNCD, PET.EFFECT_ON_FLSA, PET.ADD_GROSS
FROM PS_EARNINGS_TBL PET
WHERE PET.EFFDT= (SELECT MAX (EFFDT) FROM PS_EARNINGS_TBL
WHERE PET.ERNCD = ERNCD
AND EFFDT <=(SYSDATE)

AND PET.EFF_STATUS =”A”

AND PET.ERNCD = $Erncd
In order to avoid these database hits for each and every row of data, we can the below code to load all the valid Earning Codes at once from the Database to Load Lookup Array Name EARN and then use the array to validate the input ERNCD data.

! Lookup array for EARNINGS CODES

Let $where = ‘PET.EFFDT = (SELECT MAX (EFFDT) ‘||

‘FROM PS_EARNINGS_TBL ‘||

‘WHERE PET.ERNCD = ERNCD ‘||

‘AND EFFDT <=SYSDATE) ‘||

‘AND EFF_STATUS =”A”’

LOAD-LOOKUP

NAME = EARN

TABLE = ‘PS_EARNINGS_TBL PET’

KEY = ERNCD

RETURN_VALUE = ERNCD||’-'||EFFECT_ON_FLSA||’-'||ADD_GROSS

WHERE = $where

QUIET

In this case, KEY (This is nothing but Input Data value) is ERNCD and RETURN_VALUE (Output data value) is Combination of ERNCD, EFFECT_ON_FLSA and ADD_GROSS database field’s value.

! Checking Load lookup array for data validation

Lookup EARN $input_data $output_data

Note: It is good to initialize these Load Lookup Arrays in the SETUP Section related procedures.

Read More About Load Lookup Arrays