LOAD-LOOKUP

Syntax

In the SETUP section:

LOAD-LOOKUP NAME=lookup_table_name TABLE=database_table_name KEY=key_column_name RETURN_VALUE=return_column_name [ROWS=initial_row_estimate_int_lit] [EXTENT=size_to_grow_by_int_lit] [WHERE=where_clause_txt_lit] [SORT=sort_mode] [QUIET] [SCHEMA=schema_name] [PROCEDURE=procedure_name] [COMMAND=command_string] [GETDATA=getdata_string] [PARAMETERS=(...)] [FROM-ROWSET=(m,n,-n,m-,all)] [FROM-PARAMETER=rowset_name]

In the body of the report:

LOAD-LOOKUP NAME=lookup_table_name TABLE=database_table_name KEY=key_column_name RETURN_VALUE=return_column_name [ROWS=initial_row_estimate_lit|_var|_col] [EXTENT=size_to_grow_by_lit|_var|_col] [WHERE=where_clause_txt_lit|_var|_col] [SORT=sort_mode] [QUIET] [SCHEMA=schema_name] [PROCEDURE=procedure_name] [COMMAND=command_string] [GETDATA=getdata_string] [PARAMETERS=(...)] [FROM-ROWSET=(m,n,-n,m-,all)] [FROM-PARAMETER=rowset_name]

Description

Loads an internal table with columns from the database. Enables a quick search using the LOOKUP command.

Use the LOAD-LOOKUP command in conjunction with one or more LOOKUP commands.

LOAD-LOOKUP retrieves two columns from the database, the KEY field and the RETURN_VALUE field. Rows are ordered by KEY and stored in an array.

LOAD-LOOKUP commands specified in the SETUP section are always loaded and cannot reference variables for the ROWS, EXTENT, and WHERE arguments.

When you use the LOOKUP command, SQR searches the array (with a binary search) to find the RETURN_VALUE corresponding to the KEY referenced in the lookup.

Usually this type of lookup can be done with a database join , but joins take substantially longer. However, if your report is small and the number of rows to be joined is small, a lookup table can be slower because the entire table has to be loaded and sorted for each report run.

By default, SQR lets the database sort the data. This works well if the database and SQR both use the same character set and collating sequence. The SORT argument enables you to specify the sorting method if this is not true. Additionally, if the machine that SQR is running on is faster than the machine that the database is running on, letting SQR perform the sort could decrease the execution time of the report.

The only limit to the size of a lookup table is the amount of memory that your computer has available. You could conceivably load an array with many thousands of rows. The binary search is performed quickly regardless of how many rows are loaded.

Except for the amount of available memory, the number of lookup tables that can be defined is unlimited.

Parameters

Parameter	Description
NAME	The name of the lookup table. The array name is referenced in the LOOKUP command.
TABLE	The name of the table in the database, where the KEY and RETURN_VALUE columns or expressions are stored.
KEY	The name of the column that is used as the key in the array that is used for looking up the information. Keys can be character, date, or numeric data types. If the key is numeric, SQR permits only integers with 12 digits or fewer for the KEY column. Keys can be any database-supported expression. See the RETURN_VALUE argument.
RETURN_VALUE	The name of the column (expression) that is returned for each corresponding key. The following example is for ORACLE. See your database manual for the correct syntax. RETURN_VALUE='name||''-''||country||''-''||population'
ROWS	The initial size of the lookup table. This argument is optional, and if it is not specified, a value of 100 is used.
EXTENT	The amount to increase the array when it becomes full. This argument is optional, and if it is not specified, a value of 25% of the ROWS value is used.
WHERE	A WHERE clause used to select a subset of all the rows in the table. If it is specified, the selection begins after the word WHERE. The WHERE clause is limited to 255 characters.
SORT	The sorting method to be used. The following values are permitted: DC: Database sorts data, case-sensitive sort DI: Database sorts data, case-insensitive sort SC: SQR sorts data, case-sensitive sort SI: SQR sorts data, case-insensitive sort The default is SC or the method specified by the -LL command-line flag. The DI method is applicable only to databases that provide this feature and have been installed in that manner.
QUIET	Suppresses the message Loading lookup array... when the command executes. The warning message stating the number of duplicate keys found is also suppressed.

Example

The following command loads the array states with the columns abbr and name from the database table stateabbrs, where country is USA.

load-lookup

name=states

rows=50

table=stateabbrs

key=abbr

return_value=name

where=country='USA'

The preceding array is used in the example for the LOOKUP command to retrieve the full text of a state name from the abbreviation.

The following example uses the LOOKUP command to validate data entered by a user using an INPUT command:

get_state:
input $state 'Enter state abbreviation'
uppercase $state
lookup states $state $name
if $name = ''         ! Lookup didn't find a match
  show 'No such state.'
  goto get_state
end-if

Enclose any command argument with embedded spaces by single quotes, as shown in the following example:

where='country=''USA'' and region = ''NE'''

The entire WHERE clause is surrounded by quotes. The two single quotes around USA and NE are translated to one single quote in the SQL statement.

The following example uses joins in a LOAD-LOOKUP command by including two tables in TABLE and the join in WHERE:

load-lookup

name=states

rows=50

sort=sc

table='stateabbrs s, regions r'

key=abbr

return_value=name

where='s.abbr = r.abbr and r.location = ''ne'''