Search This Blog

Monday, November 2, 2009

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.

No comments:

Post a Comment