LET

Syntax

LET dst_var=expression

Description

Assigns the value of an expression to a string, numeric, or date variable.

Valid expressions are formed as a combination of operands, operators, and functions. String, numeric, date, and array field operands can be used in an expression and embedded functions. SQR supports a standardized set of mathematical operators and logical comparison operators working within a carefully defined set of precedence rules. SQR also provides a rich set of mathematical, string, date, and file manipulation functions along with a number of special purpose utility functions. All combined, the SQR expression provides a powerful tool that can be tailored to suit any information processing need. The following detail outlines the specific behavior of each expression component: (1) the operand, (2) the operator, and (3) the function.

Parameters

Parameter Description

dst_var

A string, numeric, or date variable or array field to which the result of the expression is assigned.

expression

The expression to evaluate.

Example

These examples show some complex expressions: 

let #j = ((#a + #b) * #c) ^ 2
if #j > 2 and sqrt(#j) < 20 or #i + 2 > 17.4
while upper(substr(&descrip,1,#j+2)) != 'XXXX' 

and not isnull(&price)
let #len = length(&fname || &initial || &lname) + 2
let $s = edit(&price * &rate, '99999.99')
let summary.total(#j) = summary.total(#j) + (&price * &rate)
if summary.total(#j) > 1000000
let store.total (#store_id, #dept)

   = store.total (#store_id, #dept) + #total
let #diff = datediff(datenow(), strtodate('1995','YYYY'),'day')
let $newdate = dateadd(datenow(),'month',50)
let $date1 = datetostr(strtodate(&sale_date), 'Day Month DD, YYYY')

SQR analyzes LET, IF, and WHILE expressions when it compiles your code and saves the result in an internal format so that repetitive execution is at maximum speed.

Operands

Operands form the backbone of an SQR expression. Operands do not have to be the same type. You can combine string, numeric, and array field operands to form a valid expression. SQR performs a sequence of automatic operand conversions when it evaluates expressions that contain dissimilar operand types. As the expression is evaluated, operands of lower precision are converted to match the operand of higher precision. Consider the following code example: 

let  #answer = #float * #decimal  / #integer

Because the multiply and divide operators are equal in precedence, the expression is evaluated as (#float * #decimal) / #integer. Working from the inside out, the #float variable is converted to a decimal type in which a multiply is performed yielding the simplified expression (#decimal)/#integer. SQR now converts the #integer operand to a decimal type before performing the final divide. When finished with the expression evaluation, SQR converts the result to match the type of the #answer variable.

Converting operands of lower precision to operands of higher precision preserves the number of significant digits. The number of significant digits is not lost when an integer is converted to float or decimal. In a similar manner, the number of significant digits is preserved when floating point operands are converted to the decimal type. The number of significant digits is sacrificed only when the final result is converted to match the type of the #answer variable and this variable is less precise than the highest of the operands being evaluated. In the example, precision is not lost if the #answer is declared as a decimal type. SQR considers integer variables as the lowest in the precision hierarchy, followed by float and then decimal.

Here are a few simple expression examples: 

let #discount  =  round (&price * #rate / 100, 2)
let $name  =  $first_name || ' ' || $last_name
let customer.total (#customer_id) =

   customer.total (#customer_id) + #invoice_total
if not range(upper($code), 'A', 'G')
   ...processing when out of range...
let store.total (#store_id, #qtr) =

   store.total (#store_id, #qtr) + #invoice_total
let $date1 = strtodate ('Apr 10 2004', 'MON DD YYYY')

The following sections list operators and functions supported in expressions.

Operators

Operators of the same precedence are processed in the sequence in which they appear in the expression, from left to right. Use parentheses to override the normal precedence rules. All numeric types (decimal, float, integer) are supported for all operators.

This table lists operators in descending order of precedence (operators listed in the same row within the table have the same precedence):

Operator Explanation

||

Concatenate two strings or dates

+, -

Sign prefix (positive or negative)

^

Exponent

*, /, %

Multiply, divide, remainder: a % b = mod(a,b) for integers

+, -

Plus, minus

Note: SQR distinguishes between a sign prefix and arithmetic operation by the context of the expression.

>, <, >=, <=, <>, !=, =

Comparison operators: greater than, less than, greater or equal to, less than or equal to, not equal to (!= or <>), equal to.

not

Logical NOT

and

Logical AND

or, xor

Logical OR, XOR (exclusive OR)

Functions

This section lists numeric, file-related, and miscellaneous functions. The functions are listed in alphabetical order.

Function arguments are enclosed in parentheses and can be nested. Arguments referenced as x, y, or z indicate the first, second, or third argument of a function. Otherwise, functions take a single argument or no arguments. All arguments are evaluated before a function is evaluated.

Not all functions support all numeric types (decimal, float, integer). Certain functions do not support the decimal type directly, but convert input decimal operands to the float type before the function is evaluated. The following table annotates the functions that directly support the decimal type and the ones that do not.

Use parentheses to override the normal precedence rules.

This table describes numeric functions:

Function Description 
abs

Returns the absolute value of num_value. This function returns a value of the same type as num_value.

Syntax: 

dst_var = abs(num_value)

num_value = decimal, float, or integer literal, column, variable, or expression.

dst_var = decimal, float, or integer variable.

Example: 

let #dabsvar = abs(#dvar) 
acos

Returns the arccosine of num_value in the range of 0 to p radians. The value of num_value must be between –1 and 1. This function returns a float value.

Syntax: 

dst_var = acos(num_value)

num_value = decimal, float, or integer literal, column, variable, or expression. The value is always converted to float.

dst_var = decimal, float, or integer variable.

Example: 

let #facosvar = acos(#fvar) 
asin

Returns the arcsine of num_value in the range of –p/2 to p/2 radians. The value of num_value must be between –1 and 1. This function returns a float value.

Syntax: 

dst_var = asin(num_value)

num_value = decimal, float, or integer literal, column, variable, or expression. The value is always converted to float.

dst_var = decimal, float, or integer variable.

Example: 

let #fasinvar = asin(#fvar) 
atan

Returns the arctangent of num_value in the range of –p/2 to p/2 radians. The value of num_value must be between –1 and 1. This function returns a float value.

Syntax: 

dst_var = atan(num_value)

num_value = decimal, float, or integer literal, column, variable, or expression. The value is always converted to float.

dst_var = decimal, float, or integer variable.

Example: 

let #fatanvar = atan(#fvar) 
ceil

Returns a value representing the smallest integer that is greater than or equal to num_value. This function returns a value of the same type as num_value.

Syntax: 

dst_var = ceil(num_value)

num_value = decimal, float, or integer literal, column, variable, or expression.

dst_var = decimal, float, or integer variable.

Example: 

let #fceilvar = ceil(#fvar)
 
cos

Returns the cosine of num_value. This function returns a float value.

Syntax: 

dst_var = cos(num_value)

num_value = decimal, float, or integer literal, column, variable, or expression. The value is always converted to float.

dst_var = decimal, float, or integer variable.

Example: 

let #fcosvar = cos(#fvar) 
cosh

Returns the hyperbolic cosine of num_value. This function returns a float value.

Syntax: 

dst_var = cosh(num_value)

num_value = decimal, float, or integer literal, column, variable, or expression. The value is always converted to float.

dst_var = decimal, float, or integer variable.

Example: 

let #fcoshvar = cosh(#fvar) 
deg

Returns a value expressed in degrees of num_value, which is expressed in radians. This function returns a float value.

Syntax: 

dst_var = deg(num_value)

num_value = decimal, float, or integer literal, column, variable, or expression. The value is always converted to float.

dst_var = decimal, float, or integer variable.

Example: 

let #fdegvar = deg(#fvar) 
e10

Returns the value of 10 raised to num_value. This function returns a float value.

Syntax: 

dst_var = e10(num_value)

num_value = decimal, float, or integer literal, column, variable, or expression. The value is always converted to float.

dst_var = decimal, float, or integer variable.

Example: 

let #fe10var = e10(#fvar) 
exp

Returns the value of e raised to num_value. This function returns a float value.

Syntax: 

dst_var = exp(num_value)

num_value = decimal, float, or integer literal, column, variable, or expression. The value is always converted to float.

dst_var = decimal, float, or integer variable.

Example: 

let #fexpvar = exp(#fvar) 
floor

Returns a value representing the largest integer that is less than or equal to num_value. This function returns a value of the same type as num_value.

Syntax: 

dst_var = floor(num_value)

num_value = decimal, float, or integer literal, column, variable, or expression.

dst_var = decimal, float, or integer variable.

Example: 

let #ffloorvar = floor(#fvar) 
log

Returns the natural logarithm of num_value. This function returns a float value.

Syntax: 

dst_var = log(num_value)

num_value = decimal, float, or integer literal, column, variable, or expression. The value is always converted to float.

dst_var = decimal, float, or integer variable.

Example: 

let #flogvar = log(#fvar) 
log10

Returns the base-10 logarithm of num_value. This function returns a float value.

Syntax: 

dst_var = log10(num_value)

num_value = decimal, float, or integer literal, column, variable, or expression. The value is always converted to float.

dst_var = decimal, float, or integer variable.

Example: 

let #flog10var = log10(#fvar) 
mod

Returns the fractional remainder, f, of x_value/ y_value such that x_value = i * y_value + f, where i is an integer, f has the same sign as x_value, and the absolute value of f is less than the absolute value of y_value. The arguments are promoted to the type of the greatest precision and the function returns a value of that type.

Syntax: 

dst_var = mod(x_value, y_value)

x_value = decimal, float, or integer literal, column, variable, or expression.

y_value = decimal, float, or integer literal, column, variable, or expression.

dst_var = decimal, float, or integer variable.

Example: 

let #fmodvar = mod(#fxvar, #fyvar)
 
power

Returns the value of x_value raised to the power of y_value. This function returns a float value.

Syntax: 

dst_var = power(x_value, y_value)

x_value = decimal, float, or integer literal, column, variable, or expression. The value is always converted to float.

y_value = decimal, float, or integer literal, column, variable, or expression. The value is always converted to float.

dst_var = decimal, float, or integer variable.

Example: 

let #fpowervar = power(#fxvar, #fyvar)
 
rad

Returns a value expressed in radians of num_value, which is expressed in degrees. This function returns a float value.

Syntax: 

dst_var = rad(num_value)

num_value = decimal, float, or integer literal, column, variable, or expression. The value is always converted to float.

place_value = decimal, float, or integer literal, column, variable, or expression. The value is always converted to float.

dst_var = decimal, float, or integer variable.

Example: 

let #fradvar = rad(#fvar) 
round

Returns a value that is num_value rounded to place_value digits after the decimal separator. This function returns a value of the same type as num_value.

Syntax: 

dst_var = round(num_value, place_value⇒
)

num_value = decimal, float, or integer literal, column, variable, or expression.

place_value = decimal, float, or integer literal, column, variable, or expression. The value is always converted to float.

dst_var = decimal, float, or integer variable.

Example: 

let #frndvar = round(#fvar, #fplace) ⇒
(#x, #y)
 
sign

Returns a –1, 0, or +1 depending on the sign of num_value. This function returns a float value.

Syntax: 

dst_var = sign(num_value)

num_value = decimal, float, or integer literal, column, variable, or expression.

dst_var = decimal, float, or integer variable.

Example: 

let #fsignvar = sign(#fvar)
 
sin

Returns the sine of num_value. This function returns a float value.

Syntax: 

dst_var = sin(num_value)

num_value = decimal, float, or integer literal, column, variable, or expression. The value is always converted to float.

dst_var = decimal, float, or integer variable.

Example: 

let #fsinvar = sin(#fvar) 
sinh

Returns the hyperbolic sine of num_value. This function returns a float value.

Syntax: 

dst_var = sinh(num_value)

num_value = decimal, float, or integer literal, column, variable, or expression. The value is always converted to float.

dst_var = decimal, float, or integer variable.

Example: 

let #fsinhvar = sinh(#fvar) 
sqrt

Returns the square root of num_value. This function returns a float value.

Syntax: 

dst_var = sqrt(num_value)

num_value = decimal, float, or integer literal, column, variable, or expression. The value is always converted to float.

dst_var = decimal, float, or integer variable.

Example: 

let #fsqrtvar = sqrt(#fvar) 
tan

Returns the tangent of num_value. This function returns a float value.

Syntax: 

dst_var = tan(num_value)

num_value = decimal, float, or integer literal, column, variable, or expression. The value is always converted to float.

dst_var = decimal, float, or integer variable.

Example: 

let #ftanvar = tan(#fvar) 
tanh

Returns the hyperbolic tangent of num_value. This function returns a float value.

Syntax: 

dst_var = tanh(num_value)

num_value = decimal, float, or integer literal, column, variable, or expression. The value is always converted to float.

dst_var = decimal, float, or integer variable.

Example: 

let #ftanhvar = tanh(#fvar) 
trunc

Returns a value that is num_value truncated to place_value digits after the decimal separator. This function returns a value of the same type as num_value.

Syntax: 

dst_var = trunc(num_value, place_value⇒
⇒
)

num_value = decimal, float, or integer literal, column, variable, or expression.

place_value = decimal, float, or integer literal, column, variable, or expression. The value is always converted to float.

dst_var = decimal, float, or integer variable.

Example: 

let #ftruncvar = trunc(#fvar,⇒
 #fplace)

The transcendental functions sin, cos, tan, sinh, cosh, and tanh take their arguments in radians. The functions asin, acos, and atan return radian values. To convert from radians to degrees or degrees to radians, use the rad or deg functions as shown here:

let #x = sin(rad(45))            ! Sine of 45 degrees.
let #y = deg(asin(#x))           ! Convert back to degrees.

If arguments or intermediate results passed to a numeric function are invalid for that function, SQR halts with an error message.

For example, passing a negative number to the sqrt function causes an error. Use the cond function described in the Miscellaneous Functions table to prevent division by zero or other invalid function or operator argument values.

The following table lists file-related functions. These functions return 0 (zero) when successful; otherwise, they return the system error code.

Function Description

delete

Deletes the file filename. The function returns either a 0 (zero) to indicate success or the value returned from the operating system to indicate an error.

Syntax: 


                           stat_var = delete(filename)

filename = text literal, column, variable, or expression.

stat_var = decimal, float, or integer variable.

Example: 

let #fstatus = delete($filename)

exists

Determines whether the file filename exists. The function returns either a 0 (zero) to indicate success or the value returned from the operating system to indicate an error.

Syntax: 


                           stat_var = exists(filename)

filename = text literal, column, variable, or expression.

stat_var = decimal, float, or integer variable.

Example: 

let #fstatus = exists($filename)

rename

Renames old_filename to new_filename. The function returns either a 0 (zero) to indicate success or the value returned from the operating system to indicate an error.

Syntax: 

stat_var = rename(old_filename, new_filename)

old_filename = text literal, column, variable, or expression.

new_filename = text literal, column, variable, or expression.

stat_var = decimal, float, or integer variable.

Example: 

let #fstatus = rename($old_filename, $new_⇒
filename)

The following table lists miscellaneous functions. These functions return a string value unless otherwise indicated.

In these functions where a string argument is expected and a date variable, column, or expression is entered, SQR converts the date to a string according to the following rules:

  • For DATETIME columns and SQR DATE variables, SQR uses the format specified by the SQR_DB_DATE_FORMAT setting.

    If this has not been set, SQR uses the first database-dependent format as listed in the Default Database Formats table.

  • For DATE columns, SQR uses the format specified by the SQR_DB_DATE_ONLY_FORMAT setting.

    If this has not been set, SQR uses the format listed in the DATE Column Formats table.

  • For TIME columns, SQR uses the format specified by the SQR_DB_TIME_ONLY_FORMAT setting.

    If this has not been set, SQR uses the format as listed in the TIME Column Formats table.

Except where noted in an individual function, if a string variable, column, or expression is entered where a date argument is expected, the string must be in the format specified by the SQR_DB_DATE_FORMAT setting, one of the database-dependent formats listed in the Default Database Formats table, or the database-independent format 'SYYYYMMDD[HH24[MI[SS[NNNNNN]]]]'.

Function Explanation

array

Returns a pointer to the starting address of the specified array field. The value returned from this function can be used only by a user-defined function. See the routine printarray in the file UFUNC.C for complete instructions on how to use this function.

Syntax: 


                           array_var = array(array_name, field_name)

array_name = text literal, column, variable, or expression

field_name = text literal, column, variable, or expression

array_var = text variable

Example: 

let #fstatus = printarray(array('products',⇒
 'name'), 10, 2, 'c')

ascii

Returns the numeric value for the first character in str_value. This function returns a float value.

Syntax: 


                           ascii_var  = ascii(str_value)

str_value = date or text literal, column, variable, or expression

ascii_var = decimal, float, or integer variable

Example: 

let #fascii = ascii($filename)

asciic

Returns the numeric value for the first character (rather than byte) of the specified string.

Syntax: 


                           ascii_var  = asciic(str_value)

str_value = date or text literal, column, variable, or expression

ascii_var = decimal, float, or integer variable

Example: 

let #fascii = asciic($filename)

chr

Returns a string that is composed of a character with the numeric value of num_value.

Syntax: 


                           dst_var = chr(num_value)

num_value = decimal, float, or integer literal, column, variable, or expression. The value is always converted to float.

dst_var = text variable.

Example: 

let $svar = chr(#num)

cond

Returns y_value if the x_value is nonzero; otherwise, returns z_value. If y_value is numeric, the z_value must also be numeric; otherwise, date and textual arguments are compatible. If either the y_value or z_value is a date variable, column, or expression, a date is returned. The return value of the function depends on which value is returned.

Syntax: 


                           dst_var = cond(x_value, y_value, z_value)

x_value = decimal, float, or integer literal, column, variable, or expression. The value is always converted to float.

y_value = Any literal, column, variable, or expression

z_value = Any literal, column, variable, or expression

dst_var = Any variable

Example: 

let #avg = #total / cond(&rate != 0, &rate, 1)

dateadd

Returns a date after adding (or subtracting) the specified units to the date_value.

Syntax: 


                           dst_var = dateadd(date_value, units_value, ⇒
quantity_value)

date_value = date variable or expression.

units_value = text literal, column, variable, or expression. Valid units are 'year', 'quarter', 'week', 'month', 'day', 'hour', 'minute', and 'second'.

quantity_value = decimal, float, or integer literal, column, variable, or expression. The value is always converted to float.

dst_var = date variable

Example: 

let $date = dateadd($startdate, 'day', 7.5)

datediff

Returns the difference between the specified dates expressed in units_value. The function returns a float value. The result can be negative if the first date is earlier than the second date.

Syntax: 


                           dst_var = datediff(date1_value, date2_value, ⇒
units_value)

date1_value = date variable or expression.

date2_value = date variable or expression.

units_value = text literal, column, variable, or expression. Valid units are 'year', 'quarter', 'week', 'month', 'day', 'hour', 'minute', and 'second'

dst_var = decimal, float, or integer variable.

Example: 

let #diff = datediff($date1, $date2, 'hour')

datenow

Returns the current local date and time from the client machine.

Syntax: 


                           dst_var = datenow()

dst_var = date variable

Example: 

let $date = datenow()

datetostr

Converts the date date_value to a string in the format format_mask.

Syntax: 


                           dst_var = datetostr(date_value [, format_mask])

date_value = date variable or expression.

format_mask = text literal, column, variable, or expression. The keyword DATE can be used to specify the DATE-EDIT-MASK setting from the current locale. If this argument is not specified, the format specified by the SQR_DB_DATE_FORMAT setting is used. If this has not been set, the first database-dependent format listed in the Default Database Formats table is used.

dst_var = text variable

Example: let $formdate = datetostr($date, 'Day Mon DD, YYYY') let $localedate = datetostr($date, DATE)

edit

Formats source_value according to edit_mask and returns a string containing the result.

Syntax: 


                           dst_var = edit(source_value, edit_mask)

source_value = Any literal, column, variable, or expression

edit_mask = text literal, column, variable, or expression

dst_var = text variable

Example: ) 

let $phone  =  edit(&phone, '(xxx) xxx-xxxxx')⇒
 let $price = edit(#price, '999.99')⇒
 let $today = edit($date, 'DD/MM/YYYY'

getenv

Returns the value of the specified environment variable. If the environment variable does not exist, an empty string is returned.

Syntax: 


                           dst_var = getenv(env_value)

env_value = text literal, column, variable, or expression

dst_var = text variable

Example: 

let $myuser = getenv('USER')

instr

Returns the numeric position of sub_value in source_value or 0 (zero) if not found. The search begins at offset offset_value. This function returns a float value.

Syntax: 


                           dst_var = instr(source_value, sub_value, offset_⇒
value)

source_value = date or text literal, column, variable, or expression.

sub_value = text literal, column, variable, or expression.

offset_value = decimal, float, or integer literal, column, variable, or expression. The value is always converted to integer.

dst_var = decimal, float, or integer variable.

Example: 

let #offset = instr(&description, 'auto', 10)

instrb

Performs the same functionality as the instr function except that the starting point and returned value are expressed in bytes rather than in characters.

Syntax: 


                           dst_var = instrb(source_value, sub_value, offset_⇒
value)

source_value = date or text literal, column, variable, or expression.

sub_value = text literal, column, variable, or expression.

offset_value = decimal, float, or integer literal, column, variable, or expression. The value is always converted to integer.

dst_var = decimal, float, or integer variable.

Example: 

let #offset = instrb(&description, 'auto', 10)

isblank

Returns a value of 1 (one) if source_val is an empty string, null string, or composed entirely of white-space characters; otherwise, returns a value of 0 (zero).

Syntax: 


                           dst_var = isblank(source_value)

source_value = date or text literal, column, variable, or expression

dst_var = decimal, float, or integer variable

Example: 

let #blank = isblank(&description)

isnull

Returns a value of 1 (one) if source_val is null; otherwise, returns a value of 0 (zero).

Syntax: 


                           dst_var =  isnull(source_value)

source_value = date or text literal, column, variable, or expression

dst_var = decimal, float, or integer variable

Example: 

let #null = isnull($date)

length

Returns the number of characters in source_value.

Syntax: 


                           dst_var = length(source_value)

source_value = date or text literal, column, variable, or expression

dst_var = decimal, float, or integer variable

Example: 

let #length = length(&description)

Note: Oracle recommends that you use either the lengthp or lengtht function instead of the length function.

lengthb

(Multibyte versions of SQR only) Has the same functionality as the length function except that the return value is expressed in bytes rather than in characters.

Syntax: 


                           dst_var = lengthb(source_value)

source_value = date or text literal, column, variable, or expression

dst_var = decimal, float, or integer variable

Example: 

let #length = lengthb(&description)

Note: Oracle recommends that you use either the lengthp or lengtht function instead of the lengthb function.

lengthp

Returns the length of a given string in print positions.

Syntax: 


                           dst_var = lengthp(source_value)

source_value = date or text literal, column, variable, or expression

dst_var = decimal, float, or integer variable

Example: 

let #printlen = lengthp(&string)

lengtht

Returns the length of a given string in bytes when converted (transformed) to a specified encoding.

Syntax: 


                           dst_var = lengtht(source_value, encoding_value)

source_value = date or text literal, column, variable, or expression

encoding_value = text literal, column, variable, or expression

dst_var = decimal, float, or integer variable

Example: 

let #sjislen = lengtht($string, 'Shift-JIS')

lower

Converts the contents of source_value to lowercase and returns the result.

Syntax: 


                           dst_var = lower(source_value)

source_value = date or text literal, column, variable, or expression

dst_var = text variable

Example: 

let $lower = lower(&description)

lpad

Pads the source_value on the left to a length of length_value using pad_value and returns the result.

Syntax:


                           dst_var = lpad(source_value, length_value, pad_⇒
value)

source_value = date or text literal, column, variable, or expression.

length_value = decimal, float, or integer literal, column, variable, or expression. The value is always converted to integer.

pad_value = text literal, column, variable, or expression.

dst_var = text variable.

Example: 

let $lpad = lpad($notice, 25, '.')

ltrim

Trims characters in source_value from the left until a character is not in set_value and returns the result.

Syntax: 


                           dst_var = ltrim(source_value, set_value)

source_value = date or text literal, column, variable, or expression

set_value = text literal, column, variable, or expression

dst_var = text variable

Example: 

let $ltrim = ltrim(&description, '.')

nvl

Returns y_value if the x_value is null; otherwise, returns x_value. If x_value is numeric, y_value must also be numeric; otherwise, date and textual arguments are compatible. In any case, the x_value determines the type of expression returned. The return value of the function depends on which value is returned.

Syntax: 


                           dst_var = nvl(x_value, y_value)

x_value = Any literal, column, variable, or expression

y_value = Any literal, column, variable, or expression

dst_var = Any variable

Example: 

let $city = nvl(&city, '-- not city --')

If x_value is a date and y_value is textual, y_value is validated according to the following rules:

For DATETIME columns and SQR DATE variables, SQR uses the format specified by the SQR_DB_DATE_FORMAT setting, one of the database-dependent formats (see the Default Database Formats table), or the database-independent format 'SYYYYMDD[HH24[MI[SS[NNNNNN]]]]'.

For DATE columns, SQR uses the format specified by the SQR_DB_DATE_ONLY_FORMAT setting, or the format listed in the DATE Column Formats table.

For TIME columns, SQR uses the format specified by the SQR_DB_TIME_ONLY_FORMAT setting, or the format as listed in the TIME Column Formats table.

range

Returns a value of 1 (one) if x_value is between y_value and z_value; otherwise, returns a value of 0 (zer0). If the first argument is text or numeric, the other arguments must be of the same type. If the first argument is a date, the remaining arguments can be dates, text, or both. You can also perform a date comparison on a mix of date and text arguments, for example, where x_value is a date and y_value and z_value are text arguments. In a comparison of this sort, y_value must represent a date that is earlier than that of z_value.

Syntax: 


                           dst_var = range(x_value, y_value, z_value)

x_value = Any literal, column, variable, or expression

y_value = Any literal, column, variable, or expression

z_value = Any literal, column, variable, or expression

dst_var = decimal, float, or integer variable

Example: 

let #inrange = range(&grade, 'A', 'D')⇒
 let #inrange = range($date, $startdate,⇒
 $enddate)⇒
 let #inrange = range($date, $startdate, '15-Apr-⇒
04')⇒
 let #inrange = range(#price, #low, #high)

If x_value is a date and y_value and/or z_value is textual, then y_value and/or z_value is validated according to the following rules:

For DATETIME columns and SQR DATE variables, SQR uses the format specified by the SQR_DB_DATE_FORMAT setting, one of the database-dependent formats (see the Default Database Formats table), or the database-independent format 'SYYYYM­MDD[HH24[MI[SS[NNNNNN]]]]'.

For DATE columns, SQR uses the format specified by the SQR_DB_DATE_ONLY_FORMAT setting, or the format listed in the table DATE Column Formats.

For TIME columns, SQR uses the format specified by the SQR_DB_TIME_ONLY_FORMAT setting, or the format as listed in table TIME Column Formats.

replace

Inspects the contents of source_value and replaces all occurrences of from_string with to_string and returns the modified string.

Syntax: 


                           dst_var = replace(source_value, from_string, to_⇒
string)

source_value = date or text literal, column, variable, or expression

from_string = text literal, column, variable, or expression

to_string = text literal, column, variable, or expression

dst_var = text variable 

Example:
let $replaced = replace($paragraph, 'good',⇒
 'excellent')

roman

Returns a string that is the character representation of source_value expressed in lowercase roman numerals.

Syntax: 


                           dst_var = roman(source_value)

source_value = text literal, column, variable, or expression

dst_var = text variable

Example: 

let $roman = roman(#page-count)

rpad

Pads the source_value on the right to a length of length_value using pad_value and returns the result.

Syntax: 


                           dst_var = rpad(source_value, length_value, pad_⇒
value)

source_value = date or text literal, column, variable, or expression

length_value = decimal, float, or integer literal, column, variable, or expression. The value is always converted to integer.

pad_value = text literal, column, variable, or expression

dst_var = text variable

Example: 

let $rpad = rpad($notice, 25, '.')

rtrim

Trims characters in source_value from the right until a character is not in set_value and returns the result.

Syntax: 


                           dst_var = rtrim(source_value, set_value)

source_value = date, or text literal, column, variable, or expression

set_value = text literal, column, variable, or expression

dst_var = text variable

Example: 

let $rtrim = rtrim(&description, '.')

strtodate

Converts the string source_value in the format format_mask to a date type.

Syntax: 


                           dst_var = strtodate(source_value [, format_mask])

source_value = text literal, column, variable, or expression.

format_mask = text literal, column, variable, or expression that describes the exact format of the source_value. The keyword DATE can be used to specify the DATE-EDIT-MASK setting from the current locale. If this argument is not specified, then source_value must be in the format specified by the SQR_DB_DATE_FORMAT setting, one of the database-dependent formats (see the Default Database Formats table), or the database-independent format 'SYYYYMDD[HH24[MI[SS[NNNNNN]]]]'. Valid format codes are specified in the Date Edit Format Codes table.

dst_var = date variable

Example: let $date = strtodate($str_date, 'Mon DD, YYYY') let $date = strtodate($str_date, DATE)

substr

Extracts the specified portion source_value. The extraction begins at offset_value (origin is 1) for a length of length_value characters.

Syntax: 


                           dst_var = substr(source_value, offset_value, ⇒
length_value)

source_value = date or text literal, column, variable, or expression.

offset_value = decimal, float, or integer literal, column, variable, or expression. The value is always converted to integer.

length_value = decimal, float, or integer literal, column, variable, or expression. The value is always converted to integer.

dst_var = text variable.

Example: 

let $piece = substr(&record, 10, #len)

Note: Oracle recommends that you use either the substrp or substrt function instead of the substr function.

substrb

Has the same functionality as the substr function except that the starting point and length are expressed in bytes rather than in characters.

Syntax: 


                           dst_var = substrb(source_value, offset_value, ⇒
length_value)

source_value = date or text literal, column, variable, or expression.

offset_value = decimal, float, or integer literal, column, variable, or expression. The value is always converted to integer.

length_value = decimal, float, or integer literal, column, variable, or expression. The value is always converted to integer.

dst_var = text variable.

Example: 

let $piece = substrb(&record, 10, #len)

Note: Oracle recommends that you use either the substrp or substrt function instead of the substrb function.

substrp

Returns a substring of a given string starting at a specified print position into the string and of a specified print length.

Syntax: 


                           dst_var = substrp(source_value, offset_value, ⇒
length_value)

source_value = date or text literal, column, variable, or expression.

offset_value = decimal, float, or integer literal, column, variable, or expression. The value is always converted to integer.

length_value = decimal, float, or integer literal, column, variable, or expression. The value is always converted to integer.

dst_var = decimal, float, or integer variable.

Example: 

let $sub = substrp(&string, #printpos, #printlen)

substrt

Returns a substring of a given string starting at a specified byte and byte length in a given encoding.

Syntax: 


                           dst_var = substrt(source_value, offset_value, ⇒
length_value, encoding_value)

source_value = date or text literal, column, variable, or expression.

offset_value = decimal, float, or integer literal, column, variable, or expression. The value is always converted to integer.

length_value = decimal, float, or integer literal, column, variable, or expression. The value is always converted to integer.

encoding_value = text literal, column, variable, or expression.

dst_var = text variable.

Example: 

let $sjisPrep = substrt(&string, 1, 10, 'Shift-⇒
JIS')

to_char

Converts source_value to a string, using maximum precision.

Syntax: 


                           dst_var = to_char(source_value)

source_value = decimal, float, or integer literal, column, variable, or expression

dst_var = text variable

Example: 

let $string = to_char(#number)

to_multi_byte

Converts the specified string in the following way: Any occurrence of a double-byte character that also has a single-byte representation (numerals, punctuation, roman characters, and katakana) is converted.

Syntax: 


                           dst_var = to_multi_byte (source_value)

source_value = date or text literal, column, variable, or expression

Example: 

let $multi = to_multi_byte (&text)

to_number

Converts source_value to a number. This function returns a float value.

Syntax: 


                           dst_var = to_number(source_value)

source_value = date or text literal, column, variable, or expression

dst_var = decimal, float, or integer variable

Example: 

let #value = to_number($number)

to_single_byte

Converts the specified string in the following way: For SJIS, EBCDIK290, and EBCDIK1027, any occurrence of a single-byte character that also has a multibyte representation (numerals, punctuation, roman characters, and katakana) is converted. This function also converts a sequence of kana characters followed by certain grammatical marks into a single-byte character that combines the two elements. For all other encodings, the string is not modified.

Syntax: 


                           dst_var = to_single_byte(source_value)

source_value = date or text literal, column, variable, or expression

Example: 

let $single = to_single_byte (&text)

translate

Inspects the contents of source_value and converts characters that match those in from_set to the corresponding character in to_set and returns the translated string.

Syntax: 


                           dst_var =  translate(source_value, from_set, to_⇒
set)

source_value = date or text literal, column, variable, or expression

from_set = text literal, column, variable, or expression

to_set = text literal, column, variable, or expression

dst_var = text variable

Example: 

let $translated = translate(edit(&price,⇒
 '999,999.99'),',',',')

transform

Returns a Unicode string that is a specified transform of a given string.

Syntax: 


                           dst_var = transform(source_value, transform_value)

source_value = date or text literal, column, variable or expression

transform_value = text literal, column, variable, or expression

dst_var = text variable

Example: 

let $hiragana = transform($string, 'ToHiragana')

unicode

Returns a Unicode string from the string of hexadecimal values provided.

Syntax: 


                           dst_var = unicode(source_value)

source_value = text literal, column, variable or expression

dst_var = text variable

Example: 

let $uniStr = unicode('u+5e73 u+2294')

upper

Converts the contents of source_value to uppercase and returns the result.

Syntax: 


                           dst_var =  upper(source_value)

source_value = date or text literal, column, variable, or expression

dst_var = text variable

Example: 

let $upper = upper(&description)

wrapdepth

Returns the number of print lines required by source_value. See the PRINTWRAP command for detailed descriptions of the parameters to this function. This function returns a float value.

Syntax: 


                           dst_var = wrapdepth(source_value, wrap_width, ⇒
line_height, on, strip)

source_value = text literal, column, variable, or expression

wrap_width = decimal, float, or integer literal, column, variable, or expression

line_height = decimal, float, or integer literal, column, variable, or expression

on = text literal, column, variable, or expression

strip = text literal, column, variable, or expression

dst_var = decimal, float, or integer variable

Example: 

let #depth = wrapdepth(&description,40,1,'<13>',''⇒
)

Writing Custom Functions

In addition to using the preceding built-in functions, you can write your own functions in C, using the supplied source file UFUNC.C .

You can pass any number of arguments to your function and values can be returned by the function or passed back in variables.

After editing and recompiling UFUNC.C, you must relink SQR.

The following step-by-step example shows how to add a user-defined function to SQR so that it can be invoked using the LET, IF, or WHILE command.

The example adds the C function random, which returns a random number. The function accepts a parameter that is used as the seed to start a new sequence of numbers. If the seed is zero, then the same sequence is used.

When adding functions to UFUNC, remember the following considerations:

  • String functions require the following arguments:

    • (int) Number of arguments.

    • (char *) or (double *) Array of argument pointers, to either char[ ] or double.

    • (char *) Address for result string. If unchanged, function returns a NULL string.

    • (int) Maximum length of result string, in bytes.

  • Numeric functions require the following arguments:

    • (int) Number of arguments.

    • (char *) or (double *) Array of argument pointers, to either char[ ] or double.

    • (double *) Address for result numeric value. If unchanged, function returns zero.

To add the random function to SQR, add the following modifications to the UFUNC.C file that was provided with SQR:

  • Add the prototype for the random function: static void random CC_ARGS((char *, char *));

  • Add the function name to the declaration list. The name of the function called from SQR is random. The return type is n for numeric. The number of arguments passed is 1, and the argument type is n for numeric. The function name in UFUNC.C is random. The characters PVR must be entered before the function name.

Name Return_type Number of Arguments Arg_Types Function

"max",

'n',

0,

"n",

PVR max,

"max",

'n',

0,

"n",

PVR max,

"split",

'n',

0,

"C",

PVR split,

"printarray",

'n',

4,

"cnnc",

PVR printarray,

"random",

'n',

1,

"n",

PVR random,

 
/* Last entry must be NULL -- do not change */
"", '\0', 0, "", 0
};

At the end the of UFUNC.C file, add the following random routine. The routine name must be lowercase; however, in your SQR program it can be referenced in either uppercase or lowercase.

static void random CC_ARGL((argc, argv, result)) 
CC_ARG(int, argc)               /* The number arguments passed   */
CC_ARG(double *, argv[])        /* The argument list             */
CC_LARG(double *, result)       /* Where to store result         */
{
if (*argv[0] != 0)
       srand(*argv[0]);
*result = rand();
return;
}

After these modifications, recompile UFUNC.C and relink SQR. See the programmer's reference manual for details about your particular machine.

This is a simple SQR program that uses the random function:

  begin-program 
    do get-random-number
    do process-calculations
  end-program

  begin-procedure
     let #seed = 44
     let #ran = random(#seed)
  end-procedure

  begin-procedure process-calculations
   .
   .
   .