Array Searches in Peoplesoft SQR

There are two things we can do after we quicksort an array.  First, we can read it row by row, and the data will be in order.  Second, we can use a binary search to find a specific row very, very quickly.

Binary Search

A binary search has some conceptual similarities to quicksort.  It starts with the whole array, does a little work, selects half its previous domain, and repeats.  It could be coded with recursion in C or Java.  This routine has two statements that are specific to the array structure; first to retrieve a key field, second to return a return-value field.  To generalize this procedure, replace the two statements with calls to array-specific procedures.

begin-procedure binary_search
  let $return_value = ''
  let #lower = 0
  let #upper = #max_index
  while #lower + 1 < #upper
    let #middle = floor((#lower + #upper) / 2)
    let $middle_key = array.key(#middle)
    evaluate $middle_key
      when > $search_key

! The middle value is too high, the next pass should
! focus on the lower half of this pass.

        let #upper = #middle
      when < $search_key

! The middle value is too low, the next pass should
! focus on the upper half of this pass.

        let #lower = #middle
      when-other

! We found the match.

        let $return_value = array.return_value(#middle)

! Set #lower and #upper to exit the loop.

        let #lower = #middle
        let #upper = #middle
    end-evaluate
  end-while
end-procedure binary_search

Binary / Linear Search

Peoplesoft uses “effective dates” to keep track of changes in an object’s status.  An object could be a person, a department, a general ledger account, etc.  Status could refer to any fact about that object.

Imagine an employee (Mary) who is hired on January 1, 1995 as a computer operator for a wage of $20 per hour.  On January 1, 1996, she gets a raise to $22 per hour.  On December 10, 1997, she is promoted to senior computer operator at $25 per hour.  On May 3, 2000, she becomes a junior programmer, assigned to learn SQR, for $30 per hour.  On August 1, 2002, she rises to programmer I, paid $35 per hour.  The PS_JOB table has a row for her employee number (EMPLID) on each of these dates (EFFDT).

Suppose we have an array with all the history of every employee, sorted by EMPLID and EFFDT.  We want to find Mary’s pay rate as of September 1, 2001.  We can use a binary search to find one of the rows for Mary, but there is no row for September 1, 2001.  The row we want is the row with the last / highest / maximum date before our target date, which is the May 3, 2000 row.  We can use a bidirectional linear search to find it.

begin-procedure binary_effdt_search

! First, find a row with a matching key.

  let #lower = 0
  let #upper = #max_index
  while #lower + 1 < #upper
    let #middle = floor((#lower + #upper) / 2)
    let $middle_key = array.key(#middle)
    evaluate $middle_key
      when > $search_key
        let #upper = #middle
      when < $search_key
        let #lower = #middle
      when-other

! Second, if the $search_key matches, find the row
! with the highest effective date.

        while array.key(#middle + 1) = $search_key
          and array.effdt(#middle + 1) <= $search_effdt
          add 1 to #middle
        end-while

! Set #lower and #upper to exit the loop.

        let #lower = #middle
        let #upper = #middle
    end-evaluate
  end-while

! Third, if the $search_key matches, find the latest
! array.effdt() less than or equal to the $search_effdt.

  if array.key(#lower) = $search_key
    while array.key(#lower - 1) = $search_key
      and array.effdt(#lower) > $search_effdt
      subtract 1 from #lower
    end-while
    let $return_value = array.return_value(#lower)
  else
    let $return_value = ''
  end-if
end-procedure binary_effdt_search

Looking Ahead

We can’t talk about sorting and searching in SQR without a brief mention of the Load-Lookup command.  This is one of my favorite features of the SQR language.  Although Valentine’s Day is over, next week I’ll begin a four part mini-series, “Load-Lookup Love Letter.”

Brain Teaser

Meanwhile, here is a brainteaser.  Please post solutions as comments.

After importing a flat file to PS_TL_RAPID_TIME, we want to report on the results.  We could have counters and accumulators in the SQR program, but we want to report on what actually made it to the database.  This code uses the “select and print” feature of SQR, which is usually for columnar output, to print a footnote (x accepted entries with $y).  Then it prints the second footnote (x rejected entries with $y) conventionally.  What is the logical error in this code?

begin-select
count(ST_INSTANCE)              (+2, 1) edit 999
'accepted entries with'         (  ,+1)
sum(TL_QUANTITY)                (  ,+1) edit $99,999.99
  print #num_rejected           (+1, 1) edit 999
  print 'rejected entries with' (  ,+1)
  print #amt_rejected           (  ,+1) edit $99,999.99
 from PS_TL_RAPID_TIME
where ST_INSTANCE = #st_instance
end-select

2 Comments

  1. Nathan says:

    I’m not familiar with the PS_TL_RAPID_TIME table, but it seems likely that if all the records in the flat file were rejected, then there would be no data returned from the select statement. In that case, neither of the notes would print (even though presumably there were some number of records rejected that you would have wanted to know about)….