Peoplesoft and SQR

Parameters

Load-lookup takes four to nine parameters. The syntax looks like this.

load-lookup
  name=
  table=
  key=
  return_value=
  rows=
  extent=
  where=
  sort=
  quiet

The parameters may be in any order. If a parameter appears more than once, its last value is used. There can be spaces and line breaks before and after the equals signs. More than one parameter can be on a line.

The SQL statement formed from the table, key, return_value, and where parameters will not be validated during program compilation. It will be passed to the database management system for validation. Load-lookup does not evaluate dynamic variables.

Name is required. It identifies the lookup object for later reference. It is a literal value with no quote marks.

Table is required. It is the text that follows the keyword “from” in the SQL statement that load-lookup constructs. There are four forms for this parameter.

1. A single database table name, with or without quote marks.

table=PS_DEPT_TBL
OR
table='PS_DEPT_TBL'

2. A single database table name, one or more spaces, and an alias, enclosed in quote marks.

table='PS_DEPT_TBL D'

3. Multiple database table names, with or without aliases, separated by commas, enclosed in quote marks.

table='PS_EMPLOYEES E, PS_DEPT_TBL D'

4. An SQL select statement enclosed in parentheses, enclosed in quote marks.

table='(select EMPLID, ROWNUM from PS_PERSONAL_DATA order by EMPLID)'

Key is required. It will be the first column or expression that follows the keyword “select” in the SQL statement that load-lookup constructs. It must be either a character string, a date, or an integer no longer than 12 digits. There are two forms for this parameter.

1. A single database column name, with or without alias, with or without quote marks.

key=DEPTID
OR
key=D.DEPTID
OR
key='DEPTID'
OR
key='D.DEPTID'

2. Any valid SQL expression that combines one or more database columns, with quote marks. An expression can be the way to use non-integer numbers or lengthy integers.

key='SETID || DEPTID'
OR
key='SETID || ''-'' || DEPTID'
OR
key='to_char(NET_PAY)'

Return_value is required. It will be the other column or expression that follows the keyword “select” in the SQL statement that load-lookup constructs. There are two forms for this parameter.

1. A single database column name, with or without alias, with or without quote marks.

key=DESCR
OR
key=D.DESCR
OR
key='DESCR'
OR
key='D.DESCR'

2. Any valid SQL expression that combines one or more database columns, with quote marks.

key='upper(DESCR)'
OR
key='DESCR || ''-'' || LOCATION'

Rows is optional. (Grammatical note: “rows” has the letter “s” at the end, but it is the name of one parameter. Therefore, I use the singular version of the verb “to be.”) It is an estimate of the number of rows that the SQL statement will return, to guide load-lookup in allocating memory. If the rows value is not specified, load-lookup will start with an allocation for 100 rows. If load-lookup appears in the setup section, rows must be a literal number without quote marks. Anywhere else, rows may be a literal number, a numeric variable, or a numeric database column.

Extent is optional. If load-lookup receives more rows from the SQL statement than were specified in the rows parameter, it will allocate more memory according to the number of rows specified in the extent parameter. You would think that extent couldn’t be used unless rows is used and that extent should be less than rows, but neither condition is required. If the extent value is not specified, load-lookup will set it to 25% of the value for the rows parameter. If load-lookup appears in the setup section, extent must be a literal number without quote marks. Anywhere else, extent may be a literal number, a numeric variable, or a numeric database column.

Where is optional. If it is present, the SQL statement will have a where clause and the text of the where parameter will follow the keyword “where” in the SQL statement. If load-lookup appears in the setup section, where must be a literal string, on one line, enclosed in quote marks. Anywhere else, where can be a literal string, on one line, enclosed in quote marks, or a character string variable, or a character string database column. The value of where is limited to 255 characters.

Sort is optional. It can take one of four values without quote marks: DC means that the database sorts the key and is case sensitive (“A” and “a” are different). DI means that the database sorts the key and is case insensitive (“A” and “a” are the same). DI doesn’t work on every database. SC and SI mean that the keys are sorted by the SQR interpreter.

If load-lookup doesn’t have a sort parameter, SQR looks for the –LL parameter on the command line, which uses the same four codes. Lacking that, the situation turns muddy. The SQR Language Reference says “By default, SQR lets the database sort the data.” It also says, “The default is SC …”. In my experience, on MS Windows and Oracle, the default is SC.

Quiet is optional and does not have an equals sign or a value. If it is present, load-lookup executes without notifying the user. If it is absent, load-lookup displays one or two lines of text in the terminal (DOS) window while the program executes. That text also appears in the log file. The first line of text declares that the program is loading the lookup array and reports the number of rows in the array. The second line of text appears only if there are duplicate values of the key found by the SQL select statement and reports the number of duplicates.

Duplicate Keys

A load-lookup command can find duplicate keys even if the underlying table doesn’t have duplicates. For example, the PS_JOB table has a unique, multi-column key consisting of EMPLID, EMPL_RCD, EFFDT, and EFFSEQ. If load-lookup selects the entire table and the Key=EMPLID, there will be duplicate keys. We can avoid that in three ways:

1. Use the all the columns of the unique, multi-column key in the key parameter.

load-lookup
  key='EMPLID || ''@'' || EMPL_RCD || ''@'' || EFFDT || ''@'' || EFFSEQ'

2. Write a where clause that returns a single row for the key parameter we want.

let $where =     'J.EMPL_RCD = (select max(EMPL_RCD) '
          ||                     'from PS_JOB '
          ||                    'where EMPLID   = J.EMPLID) '
          || 'and J.EFFDT    = (select max(EFFDT) '
          ||                     'from PS_JOB '
          ||                    'where EMPLID   = J.EMPLID '
          ||                      'and EMPL_RCD = J.EMPL_RCD '
          ||                      'and EFFDT   <= SYSDATE) '
          || 'and J.EFFSEQ   = (select max(EFFSEQ) '
          ||                     'from PS_JOB '
          ||                    'where EMPLID   = J.EMPLID '
          ||                      'and EMPL_RCD = J.EMPL_RCD '
          ||                      'and EFFDT    = J.EFFDT) '

load-lookup
  key=EMPLID
  where=$where

3. Something in-between 1 and 2.

let $where =     'J.EMPL_RCD = (select max(EMPL_RCD) '
          ||                     'from PS_JOB '
          ||                    'where EMPLID   = J.EMPLID) '
          || 'and J.EFFSEQ   = (select max(EFFSEQ) '
          ||                     'from PS_JOB '
          ||                    'where EMPLID   = J.EMPLID '
          ||                      'and EMPL_RCD = J.EMPL_RCD '
          ||                      'and EFFDT    = J.EFFDT) '

load-lookup
  key='EMPLID || ''@'' || EFFDT'
  where=$where

Looking Ahead

Next week I’ll try to describe the load-loadup syntax for DDO datasources. Since I’ve never used a DDO datasource, this could be funny … or pathetic.

Brain Teaser

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

An SQR program is importing data about people who are already in the database. The Social Security numbers (SSN) of those people are in a table (PS_PERS_NID). The people are identified only by their SSN in the import file and some of the SSN have typos. Explain or provide code for identifying the people with bad SSN.