## 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.

- If there are no earning codes, the dynamic variable should be a null string, otherwise:
- Begin the string with “and ERNCD in (“.
- Starting with the second earning code, put a comma before the earning code.
- Put each earning code in quotes.
- 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

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.

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.

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)

ELSE

Do Print-Failure(2)

END-IF

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

#IF a=a

Do Print-Failure(1)

#ELSE

Do Print-Failure(2)

#END-IF

…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

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.