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.
- 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.
- 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?)
- By using snippets of SQL as parameters, load-lookup has a flexibility and reach that few other commands can match.
- 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.
- Introduction: what do load-lookup and lookup do, and how do they work?
- SQL: what are the parameters for load-lookup in an SQL database environment?
- DDO: what are the parameters for load-lookup in a DDO datasource environment?
- 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.
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.
Is it possible to join 2 tables in the ‘TABLE=’ clause? (Without using a view?)
Definitely. Use code like this:
table = 'PS_EMPLOYEES E, PS_DEPT_TBL D'
where = 'D.DEPTID = E.DEPTID'
!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
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.
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.
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.
!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
Very good, you solved it better than I had. I didn’t think of using the built-in #page-count variable.
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……..
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.