SQR Functions

The SQR language has several families of built-in functions. They range from minor conveniences to potential life savers. There are some surprising inclusions and surprising omissions. I discuss some of the functions I wish we had in An SQR Wish List.

Math Functions

The round and trunc (truncate) functions give us control over fractions. Both of them keep the integer portion of a number and some of the decimal places. Both of them replace the least significant decimal places with zero. Round might increment the nth decimal place, depending on the (n+1)th decimal place; trunc never does. Unfortunately, we cannot round to a negative number of decimal places; -2 won’t round to the nearest hundred.

Floor is a special case of trunc where the number of decimal places is zero. It returns an integer, although it supports polymorphism (object oriented SQR?) by returning the same numeric type as its parameter. (As do round and trunc.)

Ceil (ceiling) is the opposite of floor, returning the minimum integer greater than the parameter, rather than the maximum integer less than the parameter. It’s useful with negative numbers or in touchy situations, e.g. “round an employee’s pay (in pennies) to the penny, but never downward.” There isn’t a general version of ceil, like trunc, for any number of decimal places, but we could write:

let #general_ceil = -trunc(-#amount, #decimal_places)

If we need the fraction rather than the integer, the answer is the mod function or the modulus operator (%).

let #fraction = mod(#amount, 1)

Mod can control cyclical functions. If we’re reading thousands of rows and want to supply periodic status reports, we can write this.

begin-select
…
  add 1 to #num_rows
  if mod(#num_rows, 1000) = 1
    show #num_rows edit 999,999 ' rows read so far'
  end-if
…
end-select

I learned modular arithmetic as applied to integers, but SQR is more flexible. We can even use a fractional or negative modulus. mod(9, 2.5) is 1.5. mod(9, -2.5) is also 1.5 because 9 = -2.5 * -3 (an integer) + 1.5.

Abs (absolute value) ensures we have a positive number, but it does not leave a trace of what it did or didn’t do. When I was exporting payroll data to general ledger, I found it better to write:

if #amount < 0
  let $type = 'CR'
  let #amount = -#amount
else
  let $type = 'DR'
end-if

If I were working in C or Java and wanted to branch on whether a number was positive, negative, or zero, I would use sign (or switch to Fortran II).

sign_amt = sign(amt);
switch (sign_amt)
{
  case -1:
…
  case 0:
…
  case 1
…
};

But SQR evaluate is more powerful than switch, eliminating the need for sign in this situation.

evaluate #amt
  when < 0
…
  when = 0
…
  when > 0
…
end-evaluate

After this, the SQR math functions get more advanced, with square roots, trigonometry, hyperbolic trigonometry, logarithms, and exponents. I’ve tried, and failed, to imagine the customer who requested these features in a database reporting language. I suspect that once the hard compiler programming was done to parse functions, evaluate parameters, manage the stack, and call the C standard library, it was a breeze to add these functions whether they were needed or not.

On the other hand, SQR does not contain financial and business functions like internal rate of return, yield to maturity, loan payments, present value, future value, depreciation, economic order quantity, Black-Scholes option valuation, etc.

Date Functions

I discussed date variables and date functions in the SQR Dates article. Dates are stored as strings (this article was published on ‘20091011’) and processed like strings in the datetostr, strtodate, and edit functions. On the other hand, they are processed mathematically with complex formulae in the dateadd, datediff, and edit functions.

String Functions

I use instr, substr, length, and edit most frequently. SQR has several variations of the first three to handle multibyte characters. My second tier tools are the lower, upper, ltrim, rtrim, lpad, and rpad. I nominate roman for the “hyperbolic arctangent” award for coolest, least expected function. Sure, people still use Roman numerals for clockfaces, movie copyrights, and table of contents pages, but do we need a dedicated function in addition to the Roman numeral edit mask?

Logic Functions

I use these functions to streamline my program flow. Cond and nvl replace if-else-endif blocks. Range performs two comparisons at once.

One use for cond is to build strings that contain lists, e.g. a dynamic variable for a clause like “and ERNCD in (‘REG’,’VAC’,’SIC’)”. Here are the rules.

1. If there are no earning codes, the dynamic variable should be a null string, otherwise:
2. Begin the string with “and ERNCD in (“.
3. Starting with the second earning code, put a comma before the earning code.
4. Put each earning code in quotes.
5. End the string with “)”.

Here’s the code with cond.

let $clause = ''
begin-select
ERNCD
  let $clause = $clause || cond($clause = '', 'and ERNCD in (‘, ',') || '''' || &ERNCD || ''''
…
end-select
let $clause = $clause || cond($clause = '', '', ')')

Null values are no-nos for Peoplesoft character fields, so we can use nvl to replace them with spaces before inserting or updating rows. If we have fifty columns to write, it’s easier to program this:

let $address1 = nvl($address1, ' ')
let $address2 = nvl($address2, ' ')

than this:

if isnull($address1)
  move ' ' to $address1
end-if
if isnull($address1)
  move ' ' to $address1
end-if

One use for range is to deal with fuzzy equalities. After a string of calculations, two floating point values might be very close, but the vagaries of binary representation might make them not quite equal. The answer is to test them like this.

if range(#a - #b, -0.001, 0.001)
  ! they are “equal”
end-if

Or like this.

if abs(#a - #b) < 0.001
  ! they are “equal”
end-if