Functions

List of Functions by Type

Returns the absolute value of X.

Returns the arc cosine of X. The result is in the range 0 to PI radians. Values of X outside the range -1<X>1 return undefined.

Returns the truncated value of X. If n is omitted or 0, truncates to an integer value. If n is specified, truncates to that power of 10. e.g. AINT (1266,2) truncates to 1200, AINT(1.266,-2) truncates to 1.26.

Returns the natural logarithm (base e) of X. Values less than or equal to zero return undefined.

Returns the base 10 logarithm of X. Values less than or equal to zero return undefined.

Returns the remainder of X divided by Y. If Y is zero, undefined is returned.

Returns SIR2002 application directory. That is the directory where the SIR executables are installed.

See ACOS function.

Returns the number of dimensions for specified local array variable.

Returns the start value for the specified dimension for specified local array variable (normally 1 unless array specified to start at different value).

Returns the size of the specified dimension for specified local array variable.

See ASIN function.

Returns the arc sine of X. The result is in the range -pi/2 to +pi/2 radians. Values outside the range-1<X>1 return undefined.

Returns the arctangent of X. The result is in the range -pi/2 to +pi/2 radians.

Returns the nth Attribute name (SYSTEM(52) = attribute count).

Returns the nth Buffer name (SYSTEM(56) = buffer count).

Capitalises the first alphabetic character of the string and the first alphabetic character following a blank. All other characters remain unedited. For example:

NAME = 'this is the first day of the week'
NAME = CAPITAL(NAME)
Returns: This Is The First Day Of The Week

Changes the lock type for the current Case (CIR) and attempts to read the current CIR from the database. See SYSTEM(37) function to determine if current CIR is locked. The locktype codes are (any other values set concurrent read):

1 = Exclusive
2 = Concurrent Read
3 = Concurrent Write
4 = Protected Read
5 = Protected Write
6 = Exclusive

Returns an integer corresponding to the category in categorical variable A that B matches. A and B may be variables, string constants or expressions. Returns a zero if no match is found.

Returns a string corresponding to the current value of the specified categorical variable.

Returns the date integer equivalent to the date string X which may be a string constant, variable or expression. The date format is a string constant consisting of a combination of the letters M(month), D(day), Y(year) and I(ignore). If the month or day field is omitted, the assumed value is 1. If fewer than four Y's are specified, the following applies:

With three Y's, the millennium is taken as 1nnn for values above 583 and 2nnn for values below this:

Returns string strY centred in a string X characters in length. Example:

RESULT = CENTER(9, 'ABC')

returns:  "   ABC   ".

Used when dealing with CGI from webserver. Puts value of CGI parameter into named buffer. Returns number of lines. Parameters are string expressions.

Used when dealing with CGI from webserver. Puts value of server variable into named buffer. Returns number of lines. Parameters are string expressions.

Used when dealing with CGI from webserver. Returns named parameter value in string variable. Parameter name is a string expression.

Used when dealing with CGI from webserver. Returns named server variable value in string variable. Server variable name is a string expression.

Returns a single character. The character returned is the character whose internal value is N. (See the ICHAR function.) If N is larger than 255, N is divided by 256 and the remainder is taken. This gives the set of standard characters. If N is missing, a null string is returned (length 0).

Counts the number of values in a list that exist (not missing or undefined). There may be up to 128 numeric variables in the list. Returns zero (0) if no values exist.

Returns the number of values of X found during a PROCESS REC or PROCESS ROWS loop that are not missing or undefined. Returns zero if all values are missing or undefined.

Client/Server function. Returns a count of columns created by the execute.

Client/Server function. Returns the label of a specific column created by the execute.

Client/Server function. Returns the length of a specific string column created by the execute.

Client/Server function. Returns the name of a specific column created by the execute.

Client/Server function. Returns the type of a specific column created by the execute. This is one of the following:
1 = String
2 = Timestamp string
3 = Date
4 = Time
5 = Integer
6 = R4
7 = R8
8 = Scaled Integer

A timestamp string is a formatted 18 byte string containing a combination date/time as follows:
YYYYMMDDHHMMSStttt where YYYY is the year, MM is the month, DD is the day number, HH is the 24 hour number, MM is the minutes, SS is the seconds and tttt is the thousandths of a second. Any of these components may be zero.

Client/Server function. Returns the numeric value of a specific column created by the execute. This does not have to be the same type as returned by COLTYPE. e.g. Integers can be assigned to a real.

Client/Server function. Returns the string value of a specific column created by the execute.

Places a comma between every third digit to the left of a decimal point (actual or implied) in a string with a numeric form. For example, COMMA ('4500000') returns '4,500,000'.

Returns the trigonometric cosine of X, where X is measured in radians.

Returns the number of records for record type X within the current case.

Returns the number of seconds from midnight to the given time. Specify a time string X and a time format consisting of the letters H (hours), M (minutes), S (seconds) and I (ignore). The time, X, can be a string constant, variable, or expression. If the hour, minute or second field is omitted, the assumed value is zero. For example:

WINTIME = CTIME('18:36:45', 'HHIMMISS')

Returns the current directory.

Returns a date string equivalent to the date integer X formatted according to the date format which is a string constant using the letters M(month), D(day), Y(year) and W(Day of week). All other characters are passed directly to the result string. One or two Ms result in the month number, three or more result in the first n letters of the month name. The case of the letters in the date format (upper or lower), specifies the case of strings returned. For example, DATEC(XBEG, 'Www, Mmm DDth, YYYY') produces a result such as "Thu, May 25th, 2000

Returns a string with the date map of the specified date variable. If the record number (rtnum) is negative, the function applies to a summary variable. The varname is an expression. If this is a constant, enclose the name in single quotes. Undefined is returned if the variable is unknown or is not a date variable. For example, if the variable BIRTHDAY in record type one has the date map "MM DD YY" defined in the schema, then DATESTR equals "MM DD YY".

DATESTR = DATEMAP (1, 'BIRTHDAY')

Returns a 27-character string containing the current date and time. The string is composed of the following substrings:

Date
  1- 3 Day of  the week  (SUN,MON,etc.)
  4- 5 Comma and blank
  6- 8 Month of the year  (JAN,FEB,etc.)
     9 Blank
 10-11 Day of the month
 12-13 Comma and blank
 14-17 Year
 18-19 Comma and blank

Time
 20-21 Hour (1 to 12)
    22 Period
 23-24 Minutes
    25 Blank
 26-27 AM or PM

The two arguments N1 and N are constants in the range 1 to 27 that select a substring of the 27-character string. For example, suppose the current date is May 25, 2000, and the time is 1:05 PM; the day is Thursday.

PROGRAM
TODATE  = DATET (6, 17)
NOWTIME = DATET(20, 27)
WKDAY   = DATET(1, 3)
WRITE TODATE NOWTIME WKDAY
END PROGRAM

OUTPUT:  MAY 25, 2000 01.05 PM THU

Returns name of nth index.

Returns number of record type indexed by nth index.

Returns number of indexes on default database.

Name of nth variable in nth index plus ASC/DESC and UPPER.

Returns 1 if index is unique.

Returns number of variables in nth index.

Returns a string with the name of the nth attached database. If n is zero, returns the name of the default database.

Returns 1 if a case structured database or 0 if caseless.

Returns the default family name.

Returns the default member name.

Deletes the named directory. Returns 0 for success.

Deletes the named file (use filename not attribute). Returns 0 for success.

Deletes the client from master. Get the client id from GETMCLID. Specify a password as a string variable or string in quotes if the Master is started with a password.

Deletes a global. The string expression may be the global name enclosed in quotes or a string variable.

The DITEM series of functions all pertain to the Dialog Editor which is used to construct screen painting applications.

Returns the operating system filename of an attribute. The attribute may be a variable or constant.

EDIT applies an edit string to data to produce the output. The edit string is made up of circumflexes ( ^ ), which represent a character of the input string, and any other characters to insert. Example:

PROGRAM
SSN = EDIT('123456789','^^^-^^-^^^^')
WRITE SSN STOP
END PROGRAM

Output: 123-45-6789

Returns the name of the current text editor.

Displays an error box with the specified text and waits for acknowledgment.

Returns 1 if X exists, 0 if X is missing or undefined. To test several numeric variables for existence, use the CNT function.

Returns the value of e raised to the X power. e is the constant 2.71828.

Returns the nth family name in the default procfile. (SYSTEM(57) = Count of families).

Tests two floating point numbers for equality within a limit of accuracy. The function returns 0 if approximately equal, 1 if unequal. For example, if the exponent was -3, the numbers would be equal if within .001.

Counts the files in the directory given by name. Specify a mask like '*.pql' for the count of those files in the current directory.

Displays a file browse box for user to choose existing file. Returns zero length string if user cancels.

Tests if file exists. Returns -1 if name is a directory; 0 if no such name; 1 if file exists.

Returns the nth file name in the given directory (or mask).

Displays a file browse box for user to choose output file. Returns zero length string if user cancels.

Returns various system specific data about a named file.
Type of data
1 gid Numeric identifier of group that owns file (UNIX-specific)
2 st_atime Time of last access of file. (system date/time integer)
3 st_ctime Time of creation of file. (system date/time integer)
4 st_dev Drive number of the disk containing the file (same as st_rdev).
5 st_ino Number of the information node (the inode) for the file (UNIX-specific).
6 st_mode Bit mask for file-mode information. The _S_IFDIR bit is set if path specifies a directory; the _S_IFREG bit is set if path specifies an ordinary file or a device. User read/write bits are set according to the file's permission mode; user execute bits are set according to the filename extension.
7 st_mtime Time of last modification of file. (system date/time integer)
8 st_nlink Always 1 on non-NTFS file systems.
9 st_rdev Drive number of the disk containing the file (same as st_dev).
10 st_size Size of the file in bytes; (Can exceed I4 in size)
11 uid Numeric identifier of user who owns file (UNIX-specific)

Returns SIR times or dates about a file
Type of data
1 Time of last access of file. (SIR time integer)
2 Date of last access of file. (SIR date integer)
3 Time of creation of file. (SIR time integer)
4 Date of creation of file. (SIR date integer)
5 Time of last modification of file. (SIR time integer)
6 Date of last modification of file. (SIR date integer)

Replaces all blank characters in strX with the first character of string argument strY. The length of strX does not change. For example:

RESULT = FILL ('  $100.00', '*')
returns  "**$100.00"

Search dialog choice or list for text and return position. Can start from partway through.

Converts X to a string. FORMAT( X ) returns free-field format as wide as necessary to fit the value. FORMAT(X,W) returns a free field format of width W. FORMAT(X,W,D) returns a number with D decimal places in width W. X, W and D, can be variables, constants or expressions. W, if specified, must be greater than or equal to zero. D, if specified, can be -1 or greater than or equal to zero. -1 is equivalent to not specifying a value (free field format). If D is specified, W must be greater than D. For example:

STR = FORMAT (1.3)       returns  '1.3'
STR = FORMAT (1.3, 4)    returns  ' 1.3'
STR = FORMAT (1.3, 5, 2) returns  ' 1.30'

Returns the first value in the list of up to 128 numeric variables that is not missing or undefined.

Returns the first value of X encountered during a PROCESS REC or PROCESS ROWS loop that is not missing or undefined.

Returns the height of button control (for positioning).

Returns the height of choice control( for positioning).

GETCHKH

Returns the height of check control( for positioning).

Returns time (from Master) of difference file copy interval in minutes.

Returns the value of the named environment variable.
COMPUTE OSPATH = GETENV('PATH')

GETERR

Client/server function. Returns the oldest error posted for this application and deletes the message. Returns a zero length string if no messages. Errors are not specific to a connection or statement, rather they are posted for this instance of SIR2002 and, if errors are not retrieved when an error condition occurs, multiple error messages may be waiting. One logical error may also give rise to multiple error messages from the server.

GETFLT

Gets floating point as per GETTXT. Returns 0.0 if not f.p. number.

GETFOCUS

Returns id of control with focus.

GETICHK

Returns check or radio state.

GETIFLT

Returns floating point from a list.

GETIINT

Returns integer of item from a list.

GETINT

Gets integer as per GETTXT. 0 if not integer.

GETITXT

Returns text of item from a list.

GETLBLH

Returns the height of labels ( for positioning).

GETLTXT

Gets the text from a line in a multi-line text control.

GETMAXCH

Returns the height of the maximum single line control ( for positioning).

GETMCADD

Returns tcp/ip address for client from master.

GETMCHK

Returns check state from menu item.

GETMCLID

Returns id for nth client from master.

GETMCLST

Returns time of last message for nth client from master.

GETMCON

Returns time of log on for nth client from master.

GETMDBN

Returns name of nth database from master.

GETMSEL

Returns pos of nth selected item from multiple selection.

GETNITEM

Returns number of items in choice or list.

GETNLINE

Gets number of lines in multi-line text.

GETNSEL

Returns number of items selected in multiple selection.

GETPOS

Returns pos of current selection in list or choice or keyboard focus in multiple selection.

GETRADH

Returns the height of radio control( for positioning).

GETRSTEP

Returns the size of the row step ( for positioning).

GETTXT

Gets text from edit and from highlighted item in choice or list. (Gets label text from label, button, check and radio!).

GETTXTH

Returns the height of text control ( for positioning).

Assigns a numeric value to a global variable. The first argument is the name of the global variable, the second argument is the numeric expression (or variable name). For example: To assign the global variable RT the value 25.5:

COMPUTE Y = GLOBALN('RT', 25.5)

Assigns a string value to a global variable. The first argument is the name of the global variable, the second argument is the string expression (or variable name). For example: To assign the global variable TEMP the value JOE SMITH:

COMPUTE Y = GLOBALS ('TEMP','JOE SMITH')
GLOBALN and GLOBALS return:

0 if the assignment was made.

-1 if the first argument is not a valid global variable name.

-2 if the second argument is missing.

Do not try to use the value of globals set by GLOBALN or GLOBALS for text substitution in the same program (by using the global variable name within angle brackets) because the functions work at execution time and text substitution happens at compile time.

Invokes the HELP system, beginning with the specified help page. The page must be in html format in the help directory. The page name can contain directory names using forward slashes to delimit. Enclose the name in quotes. The system will convert this to a file name prefixed with a path pointing to the help directory and suffixed with the file extension .htm. For example:

COMPUTE X = HELP ('visualpql/function/foreword')

Returns a numeric value equivalent to the position in the character collating sequence of the first character in string C. The character collating sequence for a given computer is the set of numeric codes used for internal character representation (such as ASCII or EBCDIC).

Returns the status of a gui element.

Returns whether the journalling option is on (1) or off (0) for the database.

Converts a "date integer", X, into an 12-character string of the form 'MMM DD, YYYY'.

Returns a "date integer" where the three numeric arguments X, Y and Z, stand for month, day and year, respectively. A "date integer" is the number of days since the start of the Gregorian calendar on October 15, 1582. If Z is in the range 0 - 99, the year is 1900 + Z. If Z is in the range 100 - 999, the year is 1000 + Z. A value of undefined is returned if a date earlier than October 15, 1582 is specified by X, Y and Z. For example:

DURATION  =  JULN(4, 8, 87) - BEGINDAT

ENDPROJ   =  JUNL(TMON, TDAY, TYEAR)

Returns the name of the specified keyfield for the specified record type. RTNUM is the record number. KEYNUM is the number of the keyfield, i.e 1 is the case id, 2 is the first key field in the record type, etc.

Returns "A" or "D" for the sort order of the specified keyfield. The variable name argument is an expression.

Returns an integer value that is the length, in characters, of the string strX, leading and trailing blanks included.

See ALOG function.

See ALOG function.

See ALOG10 function. (LG10 is also allowed.)

Returns the string with all characters converted to lower case.

Returns the last value in the list that is not missing or undefined. The maximum number of variables allowed in the argument list is 128. Arguments must be numeric.

Returns the last value of X encountered in a PROCESS REC or PROCESS ROWS loop that is not missing or undefined.

Creates a new directory using the name. Returns -1 if the directory cannot be created.

Returns the largest value in the list that is not missing or undefined. The maximum number of variables allowed in the argument list is 128.

Returns the largest value of the specified variable encountered during a PROCESS REC or PROCESS ROWS loop that is not missing or undefined.

Returns the maximum number of records allowed for this record type.

Returns the mean (arithmetic average), of the values within the list that are not missing or undefined. The maximum number of variables allowed in the argument list is 128.

Returns the mean (arithmetic average), for the values of the specified variable encountered during a PROCESS REC or PROCESS ROWS loop that are not missing or undefined.

Returns the count of members in the named family of the default procfile.

Returns the name of the nth members in the named family of the default procfile.

Returns the smallest value within the list that is not missing or undefined. The maximum number of variables allowed in the argument list is 128.

Returns the smallest value of the specified variable encountered during a PROCESS REC or PROCESS ROWS loop that is not missing or undefined.

If X is undefined, 0 is returned. If X is missing, the missing type is returned (1 to 3). If X is neither missing nor undefined, undefined is returned. X may be a numeric or string variable.

Returns the original value for undefined and first, second and third missing values (as a string), where n is 0, 1, 2, or 3. If the record number (rtnum) is negative, the function applies to a summary variable.

Returns the original value of a variable if it is missing, otherwise undefined is returned. If the variable is a time, date, or categorical integer, the original string value is returned. If the variable is an integer or floating point variable, the original numeric value is returned. If the argument is not a single variable name, undefined is returned.

Returns the maximum key size in bytes for the database. The key for a record is comprised of the case identifier (in a case structured database), the record type number, and the keyfields defined for the record type (if any). Refer to the schema definition command MAX KEY SIZE for more information about maximum key size.

Returns the size of the largest record type in the database. Size is expressed in the number of SIR "double words".

Returns the text of the error or warning massage given by the number num.

Returns numeric arguments from the command parameter list. (String parameters are retrieved with the SARG function.)

The argument is the position of the parameter in the list. An argument value of zero returns the number of parameters in the list. If the argument is greater than the number of parameters in the parameter list or the argument is a string, undefined is returned. For example, to return the value of the third argument of the parameter list which must be numeric:

COMPUTE ARG4 = NARG(3)

Client/server function. Steps through the rows one at a time. This must be issued before getting data for the first row. Returns the row number or zero if no more rows.

Returns the value of the specified numeric variable. The variable name is specified as a string variable, quoted string constant or string expression whose value is the name of a common, record or program variable.

Returns the value of numeric global parameters. C is a character variable, constant or expression whose value specifies the name of the global parameter. If C is not a defined global parameter, undefined is returned. String global parameters are retrieved with SGLOBAL. For example, to set the variable NVAL to the value of global parameter RACETIME:

COMPUTE NVAL = NGLOBAL ('RACETIME')

Returns the number of keyfields (sort-ids) for the specified record type (excluding the case id).

Returns a "time integer" representing the current time of day as the number of seconds since midnight. The argument is a dummy argument, specify zero.

Stores the value of numeric argument Y in numeric variable A. A is a string variable name, quoted string constant or string expression whose value is the name of a common, record, or program variable. The value returned by the function is the value actually stored in A (possibly undefined, missing, etc.). If A refers to a common or record variable, the Retrieval must be in update mode. The following example stores 175 in the variable Height:

COMPUTE DUMMY = NPUT('HEIGHT', 175)

Pops up a box on the screen with a prompt and returns a number from the user.

If a non-numeric field is entered, a message is issued and the user is prompted again.

Returns the maximum number of record types for the database which is the maximum possible number of record types, not the actual number of record types defined for the database.

Returns the name of the nth sub-directory.

Returns the numeric value of the string strX, where strX is a string constant, variable name or expression which contains only numerical characters, at most one decimal point and a plus or minus sign or is in E+exponent format.

Returns the number of cases in the database. Same as functions SYSTEM(24) or NUMRECS(0).

Returns the number of records of the specified record type.

Returns the number of valid values for the specified variable. If the record number (rtnum) is negative, the function applies to a summary variable.

Returns the number of variables (not Common) defined in the specified record type.

Returns the number of variables (including Common) defined in the specified record type.

Returns the value of the nth valid value of a numeric variable. If the record number (rtnum) is negative, the function applies to a summary variable.

Client/server function. Does an ODBC query which produces a result set which contains a list of columns from the named table on the data source and can be interrogated using the standard functions.

Client/server function. Does an ODBC query which produces a result set which contains a list of tables on the data source and can be interrogated using the standard functions.

Returns a string with leading and trailing blanks deleted and multiple blanks compressed into one blank. The argument strX may be a string constant, variable name or string expression.

Pads the input string with the specified pad character to the pad length and then truncates the string to the truncation length.

Returns the current setting for page length (length of page on output file).

Returns the current page number on output file.

Returns the current setting for page width (width of page on output file).

Returns 1 if the pattern specified by pattern_str is in strX, otherwise 0. Both arguments are strings and can be variables, constants or expressions. The pattern can contain the match anything character "@". Undefined is returned if either argument is missing or undefined. For example, the following returns 1.
THERE = PATTERN ('Mr. Ralph Jones', 'Mr.@Jones@')

formats a number according to a picture clause, returning a string. (Picture clauses are the same as those used on the WRITE command.) The following special picture characters are recognised:

9 - digit or blank (for fill to the left)
z - digit or zero  (for fill to the left)
c - character
u - uppercase character
l - lowercase character

The format string may also contain asterisks (*), commas (,), decimal point (.), and dollar signs ($). In the following example, If SALARY equals 2500, then SALSTR equals " $2500.00".
SALSTR = PFORMAT(SALARY, '$zzzz.zz')

Validates a string according to a specified picture string. The picture string ( pstr ) is composed of character codes and characters that must be matched exactly. Note: the character codes are lower case. They are:

a - any letter
d - any digit
n - any letter or digit
s - numeric value components (0-9, decimal point,+,+"",E)
u - any uppercase letter
l - any lowercase letter
x - any character

The first example returns a 0 showing the picture is correct. The second example returns a 6 to show that the picture fails in the sixth position:

X = PICTURE ('123-45-6789','ddd-dd-dddd')
X = PICTURE ('123-45-67',  'ddd-d-ddd')

Controls the display of a progress bar.
x = PROGRESS (0,0) initiates the display.
x = PROGRESS (1,n) displays progress up to n where n is a percentage from 1 to 100.
x = PROGRESS (2,0) closes the display.
The initiation, updating and closing do not have to be in the same VisualPQL program. Once initiated in a program, the progress display is closed only by this function, not automatically at the end of the program and thus can be used to display progress through a suite of programs. If the progress display has not been initiated, the function has no effect.

Returns the read security access level of the current user. That is the level corresponding to the read security password of the user.

Returns a uniform random number between 0 and 1. The argument is a dummy argument. For a given seed, the same sequence of random numbers is generated. To alter the default seed, specify a seed on the RETRIEVAL or PROGRAM command.

Converts a real*8 into a real*4 number.

Returns the nth line of documentation for the record. If the record number is zero, the function returns the nth line of database level documentation.

Returns the number of lines of documentation for the record. If the record number is zero, the function returns the number of lines of database level documentation.

Returns the update level of the current record (when it was last written to the database). The update level changes with each modification to the record (during a RETRIEVAL UPDATE, Batch Data Input run, FORMS updating session, etc.). This function can only be used in a PROCESS REC loop.

Returns the name of the specified record type padded with blanks to eight characters. If rtnum is 0, "CIR " is returned. Only used in a RETRIEVAL.

Returns the number of the specified record name. If the name does not exist, returns undefined. Only used in a RETRIEVAL.

Returns the record size for the specified record type in double words. For example, to find the length of record type 1:

EMPSIZE = RECSIZE (1)

If the search string is found in the original string, occurrences are replaced by the replace string. The number of times the string is replaced, the offset for the next starting position, and an anchor column are also specified. The following example returns "CABCBCBBAA" if INLINE is "AABABABBAA".
INLINE = REPLACE (INLINE, 'A','C',3,2,0) When anything but 0 is specified as the anchor, the string is only replaced once, at the anchor position. The following example returns "AABCBABBAA" if INLINE is "AABABABBAA".
INLINE = REPLACE (INLINE, 'A','C',3,2,4)

Reverses a string.

Returns the key length a record type. This is the sum of the key fields of the record type plus the case identifier in case structured databases.

Returns X rounded to a number of decimal places. Express n as powers of 10, negative for numbers smaller than 1. Omit n or specify 0 to round to integers. Rounding is done by adding 0.5*10ⁿ to positive numbers, subtracting 0.5*10ⁿ from negative then truncating.
Examples:
RND( 1234.5678 , -3 ) = 1234.568
RND( 1234.5678 , -2 ) = 1234.57
RND( 1234.5678 , -1 ) = 1234.6
RND( 1234.5678 , 0 ) = 1235
RND( 1234.5678 , 1 ) = 1230
RND( 1234.5678 , 2 ) = 1200
RND( 1234.5678 , 3 ) = 1000

Renames a file from oldname_str to newname_str. Returns 0 for a success and -1 for fail.

Client/Server function. Returns a count of rows created by the execute command. When using ODBC, this depends on the ODBC source and may not be available (returns -1).

Returns the read security level for a record type.

Returns the read security level for a variable.

Returns string arguments from the parameter list. The argument is the position in the list. If it is greater than the number of parameters in the list, undefined is returned. (Numeric parameters are retrieved with the NARG function. NARG(0) returns the number of parameters in the list.)

Returns a substring of the input string. The second argument, start_pos, specifies the position within the input string at which the substring begins. The third argument specifies the number of characters to retrieve from the input string. If any of the arguments are undefined or missing, undefined is returned. If the starting position is larger than the length of the input string, undefined is returned.

Sets the master difference file copy interval in minutes. If master has been started with a password, this must match the quoted password, otherwise any name up to eight characters can be used.

Sets the default directory.

Sets the position of a multi-line gui control.

Sets the return code SIR2002 will send to the operating system when it finishes.

Returns the value of the specified string variable. The argument is a string variable, quoted string constant or string expression whose value is the name of a common, record or program variable.

Returns the string value of a global variable. The argument is a string variable, constant or expression which specifies a global variable name. If it is not the name of a defined global variable, undefined is returned. Use the NGLOBAL function for numeric global variables.

Transfers the sign (positive or negative) of num_Y to the absolute value of num_X. Zero is positive.

Returns the trigonometric sine of radnum, where radnum is specified in radians.

Returns the maximum valid string value for the specified variable. If the record number (rtnum) is negative, the function applies to a summary variable.

Returns the minimum valid string value for the specified variable. If the record number (rtnum) is negative, the function applies to a summary variable.

Returns a string with a blank inserted between each character of the input string.

Stores the value of string argument str in the specified string variable. The variable name argument is a string variable name, quoted string constant or string expression whose value is the name of a common, record or program variable. The value returned by the function is the value actually stored in the variable (possibly undefined, missing, etc.). If the variable refers to a common or record variable, the Retrieval must be in UPDATE mode.

Returns the square root of X. Missing is returned for negative values.

Returns the location of the value Z in the table of values VarX to VarY, where VarX and VarY are local numeric variables defined in the program from VarX to VarY. These cannot be arrays or string variables. The values in the variables must be in ascending order.

For example, if Z matched the fourth value in the table, SRCH returns 4. If no match is found, a negative value is returned. The value indicates the correct position for Z for in the table. For example:

SET INCOME1 TO INCOME8 (1,1.5,2.2,2.5,3,3.1, 3.5, 4)
COMPUTE LOC = SRCH (INCOME1,INCOME8,SALARY)
If SALARY has the value 3.5, the function returns 7, because the seventh variable has the value 3.5. If SALARY has the value 2, the function returns the value -3 indicating that the value is not present, and that the correct place in the list would be in the third position.

Pops up a box on the screen with a prompt and returns a string from the user. The maximum input field is 80 characters which are horizontally scrolled.

Returns the column number within strX that matches strY. If strY is delimited by characters other than letters or numbers, SRST returns a positive number, otherwise SRST returns a negative value. If strY is not a substring of strX, SRST returns a zero value. The length of strX must be greater than or equal to the length of strY. For example:

POS1 = SRST ('BUBBLE GUM','GUM') results in: POS1 = 8
POS2 = SRST ('ANITA TINKLE',' ') results in: POS3 = -6

Indicates if the variable is a control or observation variable. If the record number (rtnum) is negative, the function applies to a summary variable.

0 = not a control or observation variable.
1 = observation var
2 = control var

Returns the standard deviation for the values in the list that are not missing or undefined. If fewer than 2 values are not missing or undefined, a value of undefined is returned.

Returns the standard deviation for the values of the specified variable encountered during a PROCESS REC or PROCESS ROWS loop that are not missing or undefined. If fewer than 2 values are not missing or undefined, a value of undefined is returned.

Same as SBST function. Returns a null string if the starting position is outside the length of the string.

Returns the sum of the values in the list that are not missing or undefined. The maximum number of variables allowed in the argument list is 128. Arguments must be numeric.

Returns the sum of the values of X encountered during a PROCESS REC or PROCESS ROWS loop that are not missing or undefined.

Returns the value of the nth valid value of a categorical string variable. If the record number (rtnum) is negative, the function applies to a summary variable.

Extracts a wide variety of information from an executing VisualPQL program set. Some return undefined if not in a RETRIEVAL.

SYSTEM(1) returns cpu time elapsed since beginning of run

SYSTEM(2) returns the release level of this version of the software

SYSTEM(3) not used

SYSTEM(4) returns a 1 if current case is available for processing, returns a 0 if current case is not available for processing

SYSTEM(5) returns a 1 if current case has been modified, returns a 0 is current case has not been modified

SYSTEM(6) returns a 1 if current record is available for processing, returns a 0 if current record is not available for processing

SYSTEM(7) returns a 1 if current record has been modified, returns a 0 if current record has not been modified

SYSTEM(8) if current case is available for processing, returns the number of records of all types belonging to the current case

SYSTEM(9) returns the current output file page number

SYSTEM(10) returns the lines remaining on current output file page

SYSTEM(11) returns the total number or errors in the session

SYSTEM(12) returns the number of errors during the current task

SYSTEM(13) returns the number of warnings during the current task

SYSTEM(14) returns a 1 if last CASE IS block was executed, returns a 0 if last CASE IS block was not executed

SYSTEM(15) returns a 1 if last CASE IS block created a case, returns a 0 if last CASE IS block did not create a case

SYSTEM(16) returns a 1 if last RECORD IS block was executed, returns a 0 if last RECORD IS block was not executed

SYSTEM(17) returns a 1 if last RECORD IS block created a record, returns a 0 if last RECORD IS block did not create a record

SYSTEM(18) returns current row block number

SYSTEM(19) returns current row block position. Can be used to save a row position and retrieve the data with an OLD ROW IS AT (block,pos)

SYSTEM(20) returns the number of cases (CIRs) created during current run

SYSTEM(21) returns the number of cases (CIRs) updated during current run

SYSTEM(22) returns the number of cases (CIRs) deleted during current run

SYSTEM(23) returns the database update level

SYSTEM(24) returns the number of cases in database

SYSTEM(25) returns the number of data records in database

SYSTEM(26) returns the line width of current output page

SYSTEM(27) returns a 1 if the last ROW IS block was executed, returns a 0 if the last ROW IS block was not executed

SYSTEM(28) returns a 1 if the last ROW IS block created a row, returns a 0 if the last ROW IS block did not create a row

SYSTEM(29) returns a 1 if the current row is available for processing, returns a 0 if the current row is not available for processing

SYSTEM(30) returns a 1 if the current row was modified, returns a 0 if the current row was not modified

SYSTEM(31) returns the row ordinal of the current row

SYSTEM(32) returns the number of rows in the table

SYSTEM(33) Obsolete - used to return amount of free table space

SYSTEM(34) returns amount of table space, in SIR double words, used for the VisualPQL execution stack

SYSTEM(35) returns the amount of table space, in SIR double words, used for the program schema maps

Returns the variable name and sort sequence of nth variable on index.

Returns whether nth index is unique 0 - Not unique, 1 - Unique.

Returns the number of variables in nth index.

Returns the name of nth table. TFTABS(fn) returns number of tables on nth tabfile.

Returns the number of rows on the nth table. TFTABS(fn) returns number of tables on nth tabfile.

Returns the number of variables in nth table.

Returns various numeric data about the nth variable in nth table. The type of data is set by the fourth parameter as follows:

  1 = Count of value labels
  2 = leading zero
  3 = print this column
  4 = null not allowed
  5 = ON if value labels printed
  6 = set break variable
  7 = option G on break
  8 = option C on break
  9 = option P on break
 10 = var label as col heading
 11 = unique flag
 12 = subtotal title to the left
 13 = count of ranges
 14 = SIR data type

TABVINFS

Returns various string data about the nth variable in nth table. The type of data is set by the fourth parameter as follows:

 1 = variable label
 2 = LNEG
 3 = LPOS
 4 = NULL
 5 = ZERO
 6 = TNEG
 7 = TPOS
 8 = SEPARATE
 9 = date/time format (COL should have date/time type)
 10= break string

TABVNAME

Returns the variable name for vnth var on tnth table.

Returns a string representation of the value(s) for the rnth range for vnth var on tnth table. String starts with keyword VALID or MISSING to indicate the type of range. Then string may have two values separated by :. Also may contain keywords BLANK, LOWEST and HIGHEST.

Returns the variable type for vnth var on tnth table.

Validates the value in the expression (numeric or string) against the vnth var on tnth table. Returns a code indicating whether a value is allowed in a variable. The codes are:

0 = Valid value
Negative = Error detected
2 = Violation of specified valid values or ranges
3 - n Missing value 0 to n
4 = Missing value 1
5 = Missing value 2, etc.

Returns the label for the vlnth value label for vnth var on tnth table.

Returns the value for the vlnth value label for vnth var on tnth table.

Returns the trigonometric tangent of X, where x is in radians. If X is an odd integral multiple of p/2 (e.g., p/2, 3p/2, 5p/2, etc.), the value of undefined is returned.

Returns the hyperbolic tangent of X.

Returns an integer which is the number of seconds from midnight. The input argument, X, is an integer in the range 0 - 235959; the first two digits are hours, the next two are minutes and the last two are seconds. For example, to calculate the number of seconds from midnight to 8:30 AM.
SLEEPSEC = TIME(083000)

Converts an integer, X, into a time formatted string. In the time format the letters H, M and S are converted to the corresponding integer parts of the "time integer". 'P' returns "A" or "P"; 'PP' returns "AM" or "PM", and the hours are reported in 12-hour format. All other characters are passed directly to the output string. Otherwise a 24-hour format time is returned. Values of X that are out of range are returned as undefined. For example:

WAKESTR = TIMEC(ALARM, 'HH:MM:SS')

Returns a string with the time map of the specified time variable. If the variable is not a time variable, undefined is returned. For example, TIMESTR equals "HH:MM:SS" if time variable INTIME has that time map. If the record number (rtnum) is negative, the function applies to a summary variable.

Returns the "date integer" representation of the current date. The argument is a dummy numeric argument (specify 0).

Deletes trailing blanks from the string variable, expression or constant A reducing its length.

Deletes leading blanks from the string variable, expression or constant A and reduces its length accordingly.

Deletes leading and trailing blanks from the string variable, expression or constant A and reduces its length accordingly.

Identical to TRIM function.

Returns X, truncating to a number of decimal places. If n is omitted or 0, truncates to integers. Specify n as powers of 10, negative for numbers less than 1. Identical to AINT function.
Examples:
RND( 1234.5678 , -3 ) = 1234.567
RND( 1234.5678 , -2 ) = 1234.56
RND( 1234.5678 , -1 ) = 1234.5
RND( 1234.5678 , 0 ) = 1235
RND( 1234.5678 , 1 ) = 1230
RND( 1234.5678 , 2 ) = 1200
RND( 1234.5678 , 3 ) = 1000

Writes the specified string variable, constant or expression to the scrolled output window. This function is especially useful when a program is run with an alternate output file (i.e., with the interactive SET OUTPUT command). This function can only be used during an interactive session.

Returns the current database update level. (Same as SYSTEM(23).)

Changes lowercase letters to uppercase.

Returns a code indicating whether a value is allowed in a variable. If the record number (rtnum) is negative, the function applies to a summary variable. The codes are:

0 = Valid value
1 = Wrong data type/Not valid value
2 = Violation of specified valid values or ranges
3 = Missing value 0 (Undefined or system missing value)
4 = Missing value 1
5 = Missing value 2
6 = Missing value 3

Returns a character string containing the value label for the current value of the specified variable. If there is no label defined for the value, a zero length string is returned. The argument is a variable name, not a constant or expression.

Returns the value label for the specified value of a variable padded with blanks to 40 long. If the record number (rtnum) is negative, the function applies to a summary variable. The value can be numeric or string.

For example, suppose the fourth value label for a variable DIVISION in record type 2 has value 10, label 'Head Office' then:

THISPOS = VALLABSC (2, 'DIVISION',10)

Returns the nth value label for a variable padded with blanks to 40 long. If the record number (rtnum) is negative, the function applies to a summary variable. Note that the number of labels is returned by NVALLAB.

For example, suppose the fourth value label for a variable DIVISION in record type 2 has value 10, label 'Head Office' then:

THISPOS = VALLABSN (2, 'DIVISION',4)

Returns the position (nth) of the specified value associated with value labels for a variable. If the record number (rtnum) is negative, the function applies to a summary variable. The value can be numeric or string.

For example, suppose the fourth value label for a variable DIVISION in record type 2 has value 10, label 'Head Office' then:

POS = VALLABSP (2, 'DIVISION',10)

Returns the nth value associated with value labels for a variable. If the record number (rtnum) is negative, the function applies to a summary variable. Note that the number of labels is returned by NVALLAB.

For example, suppose the fourth value label for a variable DIVISION in record type 2 has value 10, label 'Head Office' then:

THISPOS = VALLABSV (2, 'DIVISION',4)

Returns a string representation of the value in the variable named in the expression. This works on all variable types. It converts catvar, date, time, integer and real to a string according to the format of the specified variable as per the VFORMAT function. Specify a string variable or expression which contains the name of another variable. For example:

INTEGER*1 INT1
STRING*8 STR1 STR2
COMPUTE INT1 = 1; STR1 = 'INT1'
COMPUTE STR2 = VARGET (STR1)
WRITE STR1 STR2

Output is:   INT1       1

Returns the variable label for the specified variable. If there is no label defined for the variable, a zero length string is returned. The argument is a variable name, not a constant or expression.

Returns the variable label for a variable. If the record number (rtnum) is negative, the function applies to a summary variable. In the following example, if MARSTAT in record type 1 has a variable label of "Marital Status", then COLHEAD equals "Marital Status" (padded with blanks to 64 characters).
COLHEAD = VARLABSC (1, 'MARSTAT')

Returns the nth line of a variable label for a variable. If the record number (rtnum) is negative, the function applies to a summary variable. In the following example, if MARSTAT in record type 1 has a variable label of "Marital Status", then COLHEAD equals "Marital Status" (padded with blanks to 64 characters).
COLDESC = VARLABSN (1, 'MARSTAT', n)

Returns the name of the specified variable. The first argument is the record number in which the variable occurs; the second argument is the number of the variable (not including common vars). Variables are numbered in the order in which they are defined in the record schema definition. The string returned is padded with blanks to eight characters.

Returns the name of the specified variable. The first argument is the record number in which the variable occurs; the second argument is the number of the variable (including common vars). Variables are numbered in the order in which they are defined in the record schema definition. The string returned is padded with blanks to eight characters.

Returns the data length of the specified variable. The first argument is the record number in which the variable occurs; the second argument is the name of the variable.

Returns the position in the data record of the specified variable. The first argument is the record number in which the variable occurs; the second argument is the name of the variable.

Places the value from the second argument into the first argument converting from a string to a numeric value if necessary. Var_exp contains the name of a variable. If the variable is a numeric variable, the string is converted to the appropriate numeric value and stored in the variable.

Returns the same numeric values as VALIDATE.

0 = Valid value
1 = Wrong data type/Not valid value
2 = Violation of specified valid values or ranges
3 = Missing value 0 (Undefined or system missing value)
4 = Missing value 1
5 = Missing value 2
6 = Missing value 3

For example:

COMPUTE RES = VARPUT ('BIRTHDAY','12/31/50')

Returns an integer code representing the type of the specified variable. The argument is a string variable, quoted string constant or string expression whose value is the name of a common, record or local variable. Codes are:

0         if variable is string
1         if variable is numeric
undefined if variable is undefined

Returns a string representing the format of the specified variable (for example, A20, F9.2, D'MMIDDIYY', etc.) If the record number (rtnum) is negative, the function applies to a summary variable. In the following example, if variable NEWSAL in record type 3 is a four digit integer, then XFMT equals "I4".

XFMT = VFORMAT (3, 'NEWSAL')

Returns an integer code representing the type of the variable, where:

1 = string
2 = categorical
3 = date
4 = time
5 = integer
6 = real (single precision)
7 = real (double precision)
8 = scaled variable

Returns the write security access level of the current user (the level corresponding to the write security password entered).

Returns the number of lines in the output window

Returns the nth line from the output window

Moves and resizes the main window. The window is placed at horizontal position x,vertical position y, size width w by height h. The horizontal units are 1/4 of the average width for the font being used. The vertical units are 1/8 font height. If w or h is zero the window is minimized; if w or h < 0, the window is restored to its original position (before being minimised).

Moves to line in output window and highlights from pos to len on that line

Returns the line selected. 0 returns the current cursor line. 1 returns beginning line of selection. 2 reurins ending line of selection.

Returns the position in the line selected. 0 returns the current cursor position. 1 returns beginning position of selection. 2 returns ending position of selection.

Returns the write security level for a record type.

Returns the write security level for a variable.

Displays a question box with the specified text and returns response. 1 indicates Yes; 0 indicates No.