Table Driven SQR

Table-Driven programming is a technique that can make a program more easy to write or less easy to write, more clear or less clear, more maintainable or less maintainable.  It can improve a program when we use it appropriately.

What Is Table-Driven Programming?

There are many articles on the Internet about table-driven programming and data-driven programming, but I didn’t find a definition I liked.  Some authors formed complicated sentences packed with computer science buzzwords.  Other authors said, “It’s like this,” and jumped into examples immediately.  If I had known I would have to define it myself, I would have given myself more time to write this blog entry.

Almost all programs process data and changes in the input data usually cause changes in the output data – that’s not being table-driven.  Table-driven programming is a variety of techniques for allowing the contents of one or more database table(s) to affect some aspects of processing the contents of one or more other database table(s).  The goals of table-driven programming are:

  • Reduce the need to update the program to accommodate changes in the data.
  • Simplify the program by generalizing the way it handles specific data values.
  • Allow functional users to configure the program to adapt to new business requirements, as they do with the Peoplesoft setup tables.

Value Lists

If we want to calculate pay, we multiply hours worked by hourly pay rate for regular pay.  But we pay “time and one half” for overtime pay.

evaluate &PS_PAY_OTH_EARNS.ERNCD
when = 'REG'
let #pay = &PS_PAY_OTH_EARNS.OTH_HRS * &PS_JOB.HOURLY_RT
when = 'OVT'
let #pay = 1.5 * &PS_PAY_OTH_EARNS.OTH_HRS * &PS_JOB.HOURLY_RT
end-evaluate

An organization might have over one hundred earnings codes, giving us a bulky evaluate command that requires maintenance every time there’s a new code.  (Doing data entry through the compiler.)

One solution is to create a list of earning codes for each formula.  The hours times earnings formula applies to regular pay, vacation pay, sick pay, and many others.  There might be a few variations of overtime that qualify for “time and one half” pay.  We can write something like this:

let $sp_erncd_sp = ' ' || &ERNCD || ' '
if instr(' REG VAC SIC ', $sp_erncd_sp, 1)
let #pay = &PS_PAY_OTH_EARNS.OTH_HRS * &PS_JOB.HOURLY_RT
end-if
if instr(' OVT OV1 OV2 ', $sp_erncd_sp, 1)
let #pay = 1.5 * &PS_PAY_OTH_EARNS.OTH_HRS * &PS_JOB.HOURLY_RT
end-if

This is more compact, but still requires lots of maintenance and it’s not table-driven programming.

Item 7 in my May 3, 2009 blog entry “7 Little SQL Tricks” described value lists.  Value lists consist of a parent table with rows that name and describe the lists and a child table with the items on the lists.

begin-select
LIST_ID
VALUEGROUP
evaluate &LIST_ID
when = 'PAY1.0'
let $pay1.0 = $pay1.0 || ' ' || &VALUEGROUP || ' '
when = 'PAY1.5'
let $pay1.5 = $pay1.5 || ' ' || &VALUEGROUP || ' '
end-evaluate
from PS_TL_VAL_LIST_DTL
where LIST_ID like 'PAY%'
end-select

let $sp_erncd_sp = ' ' || &ERNCD || ' '
if instr($pay1.0, $sp_erncd_sp, 1)
let #pay = &PS_PAY_OTH_EARNS.OTH_HRS * &PS_JOB.HOURLY_RT
end-if
if instr($pay1.5, $sp_erncd_sp, 1)
let #pay = 1.5 * &PS_PAY_OTH_EARNS.OTH_HRS * &PS_JOB.HOURLY_RT
end-if

Now, our program doesn’t need to change when the Payroll department creates a new earning code.  But what if they create a new category; an earnings code that pays double time?  We would have to create a new value list “PAY2.0″, and add a branch to the evaluate command, and add a new if command.

We could take this approach further by parsing the LIST_ID value; extracting the characters after “PAY”, converting them to a number, and using that number as a factor in calculating #pay.  That would be ignoring a better resource and a better form of table-driven programming.

Parameter Tables

The PS_EARNINGS_TBL contains 62 columns, including the flags and quantities that define earnings codes.  An earnings code can have a FACTOR_MULT with a number like 1.0, 1.5, or anything else, to be applied to the product of hours and hourly rate.  It can have a PERUNIT_OVR_RT (per unit override rate), to be applied to the number of hours, which are really something else in these case (like dollars reimbursed or miles driven), without the hourly rate.  It can have EARN_FLAT_AMT, to be paid instead of the product of hours and hourly rate.

Instead of creating and maintaining value lists, we can refer to the PS_EARNINGS_TBL to decide which formula to use and which factor to apply.

lookup earnings &ERNCD $earnings_data
unstring $earnings_data by '@' into $payment_type $factor_mult $perunit_ovr_rt
move $factor_mult to #factor_mult
move $perunit_ovr_rt to #perunit_ovr_rt
evaluate $payment_type
when = 'A'
let #pay = &OTH_HRS
when = 'B'
when = 'E'
when = 'H'
let #pay = #factor_mult * &OTH_HRS * &HOURLY_RT
when = 'U'
let #pay = #perunit_ovr_rt * &OTH_HRS
end-evaluate

Exception Tables

I wrote an interface from the Peoplesoft Time & Labor and Payroll modules to a third party General Ledger system.  It identified the time card entries that were the sources of paycheck earnings.  It charged the OTH_EARNS from the paychecks to the ACCT_CD from the time cards.  However, it needed to make exceptions for some earning codes, overriding the time cards and charging to predefined account codes depending on the departments.

The previous version of the interface had an evaluate command based on the earning code, ERNCD.  Each value of ERNCD had its own evaluate command based on the first two digits of the department number, $dept2.  Some departments had their own account code and other departments shared a common code.

I replaced the ever-growing, two-level evaluate structure with a new table of three fields, ERNCD, DEPT2, and ACCT_CD, and ran load-lookup on that table.  This is the code that handled the changes to $acct_cd.

let $new_acct_cd = ''
evaluate ''
when = $new_acct_cd
let $key = $erncd || $dept2
lookup acct_cd $key $new_acct_cd
when = $new_acct_cd
let $key = $erncd || 'XX'
lookup acct_cd $key $new_acct_cd
when <> $new_acct_cd
move $new_acct_cd to $acct_cd
let #pos = instr($acct_cd, 'XX', 1)
if #pos > 0
let $acct_cd = substr($acct_cd, 1, #pos - 1) || $dept2 || substr($acct_cd, #pos + 2, 100)
end-if
end-evaluate

We set $new_acct_cd to the null string.  Evaluate tests the null string against $new_acct_cd.  They are equal, so the first when branch executes.  We lookup a key based on the earning code and the first two digits of the department.  If there is an exception, we get the account code.  If not, $new_acct_cd is set to the null string again.

Next, evaluate tests the null string against $new_acct_cd again.  If we didn’t have an exception before, they will be equal and the second when branch executes.  We perform another lookup, with the department set to “XX”, which I use to indicate “the rest of the organization.”  If there is an exception, we get the account code.  If not, $new_acct_cd is set to the null string again.

Finally, evaluate tests the null string against $new_acct_cd again, but this time the when branch executes only if they are unequal; we’ve found an exception.  We move the exception account code to $acct_cd.  Then we search for an “XX” within the account code.  If there is one, we replace it with the first two digits of the department.

The Limits Of Table-Driven Programming

I opened with a provocative and equivocal statement that “Table-Driven programming is a technique that can make a program more easy to write or less easy to write, more clear or less clear, more maintainable or less maintainable.”

Table-Driven programming requires an infrastructure.  Before we can use value lists, we have to create a parent table, a child table, a data entry page, a component, a menu item, and portal objects.  For each new list, we have to enter the values.  Our program needs a begin-select command for the list, although we could write a standard procedure in an include file for that.

We can use an existing parameter table with no more than a begin-select or load-lookup command, but there’s more work if we need a new column, and much more work if we need a new table.  Exception tables are usually new development and sometimes require thoughtful design.

When we’re choosing between a small number of hard coded literals versus the infrastructure a new table requires, table-driven programming is less easy.  If we face a large number of hard coded literals or the prospect of frequent change, table-driven programming is more easy.

Clarity is also a matter of degree or scale.  It’s easy to see what we’re doing with a small number of literals used simply.  It’s hard to see what we’re doing when the literal values are removed from the program and read from a table.  We view programs with an editor, we view tables with a SQL browser.  Every additional window on our monitor is another blow to clarity.  The code to import and manage table-driven data does not contribute directly to goals of the program.  If it’s too complex, it reduces clarity.

Table-driven programming is based on a prediction of the kind of flexibility the program will need.  It is an investment of extra work, compared to hard coding a few literals, in the hope that future business requirements can be supported with little or no further development.  The risk is with the unpredictable requirement.  Suppose we have a table-driven system that varies the program’s behavior with the employee’s home state.  It works fine as we add employees in one new state after another, until we reach a state that requires different treatment for each city.  It might be easier to modify the program for that exception in a hard coded evaluate command than in a sleek table-driven design.

Finally, no program can be completely table-driven, eliminating all literal values.  We have to hard code the name of the value list or the name of the parameter table.  The program needs to know where to look for the driving data and how to use it.

7 Comments

  1. Anand says:

    I love the site very much, its keeps interesting me to learn sqr more..
    I have the Issue with the page no… My pdf(single) file has 2reports I would like insert the page no on each page in footer.. I would like to get the page No like this 1-26 and 1-34 it differ accordingly..
    when i use the statement

    begin-footing f1
    page-number(1) ’Page ’
    last-page () ’ of ’
    end-footing

    In the middle i have altered the #page-count to 1 for second part of the report
    here the last-page return page 1 of 60 (for first part) and page 1 of 60 ( for second part) instead I would like to get
    page 1 of 26 and page 1 of 34 (in same pdf file).

    by alternative.. I tried to using simple print statement with delay in footer and after the end-select i used set-print-delay for total page but It doesnt work.. How to solve it..suggest me any idea. to find total page.. (since page count may vary)

    thanks in Advance
    AnandhRaj

    • administrator says:

      Hi Anand,

      Thank you for your kind words. I’m glad you like my blog.

      You have discovered something about the last-page command that I didn’t know. It doesn’t use the #page-count variable. It has some other counter, which may or may not be available to us to inspect or alter. I don’t think the multiple report option will help – it requires you to put each report in its own file. You could experiment with other variable names in hope of finding an undocumented variable: #page-count2, #page-counter, #last-page, etc. You could loop through your report generation twice; first to get the page counts in throw-away files, then to print again with your own #last-page numbers. Another possibility to print separate files and use the call system command with a utility concatenate them – type “concatenate pdf files” into Google for ideas.

      You may get a better answer on the http://www.sqrug.org discussion group. Good luck.

  2. Anand says:

    Hi,

    How to get the fractional second in SQR.

    I used different format like

    date-time () ‘DD-MM-YYYY HH:Mi:SS:FF’
    date-time () ‘DD-MM-yyyy HH:MI:SS:NN’

    Its shows the error date format is not correct.. Suggest me any Idea to find it.

    Thanks in Advance,
    AnandhRaj

    • administrator says:

      Hi Anand,

      The SQR Language Reference covers edit masks in the section about the print command. It says you can use “N” for fractions. I haven’t tried it myself, but I think you need to use the decimal point (in USA) or decimal comma (other countries), not the colon. Try ‘DD-MM-YYYY HH:MI:SS.NN’. The manual also warns us that SQR tries to be precise to microseconds (1/1,000,000) but most hardware and databases will not supply that level of accuracy. Good luck.

  3. Anand says:

    Thanks,

    I tried using date-time () for Fractional second. it doesnt work.
    Instead I used
    print $current-date (+1) edit ‘DD-MON-YYYY HH24:Mi:SS:NN’
    Its works fine.

    • administrator says:

      The SQR Language Reference recommends that you don’t use the date-time command. It also mentions that if you use it for printing, it will return the date and time that the program started, not the date and time that the command executed. I’m curious, why do you need to print fractions of a second?

  4. Anand says:

    Ya, I got what you are saying…

    Thanks
    AnandhRaj