Load-Lookup Love Letter: Introduction

Load-lookup and lookup are my favorite commands in SQR. I’m sorry if that hurts the feelings of all the other commands, but it can’t be helped. Load-lookup, how do I love thee? Let me count the ways.

  1. These commands are simple on the surface but encapsulate enough computer science for semester at college: SQL, dynamic memory allocation, a two-dimensional array data structure, four kinds of sorting, binary searching, and performance optimization.
  2. The concept of built-in in-memory lookup is rare in programming languages, although SQR demonstrates that it is amazingly useful. (I know of Excel’s LOOKUP function. What else is there?)
  3. By using snippets of SQL as parameters, load-lookup has a flexibility and reach that few other commands can match.
  4. Load-lookup creates information objects that enable a wide variety of algorithms.

O.K., that’s enough mush. This is a programming blog, not a romance novel.

Plan of exposition

I really just want to discuss the wonderful things load-lookup can do, but first we need to discuss the basics. This post will be in four parts.

  1. Introduction: what do load-lookup and lookup do, and how do they work?
  2. SQL: what are the parameters for load-lookup in an SQL database environment?
  3. DDO: what are the parameters for load-lookup in a DDO datasource environment?
  4. Algorithms: what can we do with load-lookup?

What do these commands do?

Load-lookup establishes an in-memory array with two fields – a unique key and a corresponding return value. Lookup searches that array to find the return value corresponding to a given key. The key and return value are both character strings. If lookup doesn’t find the key in the array, it returns the null string (”).

How do they work?

Load-lookup uses between four and thirteen parameters. The name parameter identifies the lookup object for future reference. Load-lookup constructs and executes an SQL statement from the table, key, return_value, and where parameters. It reads the results of the SQL statement, sorts them according to the sort parameter, and stores them in an array according to the rows and extent parameters.

Lookup uses three parameters. The first parameter is the name of the lookup object created by load-lookup. The second parameter is the search value. The third parameter is a string variable in which to store the return value. Lookup uses binary search to locate the search value in the key field of the array.

What are they for?

These commands can replace a SQL select statement. Suppose we have an employee’s DEPTID from the PS_JOB table and we want to know the name of the department. Instead of this:

begin-select
DESCR
 from PS_DEPT_TBL
where SETID = &J.SETID_DEPT
  and DEPTID = &J.DEPTID
end-select

We can write:

lookup deptname &J.DEPTID $descr

We’ll have one SQL select execution on the entire PS_DEPT_TBL table rather than many SQL select executions for one row each of the PS_DEPT_TBL table.

These commands can simplify a SQL select statement. Suppose we are reading PS_JOB and we also want the department name. Instead of this:

begin-select
J.EMPLID
J.DEPTID
D.DESCR
 from PS_JOB J, PS_DEPT_TBL D
where D.SETID = J.SETID_DEPT
  and D.DEPTID = J.DEPTID

… (three effective date subqueries)

We can write:

begin-select
J.EMPLID
J.DEPTID
  lookup deptname &J.DEPTID $descr
 from PS_JOB J
where …

(two effective date subqueries)

We’ll simplify the SQL statement, which might make the difference between an optimized search and reading every single row. We’ll also avoid the possibility that bad values in DEPTID or SETID_DEPT, or rows missing in PS_DEPT_TBL could cause us to skip rows in PS_JOB.

There’s lots more to do with load-lookup. We’ll discuss that in the post on algorithms.

Looking Ahead

Next week we’ll look at the load-loadup syntax for SQL databases.

Brain Teaser

Meanwhile, here is a brain teaser. Please post the solution as a comment.

An SQR program is to print a telephone directory sorted by name. Explain or provide code for how can it print, at the top of each page, the first person and last person to appear on that page.

11 Comments

  1. Bob Josephson says:

    A built-in, in-memory lookup other than SQR and Excel? Well, C#’s new LINQ functionality might qualify. Of course, it came much later than SQR’s lookup commands.

  2. Petro Philip says:

    Is it possible to join 2 tables in the ‘TABLE=’ clause? (Without using a view?)

    • administrator says:

      Definitely. Use code like this:

      table = 'PS_EMPLOYEES E, PS_DEPT_TBL D'
      where = 'D.DEPTID = E.DEPTID'

  3. Sundeep Kumar says:

    !Below is the code for the brain teaser
    ! Assumption that i have taken here is that the number of lines per page is 62

    Begin-Setup
    no-formfeed
    End-Setup

    Begin-Program
    lET #I=1
    Begin-Select
    EMPLID
    NAME
    IF MOD(#I, 59) = 1
    IF #I = 1
    Print &Name (+1,70)
    ELSE
    Print &Name (+1,70)
    END-IF
    eND-IF

    Print &EMPLID (+1,1,20)
    Print &NAME (,24,30)

    IF MOD(#I, 59) = 0
    Print &Name (+1,70)
    eND-IF

    LET #I = #I +1

    From PS_PERSONAL_DATA
    where rownum <200
    order by EMPLID
    End-Select
    If mod(#I – 1,59) 0
    Print &Name (62,70,50)
    End-If
    End-Program

    • administrator says:

      You’re working too hard. Think about the begin-heading command. It waits until you’ve filled the main body of the page, then it prints the header. It is like a subroutine that you don’t have to call, it knows when to do its work. It has access to any global variable in the program and it can perform any command – not just print commands.

  4. Sundeep Kumar says:

    Yeah I did try using the Headers and footers too. The problem was when i use the variable in Heading and footing section the name of the last employee on the page is being printed always. It seems to me like the SQR first prints all the data and then prints the Header and footer for that page. Since i was using $NAME in the header and footer the last employees name of that page was printed in both header and footer.

    • administrator says:

      Excellent, you’ve solved half the problem – printing the last name. You are correct about the way heading and footing work. Now, try to determine when a name is the first on a page and save that name until the heading prints.

  5. Sundeep Kumar says:

    !Final Solution to the Brain Teaser

    Begin-Heading 1
    &nsbp;&nsbp;Print $NAME (+1,70)
    End-Heading

    Begin-footing 1
    &nsbp;&nsbp;Print &NAME (,70)
    End-footing

    Begin-Program
    Begin-Select
    EMPLID
    NAME
    &nsbp;&nsbp;Print &EMPLID (+1,1,20)
    &nsbp;&nsbp;If #PAGE-COUNT <> #OLD
    &nsbp;&nsbp;&nsbp;&nsbp;Let $NAME = &NAME
    &nsbp;&nsbp;End-If
    &nsbp;&nsbp;Print &NAME (,24,30)

    &nsbp;&nsbp;Let #OLD = #PAGE-COUNT

    From PS_PERSONAL_DATA
    where rownum <200
    order by EMPLID
    End-Select
    End-Program

    • administrator says:

      Very good, you solved it better than I had. I didn’t think of using the built-in #page-count variable.

  6. prashanth says:

    How can i write a query using load lookup for joining two or more tables…
    Here is my query..Can you just change the query using load lookup…

    begin-select

    alter-printer
    font={CourierFont}
    point-size={PointSize2}

    a.account_name (,{Col1},{Col1Width}) on-break level=1
    sh.service_name (,{Col2},{Col2Width}) on-break level=2
    !i.effective_date (,{Col3},{Col3Width}) !Edit {TaskQueueDTFormat}
    to_char(i.effective_date,’dd-mm-yyyy hh24:mi:ss’) &BillDate (,{Col3},{Col3Width})
    move &BillDate to $BillDate
    i.invoice_id (,{Col4},{Col4Width})
    a.unbilled_amount (,{Col5},{Col5Width}) Edit {NumberMask}
    sh.general_1 &RatePlan
    move &RatePlan to #RatePlan
    sh.general_2 &ServiceCategory
    move &ServiceCategory to #ServiceCategory
    sh.general_4 &ConnectionType
    move &ConnectionType to #ConnectionType
    sh.general_5 &CircuitType
    move &CircuitType to #CircuitType
    sh.general_6 &SubscriptionType
    move &SubscriptionType to #SubscriptionType
    pih.product_id &ProdId
    move &ProdId to #ProdId

    do RequiredRentAmount

    position (+1)

    from account a,service_history sh,invoice i,reference_code rc,customer_node_history cnh,product_instance_history pih
    where sh.customer_node_id=a.customer_node_id
    and a.invoice_id=i.invoice_id
    and a.account_id=i.account_id
    and a.account_type_id=10000
    and i.invoice_type_id=1000020
    and sh.BASE_PRODUCT_INSTANCE_ID = pih.PRODUCT_INSTANCE_ID
    and pih.BASE_PRODUCT_INSTANCE_ID is null
    and sh.customer_node_id=pih.customer_node_id
    and sh.service_id=(select max(service_id) from service_history sh,product_instance_history pih
    where sh.BASE_PRODUCT_INSTANCE_ID = pih.PRODUCT_INSTANCE_ID
    and pih.BASE_PRODUCT_INSTANCE_ID is null
    and sh.customer_node_id=cnh.customer_node_id)
    and to_char(rc.reference_code)=sh.general_1
    and rc.reference_type_id in(select reference_type_id from reference_type where type_label like $PlanName)
    and cnh.region_code=#ref_code
    and sh.customer_node_id=cnh.customer_node_id
    and a.customer_node_id=cnh.customer_node_id
    and i.customer_node_id=cnh.customer_node_id
    and trunc(a.LAST_MODIFIED) between trunc(to_date($ReportStDate,’dd-mm-yyyy hh24:mi:ss’))
    and trunc(to_date($RptEndDate,’dd-mm-yyyy hh24:mi:ss’))
    and trunc(a.LAST_MODIFIED) between trunc(sh.effective_start_date) and trunc(sh.effective_end_date)
    and trunc(a.LAST_MODIFIED) between trunc(cnh.effective_start_date) and trunc(cnh.effective_end_date)
    and trunc(a.LAST_MODIFIED) between trunc(pih.effective_start_date) and trunc(pih.effective_end_date)
    !and cnh.node_name like ‘PcustomerAccured5′

    end-select

    SOMEONE PLEASE DO THE NEEDFUL AS IT IS HIGHLY ESSENTIAL FOR PERFORMANCE……..

  7. administrator says:

    Wow, Prashanth, that is a very large query! The “where” parameter in load-lookup is limited to 256 characters, and the “table” parameter must be a literal string on a single line. That’s going to be hard to write and harder to read. Try creating database views that encapsulate as much logic as you can. The SQL statement for a view can join tables and be as long as you need. Then you can use the view in the load-lookup statement and it will appear simple.