UNIT 4

Operators and Functions

SQL Operators

In chapter three we briefly covered the operators possible with SQL, now we will go more in depth as to the use of those operators. The SQL operators are grouped into three categories, comparison, logical, and arithmetic (numeric and character). Each category is discussed separately in the following sections.

Comparison Operators

Comparison operators return either a true or false value and are most commonly used in the WHERE clause of the SELECT statement to condition which records are chosen from the file or files. Comparison operators are also used in the WHERE clause to form the join specification when two or more files are joined. All of the comparison operators take two operands, which can be of any type, but both operands must be the same type. The SQL comparison operators are:

= Equal to

Not equal

< > Not equal

Greater than

Not Greater than

< Less than

Not less than

>= Greater than or equal to

<= Less than or equal to

Logical Operators

Like the comparison operators, the logical operators also return a true or false condition and are most commonly used in the WHERE clause of the SELECT statement. Operands to these operators must be of type logical (true or false value). The logical operators are:

AND   syntax: <logical_expr> AND <logical_expr>
           return: true only if both operands are true, false otherwise.

OR      syntax: <logical_expr> OR <logical_expr>
           return: false only if both operands are false, true otherwise.

NOT   syntax: NOT <logical_expr>
           return: true if the operand is false, false if the operand is true.

Arithmetic Operators

There are two types of arithmetic operators, numeric and character. The numeric operators have an associated precedence or priority of execution. If you do not use parenthesis to group an expression involving numeric operators this precedence is used to prioritize the order of calculation. The following are the numeric arithmetic operators (their precedence is given in parenthesis with 1 being the highest):

(1)    +     positive sign for a numeric expression
        -      negative sign for a numeric expression

(2)    **   exponentiation

(3)    *     multiplication
        /      division

(4)    +     addition
        -      subtraction

There is only one character operator, concatenation. Concatenation adds one character value to the end of another. Programmers familiars with CL will immediately recognize this as the equivalent to the *CAT CL operator. In SQL however, the operator is:

    || OR CONCAT    concatenation

SQL Functions

SQL also provides some built-in functions that you can use to change how data is presented. The SQL functions are grouped into two categories, scalar and column. Scalar functions operate on a single value and return a value for every record in a SQL statement. Column function operates on a column or group of values returning only a single value for a group of records.

In the example,

SELECT ABSVAL (SALES) FROM SALES_FILE

the scalar function ABSVAL (absolute value) will return a value for every record in file SALES_FILE, the absolute value of SALES.

In the example,

SELECT AVG (SALES) FROM SALES_FILE

the column function AVG (compute statistical average) will return only a single value for all the records in SALES_FILE, namely the average of the values in field SALES.

NOTE: Next to each function in the following sections, the earliest release of OS/400, which supports the function, is given in parenthesis. If you attempt to use a function and you are on an earlier release, it will fail.

Scalar Functions

Scalar functions are those built-in functions that allow you to change the size and characteristics of a single field. Some scalar functions can be performed on numeric values, some on character values.

Scalar functions can be embedded within each other as long as the data type rules are followed. Column functions can be embedded within scalar functions.

The following are the scalar functions available:

ABSVAL (V2R2)

The ABSVAL function returns the absolute value of a number. This function is only valid with numeric fields. The syntax for the ABSVAL function is:

ABSVAL (value)

Example:

ABSVAL (rtnsls)

This function will return the absolute value of the returned sales.

ACOS (V2R2)

The ACOS function returns the arc cosine of a number in radians. This function is only valid on a numeric field whose value is greater than or equal to -1 and less than or equal to 1. The syntax for the ACOS function is:

ACOS (VALUE)

ANTILOG (V2R2)

The ANTILOG function returns the anti-logarithm (base 10) of a number. This function is only valid on a numeric field. The syntax for the ANTILOG function is:

ANTILOG (VALUE)

ASIN (V2R2)

The ASIN function returns the arc sine of a number in radians. The expression must be a number whose value is greater than or equal to -1 and less than or equal to 1. The syntax for the ASIN function is:

ASIN (VALUE)

ATAN (V2R2)

The ATAN function returns the arc tangent of a number in radians. The expression must be a number. The syntax for the ATAN function is:

ATAN (VALUE)

ATANH (V2R2)

The ATANH function returns the hyperbolic arc tangent of a number in radians. The expression must be a number whose value is greater than -1 and less than 1. The syntax for the ATANH function is:

ATANH (VALUE)

CHAR (V2R2)

The CHAR function returns a string representation of a date/time value. The first portion of the expression must be a date, time or timestamp. The second portion of the expression, if applicable, is an SAS date/time format. The syntax for the CHAR function is:

CHAR (<VALUE>, [SAS FORMAT])

Example:

CHAR (ORDTE, USA)

This will return 10/20/1992

CHAR (ORDDTE)

This will return 10/20/92

If the second portion of the expression is not specified, the format used is the internal job format. If the format is ISO, USA, EUR or JIS, the length of the result is 10. If the format is YMD, MDY or DMY the length of the result is 8. If the format is JUL the length of the result is 6. If the value is a timestamp, the length of the result is 26. If the value is a time, the length of the result is 8.

COS (V2R2)

The COS function returns the cosine of a number. This function is only valid for numeric fields. The syntax for the COS function is:

COS (VALUE)

COSH (V2R2)

The COSH function returns the hyperbolic cosine of a number. The expression must be a number whose value are specified in radians. The syntax for the COSH function is:

COSH (VALUE)

COT (V2R2)

The COT function returns the cotangent of a number. The expression must be a number whose value is specified in radians. The syntax for the COT function is:

COT (VALUE)

DATE (V2R1M1)

The DATE function returns a date from a value. The field must be a date, timestamp, valid string representation of a date, character string with a length of 7 or a positive number less than or equal to 3652059. The syntax for the DATE function is:

DATE (VALUE)

If the field is a timestamp then the result would be the date portion of the timestamp. If the field is a number then the result would be the number of days after January 1, 0001. If the field is a character string then the result would be the internal default CCSID converted to a date.

DAY (V2R1M1)

The DAY function returns the day portion of a value. The field must be a date, timestamp, date duration or timestamp duration. The syntax for the DAY function is:

DAY (VALUE)

If the field is a date or timestamp then the result would be the day portion of the value which is a number between 1 and 31. If the field is date duration or a timestamp duration then the result would be the day portion of the value, which is a number between -99 and 99.

DAYS (V2R1M1)

The DAYS function returns a numeric representation of a date. The value must be a date, timestamp or a valid string representation of a date. The result is 1 more than the number of days from January 1, 0001 to the date that would occur if you applied the DATE function to the value. The syntax for the DAYS function is:

DAYS(VALUE)

DECIMAL (V1R3)

The DECIMAL function allows us to define the size of a numeric field. This is useful, when we wish to present a calculated value with a specific size, instead of the default size. You must be careful that significant digits are not truncated when sizing the value. If this happens, the statement being executed will fail. This syntax for the DECIMAL function is:

DECIMAL (value, size, decimals)

Example:

DECIMAL (ORDQTY*PRICE, 9, 2)

This function will multiply the order quantity by the price and place the result in a field with a size of 9.2.

DIGITS (V1R3)

The DIGITS function allows us to convert a number to a character value. The syntax for the DIGITS function is:

DIGITS(value)

Example:

DIGITS(ORDDTE)

This function will convert the numeric order entry date to a character value. You will notice that there are no functions to convert a string value into a numeric value. This will severely restrict your ability to manipulate a file with no external definition.

EXP (V2R2)

The EXP function returns a value that is the base of the natural logarithm raised to the power specified by the value. This function is only valid with numeric values. The syntax for the EXP function is:

EXP(VALUE)

FLOAT (V1R3)

The FLOAT function allows us to convert a number into a floating point number. The syntax for the FLOAT function is:

FLOAT (value)

Example:

FLOAT(ORDQTY*PRICE)

This function will multiply the order quantity by the price and present the result as a floating point number.

HEX (V2R1M1)

The HEX function returns a hexadecimal representation of a value. The value can be a character or numeric data type. The result of the function is a character string that is twice the length attribute of the value. The syntax for the HEX function is:

HEX (VALUE)

HOUR (V2R1M1)

The HOUR function returns the hour portion of a value. The value must be a time, timestamp, time duration or a timestamp duration. If the value is a time or timestamp then the result will be a number between 0 and 24. If the value is a time duration or timestamp duration then the result will be a number between - 99 and 99. The syntax for the HOUR function is:

HOUR(VALUE)

INTEGER (V1R3)

The INTEGER function allows us to convert a number into an integer value. The syntax for the INTEGER function is:

INTEGER(value)

Example:

INTEGER(PRICE)

This function will convert the price field into an integer value.

LAND (V2R2)

The LAND function returns a string that is the logical AND of the argument strings. This function takes the first argument string, does an AND comparison with the next string, and then continues to do AND comparisons with each successive argument using the previous result. If an argument is shorter than the previous result then it is padded with blanks. This function is only valid with character strings, however they cannot be mixed character strings. There must always be two or more arguments. The resulting length is the same as that of the longest argument.

The syntax for the LAND function is:

LAND(VALUE1, VALUE2 [, . . .])

LENGTH (V1R3)

The LENGTH function returns the length of a value. The length returned represents the number of positions that are required to store the value, excluding trailing blanks. The value checked can be either numeric or character. The syntax for the LENGTH function is:

LENGTH(value)

Example:

LENGTH(CATDSC)

This function will return the length of the catalog description field.

LN (V2R2)

The LN function returns the natural logarithm of a number. The value must be a number. The syntax for the LN function is:

LN(VALUE)

LOG (V2R2)

The LOG function returns the common logarithm of a number. This function is only valid with numeric values. The syntax for the LOG function is:

LOG(VALUE)

LOR (V2R2)

The LOR function returns a string that is the logical OR of the argument strings. This function takes the first argument string, does an OR comparison with the next string, and then continues to do OR comparisons for each successive argument using the previous result. If an argument is shorter than the previous result then it is padded with blanks. The arguments must be character strings, however they cannot be mixed character strings. There must be two or more arguments. The resulting length is the same as that of the longest argument. The syntax for the LOR function is:

LOR (VALUE1, VALUE2 [, . . .])

LNOT (V2R2)

The LNOT function returns a string that is the logical NOT of the argument string. The argument must be a character string, however it cannot be a mixed character string. The syntax for the LNOT function is:

LNOT(VALUE)

MAX (V2R2)

The MAX scalar function returns the maximum value in a set of values. This function is only valid with two or more arguments whose data types are compatible. The maximum length allowed for a character string is 255. The result of the function is the largest argument value. The syntax for the MAX scalar function is:

MAX (VALUE1, VALUE2 [, . . .])

MICROSECOND (V2R1M1)

The MICROSECOND function returns the microsecond portion of a value. The value must be a timestamp, a string representation of a timestamp or a timestamp duration. The syntax for the MICROSECOND function is:

MICROSECOND(VALUE)

MIN (V2R2)

The MIN scalar function returns the minimum value for a set of values. This function is only valid with two or more arguments whose data types are compatible. The maximum length allowed for a character string is 255. The result of the function is the largest argument value. The syntax for the MIN scalar function is:

MIN(VALUE1, VALUE2 [, . . .])

MINUTE (V2R1M1)

The MINUTE function returns the minute portion of a value. The value must be a time, timestamp, time duration or timestamp duration. The syntax for the MINUTE function is:

MINUTE(VALUE)

MOD (V2R2)

The MOD function divides the first value by the second value and returns the remainder. The values must be numbers and the second value cannot be a zero. The syntax for the MOD function is:

MOD(VALUE1, VALUE2)

MONTH (V2R1M1)

The MONTH function returns the month portion of a value. The value must be a date, timestamp, date duration or timestamp duration. If the value is a date or a timestamp, then the result is a number between 1 and 12. If the value is a timestamp duration or a date duration, then the result is a number between -99 and 99. The syntax for the MONTH function is:

MONTH(VALUE)

SECOND (V2R1M1)

The SECOND function returns the seconds portion of a value. The value must be a time, timestamp, time duration or timestamp duration. If the value is a time or timestamp, then the result is a number between 1 and 59. If the value is a time duration or timestamp duration, then the result is a number between -99 and 99. The syntax for the SECOND function is:

SECOND(VALUE)

SIN (V2R2)

The SIN function returns the sine of a number. The value must be a number specified in radians. The syntax for the SIN function is:

SIN(VALUE)

SINH (V2R2)

The SINH function returns the hyperbolic sine of a number. The value must be a number specified in radians. The syntax for the SINH function is:

SINH(VALUE)

SQRT (V2R2)

The SQRT function returns the square root of a number. The value must be a positive number. The syntax for the SQRT function is:

SQRT(VALUE)

STRIP (V2R1M1)

The STRIP function removes blanks or another specified character from the beginning or end of a string expression. The first value must be a string expression. The second value indicates whether characters are removed from the beginning or end of the string. The possible syntax for the second value are: B for both, L for leading and T for trailing. If no second value is specified then blanks are removed from both the beginning and end of the string. The third value specifies which single character constant is to be removed. If no third value is specified then the default strip character is a blank. The syntax for the STRIP function is:

STRIP ([, STRIP POSITION] [, STRIP CHARACTER])

SUBSTR (V1R3)

The SUBSTR function allows us to extract a portion of a string value. The syntax for the SUBSTR function is:

SUBSTR (string,start,length)

You must be careful that the starting position and length specified do not exceed the actual length of the string. If this happens, the statement will fail.

This is the function that you use if you wish to extract data from a file with no external definition.

Example:

SUBSTR (CATLG#,1,10)

This function will extract the first 10 characters of the catalog number.

TAN (V2R2)

The TAN function returns the tangent of a number. The value must be a number specified in radians. The syntax for the TAN function is:

TAN(VALUE)

TANH (V2R2)

The TANH function returns the hyperbolic tangent of a number. The value must be a number specified in radians. The syntax for the TANH function is:

TANH(VALUE)

TIME (V2R1M1)

The TIME function returns a time from a value. The value must be a time, timestamp or a valid string representation of a time. The syntax for the TIME function is:

TIME(VALUE)

TIMESTAMP (V2R1M1)

The TIMESTAMP function returns a timestamp from a value or pair of values. If only one argument is specified then it must be a timestamp, valid string representation of a timestamp or a character string with a length of 14. The character string must be digits representing a valid date and time in the form yyymmddhhmnmss. If both arguments are specified then the first argument must be a date or valid string representation of a date, and the second argument must be a time or valid string representation of a time. The syntax for the TIMESTAMP function is:

TIMESTAMP ([, VALUE2])

TRANSLATE (V2R2)

The TRANSLATE function translates the characters of the value to uppercase. The value must be a character string or mixed character string, however, it cannot be a graphic string. The syntax for the TRANSLATE function is:

TRANSLATE(VALUE)

VALUE (V2R1M1)

The VALUE function returns the first argument that is not null. There must be two or more arguments and they must have compatible data types. The arguments are evaluated in the order which they are specified. The syntax for the VALUE function is:

VALUE (VALUE1, VALUE2 [, . . .])

XOR (V2R2)

The XOR function returns a string that is the logical 'XOR' of the argument strings. This function takes the first argument string, does an 'XOR' comparison with the next string, and then continues to do 'XOR' comparisons for each successive argument using the previous result. If an argument is shorter than the previous result, it is padded with blanks. There must be two or more arguments and they must be character strings, however, they cannot be mixed character strings. The syntax for the XOR function is:

XOR (VALUE1, VALUE2 [, . . .])

YEAR (V2R1M1)

The YEAR function returns the year portion of a value. The value must be a date, timestamp, date duration or timestamp duration. If the value is a date or timestamp then the result is a number between 1 and 9999. If the value is a date duration or timestamp duration then the result is a number between -9999 and 9999. The syntax for the YEAR function is:

YEAR(VALUE)

ZONED (V2R2)

The ZONED function returns a zoned decimal representation of a number. The first argument must be a number. The second argument, if specified, must be a number between 1 and 31. The third argument, if specified, must be a number between 0 and the value of the second argument. If the third argument is not specified then the default is zero. The default for the second argument is either 5 for small integer, 11 for large integer or 15 for floating-point, decimal, numeric or non-zero scale binary. The syntax for the ZONED function is:

ZONED([, PRECISION, SCALE])

Column Functions

Column functions perform an operation over a group of records and return a single result for each group (see GROUP BY clause). For example, you can obtain the average amount for a group of invoice records.

Column functions can only be used in the SELECT and HAVING clauses. You can obtain a column function figure for an entire file or you can obtain figures for subgroups of records by using the GROUP BY clause.

You cannot embed column functions within each other.

The following are the column functions available:

AVG (V1R3)

The AVG function returns the average value for a numeric field in a group of records. The syntax for the AVG function is:

AVG(value)

Examples:

AVG(CYSLS$)

This function would return the average of the current year sales for each group selected.

AVG(CYSLS$+LYSLS$)

This function would return the average of the total of the current and last year sales for each group selected.

COUNT(*) (V1R3)

The COUNT function returns the number of records found within a group. The result is a number. There are no values used with the COUNT function.

Example:

COUNT (*)

MAX (V1R3)

The MAX function returns the maximum value encountered for a group of records. This function can be performed on numeric or character fields. When processing a character field, the result cannot exceed 256 characters. The syntax for the MAX function is:

MAX(value)

Examples:

MAX (SLSAMT)

This function will return the maximum sales amount found for the group selected.

MAX (SUBSTR (DIGITS (ORDTE), 5, 2)

This function will return the maximum year found in the order entry date for the group selected. Notice that we used several built-in functions to extract the year.

MIN (V1R3)

The MIN function returns the minimum value found for a group of records. This function can be performed on numeric and character fields. When processing a character field, the result of the MIN function cannot exceed 256 characters. The syntax for the MIN function is:

MIN(value)

Examples:

MIN (SLSAMT)

This function will return the minimum sales amount found for the group selected.

MIN (SUBSTR (DIGITS (ORDDDTE), 5, 2)

This function will return the minimum year found in the order entry date for the group selected. Notice that we used several built-in functions to extract the year.

STDDEV (V2R2)

The STDDEV function returns the biased standard deviation for a set of numbers. This function is valid only on numeric fields. The syntax for the STDDEV function is:

STDDEV(value)

Examples:

STDDEV (SLSAMT)

This function will return the standard deviation for the total sales amount for each group selected.

SUM (V1R3)

The SUM function returns the total value for a group of records. This function is valid only for numeric fields. The syntax for the SUM function is:

SUM(value)

Example:

SUM(CYSLS$)

This function will return the sum of the current year sales for the group selected.

VAR (V2R2)

The VAR function returns the biased variance for a set of numbers. This function is only valid for numeric fields. The syntax for the VAR function is:

VAR(value)

Example:

VAR(PRICE)

This function will return the variance of price for the group selected.