Miscellaneous Functions

Note:

Miscellaneous functions return a string value unless otherwise indicated.

Table 52. Miscellaneous Functions

Function	Explanation
array	Returns a pointer to the starting address of the specified array field. The value returned can only be used by a user-defined function. See printarray in UFUNC.C. 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')
command_line	Returns command line arguments passed to SQR (or SQRT). Syntax: dst_var = command_line( ) dst_var = text variable Example: let $cmdline = command_line( )
cond	Returns y_value if x_value is nonzero (0); otherwise, returns z_value. If y_value is numeric, then z_value must also be numeric; otherwise, date and textual arguments are compatible. If either 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)
getenv	Returns the value of the 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')
getfilemapname	Returns the mapped filename. In Oracle Enterprise Performance Management Workspace, Fusion Edition, if the filename has a mapped equivalent the mapped filename is returned; otherwise, the filename is returned unchanged. Outside of EPM Workspace, the filename is returned unchanged. Syntax: dst_var = getfilemapename(source_value) source_value = text literal, column, variable, or expression dst_var = text variable Example: let $realfile = getfilemapname('data.fil' )! get real filename is run under EPM Workspace let #Status = System('cp ' \|\| $RealFile \|\| ' /tmp') ! Copy to temp directory
isnull	Returns one (1) if source_val is null; otherwise, returns zero (0). 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)
isnumber	Returns one (1) if source_value is a number; otherwise, returns zero (0). A number is defined to be of the form: [Sign] [Digits] [.Digits] [E] e [Sign] Digits]. Leading and trailing blanks are ignored. Syntax: dst_var = isnumber(source_value) source_value = text literal, column, variable, or expression dst_var = decimal, float, or integer variable Example: let #isnumber = isnumber($string)
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, then y_value is validated according to the following rules: For DATETIME columns and Production Reporting DATE variables, Production Reporting uses the format specified by SQR_DB_DATE_FORMAT, one of the database-dependent formats (see Table 61, Default Formats by Database), or the database-independent format 'SYYYYMDD[HH24[MI[SS[NNNNNN]]]]'. For DATE columns, Production Reporting uses the format specified by SQR_DB_DATE_ONLY_FORMAT, or the format in Table 62, DATE Column Formats. For TIME columns, Production Reporting uses the format specified by SQR_DB_TIME_ONLY_FORMAT, or the format in Table 63, TIME Column Formats.
range	Returns one (1) if x_value is between y_value and z_value; otherwise, returns zero (0). 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 and/or text. It is also possible to 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-97') 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 Production Reporting DATE variables, Production Reporting uses the format specified by SQR_DB_DATE_FORMAT, one of the database-dependent formats (see Table 61, Default Formats by Database), or the database-independent format 'SYYYYMMDD[HH24[MI[SS[NNNNNN]]]]'. For DATE columns, Production Reporting uses the format specified by SQR_DB_DATE_ONLY_FORMAT, or the format inTable 62, DATE Column Formats. For TIME columns, Production Reporting uses the format specified by SQR_DB_TIME_ONLY_FORMAT, or the format in Table 63, TIME Column Formats.
roman	Returns a string that is the character representation of source_value expressed in lower case 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)
wrapdepth	Returns the number of print lines required by source_value. See the WRAP argument in PRINT 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>','')