Loads an internal table with columns from the database. Allows for quick search using LOOKUP.
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_txt_lit] [ PROCEDURE=proc_txt_lit [PARAMETERS=({{arg1 [IN|INOUT]}|NULL} [[,argi [IN|INOUT]]|NULL] ... )] (or) COMMAND=command_txt_lit (or) GETDATA=getdata_txt_lit ] [{FROM-ROWSETS=({m|m-n|m-|-n} [,...]}|{ALL})}| {FROM-PARAMETER=parameter_txt_lit}]
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={txt_lit|_var}] [ PROCEDURE={txt_lit|_var} [PARAMETERS=({{arg1 [IN|INOUT]}|NULL} [[,argi [IN|INOUT]]|NULL] ... )] (or) COMMAND={txt_lit|_var} (or) GETDATA={txt_lit|_var} ] [{FROM-ROWSETS=({m|m-n|m-|-n} [,...]}|{ALL})}| {FROM-PARAMETER={txt_lit|_var}}]
The following LOAD-LOOKUP elements are not supported (processed but not used) in Production Reporting DDO:
Name of the lookup table referenced in LOOKUP.
Name of the table in the database, where the KEY and RETURN_VALUE columns or expressions are stored (not supported for DDO).
Name of the column 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 numeric, Production Reporting permits only integers 12 digits or less for the KEY column. Keys can be any database-supported expression. See the RETURN_VALUE argument.
Name of the column (expression) returned for each corresponding key.
You can combine several columns into an expression if you need several fields returned for each lookup. You can do this by concatenating columns. (This is not supported for DDO.)
The following example is for ORACLE. See your database manual for the correct syntax.
RETURN_VALUE='name||''‑''||country||''‑''||population'
(Optional) Initial size of the lookup table. If not specified, a value of 100 is used.
(Optional) Amount to increase the array when it becomes full. If not specified, a value of 25% of the ROWS value is used.
WHERE clause used to select a subset of all the rows in the table. If specified, the selection begins after the word WHERE. The WHERE clause is limited to 255 characters (not supported for DDO).
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.
Suppresses the message Loading lookup array... when the command executes. The warning message stating the number of duplicate keys found is also suppressed.
Identifies the location in the datasource of the object being queried. You can enter the following options under SCHEMA:
PROCEDURE—Name of datasource-stored procedure to execute. If the datasource is SAP R/3, this procedure is a BAPI. The name may include spaces.
PARAMETERS—Scalar and/or list variables of the form list_var|num_lit|txt_lit|txt_var| um_var|any_col. If you do not specify the keywords IN or INOUT, IN is the default. Specify all parameters in order; leaving any parameters unnamed causes a syntax error. To ignore a parameter, fill its position with the keyword NULL. This results in a Null value for that parameter position.
COMMAND—Text string passed to the datasource without modification by Production Reporting. This string can include embedded Production Reporting variables.
GETDATA—Supports the Java (DDO) GetData paradigm for data access.
Special case addition to the LOAD-LOOKUP syntax. Available for use with all datasource types, including SAP R/3 and JDBC. Names the rowset(s) from which to retrieve the column variables. For multiple row sets, use identical column name/type signatures. Row set numbers must be sequential from left-to-right within the parentheses, and must not overlap as in this example: (1-3, 2-4). Numeric literals or #variables are allowed.
In the FROM ROWSETS argument, “m” and “n” are integer values (1, 2, 3, 4, 5). “m-n” is 3-5 (rowsets 3, 4, 5). “m-” is 4- (rowsets 4, 5). “-n” is -3 (rowset 1, 2, 3).
Special case addition to the LOAD-LOOKUP syntax. Available only for SAP R/3 datasources.Use only in conjunction with the PROCEDURE keyword. This argument names an output parameter containing one or more rows from which the column variables are retrieved.
This is a similar concept to the PARAMETERS = statement in DECLARE-CONNECTION and ALTER-CONNECTION, execpt that the properties specified here alter the flow of returned information, as opposed to simply setting login properties. Can be used in conjunction with any data-access model (Procedure, Command, Getdata). An application of this statement would be in the MDB setting, where it might be used to specify such things as Level, Generation, or Include-Column. For example, PROPERTIES = ( ‘SetColumn’ = 5 )
Use LOAD‑LOOKUP 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 LOOKUP, Production Reporting 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 joined is small, using a lookup table can be slower, since the entire table has to be loaded and sorted for each report run.
By default, Production Reporting lets the database sort the data. This works fine if the database and Production Reporting both use the same character set and collating sequence. The SORT argument allows you to specify the sorting method if this is not true. Additionally, if the machine that Production Reporting is running on is faster than the machine the database is running on, letting Production Reporting 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 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, there is no limit to the number of lookup tables that can be defined.
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 LOOKUP to retrieve the full text of a state name from the abbreviation.
The following example uses LOOKUP to validate data entered by a user using INPUT:
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
Surround any command argument with embedded spaces by single quotes, as shown here:
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 LOAD‑LOOKUP 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'''
The following example uses multiple columns as the KEY for LOAD-LOOKUP: