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.