Evaluate Two Values At Once In SQR

Last year I wrote about the similarity and superiority of the SQR evaluate command compared to the C or Java switch command in SQR Evaluate Versus C/Java Switch, part 1 and part 2. Here is another way to extend evaluate functionality.

Evaluate Two Values

Evaluate takes one value and compares it to one or more other values.  Sometimes we need to perform different operations depending on two values.  Consider two columns of the PS_EMPLOYEES table, FULL_PART_TIME, which can be “F” or “P”, and REG_TEMP, which can be “R” or “T.”  There are four combinations of values that might require four different actions.  We can control program flow like this:

if &FULL_PART_TIME = 'F'
  if &REG_TEMP = 'R'
    ! branch 1
  else
    ! branch 2
  end-if
else
  if &REG_TEMP = 'R'
    ! branch 3
  else
    !branch 4
  end-if
end-if

or not improve it like this:

evaluate &FULL_PART_TIME
  when = 'F'
    evaluate &REG_TEMP
      when = 'R'
        ! branch 1
      when = 'T'
        ! branch 2
    end-evaluate
  when = 'P'
    evaluate &REG_TEMP
      when = 'R'
        ! branch 3
      when = 'T'
        ! branch 4
    end-evaluate
end-evaluate

We can remove a level of nesting and complexity with this:

let $key = &FULL_PART_TIME || &REG_TEMP
evaluate $key
  when = 'FR'
    ! branch 1
  when = 'FT'
    ! branch 2
  when = 'PR'
    ! branch 3
  when = 'PT'
    ! branch 4
  when-other
    ! invalid values
end-evaluate

When-Other, Whatever

I’m confident that these two columns from the PS_JOB table, via the PS_EMPLOYEES table,  will have values (Peoplesoft character fields are always defined as non-null) and they will have the predicted values (as enforced by the PIA page processor).  I use a when-other branch because I don’t want the program to proceed heedlessly when it turns out that I was overconfident.  How could that happen?

  • Someone updates a PS_JOB or PS_EMPLOYEES row in SQL, bypassing Peoplesoft validation.
  • The where clause of my PS_EMPLOYEES selection doesn’t return any rows, leaving the column variables set to null.
  • The latest release of Peoplesoft HCM introduces new valid values for these columns.

About This Post

Last time I wrote about evaluate, it took 2400 words and two weeks to express all my thoughts.  A while later, I had the afterthought you’ve just read.  I saved it, waiting until I had enough material for a full sized post.  I did have more to write about evaluate, but it was in the nature of “here’s a farfetched use of evaluate, but it would be unwise to do it.”  As a result, I withheld this idea too.  This year, I will allow myself to publish shorter posts, which will give us new topics on which I have a bit, but not a lot, to say.

Comments are closed.