Load-Lookup in SQR

It’s common to join tables within SQRs to retrieve data from normalized tables.

As SQL statements consume significant computing resources, such joins may be a hindrance to the performance of the SQR.

Further, as the number of tables that are used in the join increases, the performance decreases.

This rationale makes us look for ways to reduce the number of tables used in the join as a means to tune the SQR.

This is when Load-Lookup in SQR comes into the picture.

Using Load-Lookup is a two-step process – here’s how to make use of it in your SQR programs.

Load-Lookup

You start by loading the Load-Lookup. This can either be done within the setup section or within a procedure. While done within the setup section, it is only executed once. When within procedures, the execution happens each time the code is encountered.

The code snippet shows how this is used within the setup section. On execution of the below Load-Lookup, SQR creates an array containing a set of return values against keys.

Begin-Setup
LOAD-LOOKUP
NAME = PRODUCT_NAMES
TABLE = PRODUCTS
KEY = PRODUCT_CODE
RETURN_VALUE = DESCRIPTION
End-Setup

Lookup

Once we have the first step in place, it’s time to utilize the lookup.

The below code will essentially lookup for a key (PRODUCT_CODE) in the array and return the return value (DESCRIPTION).

BEGIN-SELECT
ORDER_NUM (+1,1)
PRODUCT_CODE
LOOKUP PRODUCT_NAMES &PRODUCT_CODE $DESC
PRINT $DESC (,15)
FROM ORDERLINES
END-SELECT

Multiple Keys / Return_values

Although Load-Lookup doesn’t support multiple keys or return_values, we can do this by concatenating the values using database-specific concatenation operators.

So if you are on Oracle DB, this would be how you can do it. The return values can later be separated using the unstring command.

LOAD-LOOKUP
NAME = PRODUCT_NAMES
TABLE = PRODUCTS1
KEY = 'PRODUCT_CODE||','||KEY2'
RETURN_VALUE = 'DESCRIPTION||','||COLUMN2'

Using where clause in Load-Lookup

To limit the values that are populated in the Load-Lookup array, we can use a where clause as shown below.

LOAD-LOOKUP
NAME = PRODUCT_NAMES
TABLE = PRODUCTS
KEY = PRODUCT_CODE
RETURN_VALUE = DESCRIPTION
WHERE = PRODUCT_CODE > 1000

Tags: Load-Loookup, PeopleSoft Tutorials, SQR, SQR Tuning

LOAD-LOOKUP NAME = PRODUCT_NAMES TABLE = 'PRODUCTS A' KEY = PRODUCT_CODE RETURN_VALUE = DESCRIPTION WHERE = 'A.EFFDT = (SELECT MAX(EFFDT) FROM PRODUCTS WHERE PRODUCT_CODE = A.PRODUCT_CODE AND EFFDT <= SYSDATE)'

Subscribe to Comments RSS Feed in this post

15 Responses

anoop puzhamudi #

Thanks Buddy! 🙂

- Rakesh Subhagan #
  
  @Anoop – Thanks for stopping by…
  Hope you found the post useful 🙂
  
  - Sravani #
    
    Does DB2 support multiple return values ?
    
    - Rakesh Subhagan #
      
      Hi Sravani,
      
      I’ve not worked on DB2 yet but you may want to try the CONCAT operator to see if it works.
      
      Return_value = 'DESCRIPTION CONCAT ',' CONCAT COLUMN2'
      Return_value = 'DESCRIPTION CONCAT ',' CONCAT COLUMN2'
      
      Please let us know if this helped.
      
Shafia #

@Rakesh: Good Post.

- Rakesh Subhagan #
  
  @ Shafia – Glad that you found the post to be useful. 🙂
  
dcraig #

Hello Rakesh,
Nice to see this post. that is showing some light to a newbie.Does this LOAD LOOKUP can be used in Peoplesoft page where i’m trying to filter one field values depend on other?
like there are two fields one Business unit & other is Plant.
when i select a bussiness unit the plant Lookvalues should sort according to Business unit.
if i try this in SQR but SQR is called later once you submit your data from peoplesoft page.

Bit confused here…can you show some light?

Regards

- Rakesh Subhagan #
  
  Craig, glad that you found it useful.
  
  Load lookup is limited to SQRs. You cannot use it within a PeopleSoft page.
  
  For your case, a view that has both Business Unit and Plan as key fields could be used as a Lookup / prompt table for Plant field.
  Also, Business Unit should be a high level key on the record that is used on the page. PeopleSoft would take care of the rest.
  
dcraig #

Thanks for the info.
I created a view/record with BUSINESS_UNIT,PLANT with Key values.
Now when i select business unit(it has another prompt table values(Business_unit,descr)it goes well. But when i select for the PLANT(With new views/reocrd created as prompt table), it says no matching values.
The view/record i created has PLANT matching values with BUSINESS_UNIT column values.
I don’t know where i’m getting wrong.Any idea?

Regards

Haritha #

Nice answers

Santhosh #

Hi Rakesh,

Can you plese tell how we can include subquery for the where field.

Thanks,
Santhosh

Rakesh Subhagan #

Hi Santhosh,

I haven’t tried this myself but you may want to give the below one a try.

If your where clause is longer ( >256 chars), you can assign it to a string and then use it with the Load-Lookup.

vidit #
Hi Rakesh,

Just going through your blog and found that correct syntax to use subquery in where clause of load lookup should be:
WHERE = A.EFFDT = (SELECT MAX(EFFDT) FROM PRODUCTS WHERE PRODUCT_CODE = A.PRODUCT_CODE AND EFFDT <= SYSDATE)
WHERE = A.EFFDT = (SELECT MAX(EFFDT) FROM PRODUCTS WHERE PRODUCT_CODE = A.PRODUCT_CODE AND EFFDT <= SYSDATE)
Regards,
Vidit

Ankit #

I tried running the code with writting the where condition in single quotes it is given error as “Did not find end of literal”

- Rakesh Subhagan #
  
  Hi Ankit,
  
  Do you have an exclamation mark (!) in your where clause?
  
  Regards
  Rakesh

PSoftSearch - PeopleSoft Blog