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.


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


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
  let $type = 'DR'

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


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 = ''
  let $clause = $clause || cond($clause = '', 'and ERNCD in (‘, ',') || '''' || &ERNCD || ''''

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
if isnull($address1)
  move ' ' to $address1

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”

Or like this.

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


  1. Bob Josephson says:

    I don’t know about SQR, but in C, floor is different from trunc (or C’s equivalent of trunc). floor(-1.5) returns -2, whereas trunc(-1.5) returns -1.

    • administrator says:

      It’s the same in SQR. My description of floor was correct and my description of trunc was correct, but my statement that floor was a special case of trunc was wrong for negative numbers. Thanks for the correction.

  2. Florian Böttcher says:

    I have a Problem understanding an existing SQR-Code, I would be very glad if you could help me:

    Where is the difference between:

    If a=a
    Do Print-Failure(1)
    Do Print-Failure(2)

    …and an IF with a hashmark, like the following:

    #IF a=a
    Do Print-Failure(1)
    Do Print-Failure(2)

    …its only a small example of me problem.
    Please tell me whats the difference and in which cases you use a hashmark…

    Please make me happy ;)

    • administrator says:

      Great question! Both examples will PERFORM the same way, executing the “Do Print-Failure(1)” command, but they get to that point quite differently. Running an SQR program is usually a two step process. First, the SQR interpreter (SQRW.EXE or PSSQR.EXE) reads the program and translates it into a form that is easier and faster to execute. Second, the SQR interpreter executes that form.

      Your second example, with the hash marks, indicate that the SQR interpreter should not translate all your code. The translator evaluates the “a=a” expression and determines that it is true. It translates the “Do Print-Failure(1)” command and ignores the “Do Print-Failure(2)” command. The translation will be the same as if you had just coded “Do Print-Failure(1).”

      Your first example translates all five lines of code. When we get to the second stage of executing code, the SQR interpreter will evaluate the IF statement every time you pass through this code, perform the first branch, “Do Print-Failure(1)”, and skip over the rest of the translated code (”Do Print-Failure(2)”).

      In the first example, the translation takes a little longer because it translates five lines instead of one, and the execution takes a little longer because it has to test the expression “a=a” and branch appropriately.

      The “#IF” command is a directive to the code translator. The “IF” command is a directive to the code executor. As for usage:

      - #IF can only evaluate simple expressions; complex expressions require IF.
      - #IF is good if you have to choose between two blocks of code and SQR won’t let you compile both. For example, two different BEGIN-SELECT blocks that use the same aliases.
      - #IF is good if you have a piece of code that is not a full, stand-alone command, and you might or might not want to use it. For example: (1) part of a WHERE clause, (2) fields in a CREATE-ARRAY command.