Functions

List of Functions by Type

Trigonometric Functions

Mathematical Functions

Argument List Functions

Across Record Functions

Date and Time Functions

Global Functions

String Functions

Concurrent Functions

Miscellaneous Functions

Session Functions

Schema & Database Functions

Tabfile & Table Functions

Read/Write Functions

Dialog & Menu Functions

Client/Server Functions

Client Functions to administer Master

CGI Functions

Alphabetic List of All Functions

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 SIR/XS 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 ACOS function.

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

Binds an sqlserver/odbc parameter.

Creates a new node under the given parent node in the tree control.

Deletes a branch in the tree control given by the node.

Returns id of nth child of the given node.

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 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 with the internal value of 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).

Returns a hex representation of the input string. Each character is converted to its ASCII value then that is converted to hexadecimal.
COMPUTE HEX1 = CHARHEX("Hello World")

Converts the hexadecimal codes in the input string to their associated ASCII characters.
COMPUTE STR = HEXCHAR("48656C6C6F20576F726C64")

Counts the number of values in a list that exist (not missing or undefined). There may be up to 128 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.

Copies a file from oldname_str to newname_str. Returns 0 for a success, -1 for a problem opening the source file, -2 for a problem opening the target file and -3 for errors during the copy.

Sets the key used by the encryption functions ENCRYPT/DECRYPT. The key is a 256 bit (32 character) string. The key only needs to be set once in a session that uses the encryption functions. If the key is not set, the encryption functions use a key of all blanks.

The specified key is an expression. i.e. a variable name or a string in quotes.

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

Returns the current directory.

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

Takes a date and time integer and returns a timestamp. A timestamp is a real*8 representation and is the number of seconds since the start of the SIR/XS calendar. You can do calculations between timestamps but the individual date and time components must be extracted using the TSTODT and TSTOTM functions before using any other date and time functions e.g. for print formatting.

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 the index is unique or 0 if the index is not 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.

Decrypts an encrypted string. Set the encryption key (using CRYPTKEY prior to the first invocation of this function. Obviously the key must be the same as was used to encrypt the string.

Returns the default family name.

Returns the default member name.

Returns the default tabfile 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 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 ( ^ ), that represent a character of the input string, and any other characters to insert. Example:

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

Output: 123-45-6789

Returns the name of the current text editor.

Encrypts a string. Set the encryption key (using CRYPTKEY) prior to the first invocation of this function.
The string is a SIR string and the encrypted string is also a normal SIR string but it should be noted that encryption may result in non-text characters and so encrypted strings should not be written to text files.
The encryption algorithm encrypts eight (8) characters at a time and any input string is padded with blanks so that the output is a correctly encrypted string. If you truncate this and save an encrypted string that is not a multiple of eight, the last few characters will not decrypt properly.

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.

Invokes a user-supplied external function from the EXTERN dll. The function can take a numeric or string parameter and calls a different user function for each case. The extern.dll library supplied by SIR contains dummy functions which return zero.

Invokes a user-supplied external function from the EXTERN dll. The function can take a numeric or string parameter and calls a different user function for each case. The extern.dll library supplied by SIR contains dummy functions which return blank (a zero length string).

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 the string argument. The string must be a filename or mask. For example, specify a mask like '*.pql' for the count of those files with extension "pql" 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 directory given by the string argument. The string must be a filename or mask. For example, to list all files with extension "pql" in the current directory:

FOR N = 1,FILECNT("*.pql")
. WRITE [FILEN("*.pql",N)
END FOR

FILEOUT

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 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 automatic disconnection timeout for idle clients (from Master) in minutes.

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 SIR/XS 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 the position of the nth selected item from a single or multiple selection list. In the case of a single selection list then GETMSEL(id,1) is the same as GETPOS(id).

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 or returns 1 for a single selection list.

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 converts 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 (ASCII).

Returns the status of a GUI element. -1 = the control does not exist; 0 = the control is disabled; 1 = the control is enabled.

Returns whether journaling 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 are month, day and year. A "date integer" is the number of days since the start of the Gregorian calendar on October 15, 1582.
Years can be specified as various values. If a year of zero is specified, this year is used. If a year of less than 10 is specified, this decade is used. If a year between 10 and 99 is specified, the CENTYR parameter is used to determine the appropriate century to use. If a year between 100 and 999 is specified, then values greater than 583 are taken to be in the last millennium (1583 - 1999), values smaller than 583 are taken to be in the current millennium (2000+).
A value of undefined is returned if a date earlier than October 15, 1582 is specified. Because of leap years, this routine is only accurate for dates up to Dec 31 29999.
For example:

DURATION  =  JULN(4, 8, 0) - 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 ALOG10 function.

Returns number of lines remaining on current page being written to a file.

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 of up to 128 variables that is not missing or undefined.

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 maximum value in the list of up to 128 variables.

Returns the maximum 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 information about the named member. The member name can include the :type qualifier. The function returns missing if the member does not exist.

The INFOTYPES are:

1. TYPE - returns 1 for :T; 2 for :E; 4 for :O ; 8 for :V
   If the member type is not given in the member name, and there is more than one type for this name then the sum of the types is returned. e.g. SYSPROC.MENU.ABOUT is a :t and an :o so
   MEMINFO ("SYSPROC.MENU.ABOUT",1) returns 5
   MEMINFO ("SYSPROC.MENU.ABOUT:O",1) returns 4
   MEMINFO ("SYSPROC.MENU.ABOUT:E",1) returns missing.
   
2. SECURITY returns 0 (no password), 1 (password & public), 2 (password & not public).
3. LENGTH returns the number of bytes in the member (note there is some condensing going on here).
4. CREATE DATE returns the creation date as a Julian date integer.
5. CREATE TIME returns the creation time as number of seconds since midnight.
6. MOD DATE returns the last modification date as a Julian date integer.
7. MOD TIME returns the last modification time as number of seconds since midnight.
8. STATUS (mainly for :e :o and :v types):
   0 - good
   -1 incorrect version of SIR
   -2 Default database not connected
   -3 Database creation date/time mismatch
   -4 incorrect PQL Retrieval version
   -5 CIR/Record Schema level mismatch.
   

MEMNAME

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

Returns the minimum value in the list of up to 128 variables.

Returns the minimum 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 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 double words that are equivalent to eight characters.

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)

Returns number of branches of the given node in a tree control.

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

See NVALLAB.

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. This 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. strX is a string constant, variable name or expression and 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 variables (not Common) defined in the specified record type.

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

Client/server function. Does an ODBC query that produces a result set that 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 that produces a result set that 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 for specified file.

Returns the current page number on specified file.

Returns the current setting for page width on specified 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@')

PICTURE

Validates a string according to a specified picture. A picture is a string of characters, enclosed in quotes. string. Within the picture certain characters have special meanings: 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 string matches the picture. The second example returns a 6 to show that the string does not match the picture in the sixth position:

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

Returns the attribute of the default procfile. In SIR/XS this is always PROCFILE.

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 normal way to call the function is with a dummy argument of zero. Multiple calls then return a sequence of random numbers. If the function is called with a number as the argument, this resets the seed and returns the first random number generated from that seed. For a given seed, the same sequence of random numbers is generated. You can also alter the default seed by specifying a seed on the RETRIEVAL or PROGRAM command.

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

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

Searches a string for the nth occurrence of a substring as specified by the regular expression. Returns a negative number if there is an error in the regular expression, 0 if not found in string or n where n is start position of nth occurrence of found string.

A regular expression is one where special characters describe the matching that is required. The meaning of the special characters needs to be specified and there is a standard for regular expressions used by many packages. SIR has had its own regular expression processor and these functions REGEXP/REGREP allow you to choose whether to use standard PERL or POSIX expressions or SIR expressions. A Style of 1 specifies SIR expression, 2 specifies PERL and 3 specifies POSIX. PERL is the default. Please see standard documentation for PERL and POSIX for a full explanation of the syntax of their regular expressions.

SIR Expressions

Any special meaning of characters in an expression is lost when escaped, inside [...] or in the following cases:
% not at the beginning ( eg: [0-9.]+%);
$ not at the end (eg: $[0-9.]+);
* at the beginning of a pattern;
+ at the beginning;

A character class consists of zero or more of the following elements, surrounded by [ and ]:
c literal character, including [
a-c range of characters (digits, lower or upper case)
! negated character class (if at beginning)
@c escaped character (@!, @-, @@, @])
Special meaning of characters in a character class is lost when escaped or for:
! not at the beginning
- at the beginning or end

Any part of expression may be specified to be 'tagged':
< start a tagged substring
> end a tagged substring
(Tagged substrings are numbered from left to right. See the substitution expression for replacement of tagged substrings.)
A substitution pattern consists of zero or more of the following elements:
c literal character
& ditto, i.e. whatever was matched
@c escaped character (@&)
@n tagged substring insertion
An escape sequence consists of the character @ followed by a single character:
@n end of line
@t tab character
@c any other character (including @@)

Examples:

PROGRAM
c
c Put numbers in square brackets
c
. STRING*80 TEXT
. SET TEXT ("this is 123 or 456 test")
. COMPUTE TEXT = REGrep(TEXT,"[0-9]+","[&]",1,1)
. WRITE TEXT
END PROGRAM

RETRIEVAL
. PROCESS CASES
.   PROCESS RECORD 1
.     GET VARS NAME
.     WRITE [REGREP(NAME,"<[A-Za-z'-]*><[ A-Z]*> <[A-Za-z'-]*>","@3   "+CHAR(9)+"@1 @2 ",1,1)]
.   END RECORD
. END CASE
END RETRIEVAL

PROGRAM
c
c Find words in a string
c
STRING*80 TEXT
SET TEXT ("Find the words in a string")
SET N (1)
LOOP
. COMPUTE POS1 = REGEXP(TEXT,"\w*(\W|$)",N,3)
. COMPUTE POS2 = REGEXP(TEXT,"\w(\W|$)",N,3)
. IF (POS1 EQ 0) EXIT LOOP
. WRITE "WORD# " N [SBST(TEXT,POS1,1+POS2-POS1)]
. COMPUTE N = N + 1
END LOOP
END PROGRAM

PROGRAM
c
c Check an email address
c
STRING*80 TEXT
SET TEXT ("")
LOOP
. DISPLAY TEXTBOX "Enter a valid email address" response RC,TEXT
. IFNOT (RC GT 0) EXIT LOOP
. IFTHEN (REGEXP(TEXT,"^[A-Za-z0-9._%-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,4}$",1,3) EQ 0)
.   DISPLAY ERRORBOX " is an invalid email address"
.   NEXT LOOP
. ELSE
.   DISPLAY INFOBOX  "Thank you"
.   EXIT LOOP
. ENDIF
END LOOP
END PROGRAM

REGREP

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

Renames a file or directory 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 where 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 position of currently open file where position is the number of characters to move from the current location in the file. If position is -1 it moves to EOF; if position is -2 then it doesn't move and just returns current position; if position is -3 it moves to the start of the file. To move to an absolute position, first move to the start of the file and then to the position.

Client function for PQLServer. Server administration function.
Function types:
1 - number of server clients
2 - server client id of nth client
3 - close server client id (not us) (password)
4 - server client id logon time
5 - server client id last message time
6 - shutdown server
7 - shutdown server when no clients (password)
The server client id is required on function types 3,4 and 5. Server client ids are returned by function type 2. Use the nth client number as the client id on function type 2 (0 returns our server client id). Use 0 on function types 1,6 and 7.
Do not use SERADMIN function type 3 to close this client.

Client function for PQLServer. Server admin functions that return a string value.
Function types:
1 - address/name of client id (0 returns our name)

Send to server to execute previous sent commands.
0 Wait means return without waiting;
1 means wait one interval, 2 two intervals, etc. The basic server timeout interval can be set on the sir.ini file- the setting is called server.timeout. If not set, the default is 5 seconds.
Returns completion code:
-1 - Timeout. Note that the server continues to run the request - it is just taking longer than expected. The SERTEST function can be used subsequently to inquire as to status.
0 Completed OK
1 Completed with warnings
2 Completed with errors
Other values can set by user program on server using the SETRC (RC) function (note user return codes are returned as positive numbers so should avoid values of 1 or 2).

Returns a single line of output. If the number of lines to skip is 0, the next line is returned, otherwise it is the line after the skipped lines.

Returns the number of lines left. If this function is invoked after lines are returned or skipped, it returns the remaining number of lines.

Logs on/off to the server. This first logs off any current connection then, if the name of the server to logon to is not blank, this is used to try to log on to. Returns -1 if the logon fails. A string containing the password is required. If the server is started with UPASS specified then the password must match the server administration password. Note that while specified as a string or string variable, the password is a SIR/XS name and is uppercase if not a non-standard name in curly brackets {}.

PQLServer side function that controls SERVER NOOUTPUT flag. If flag is set on, then any output directed to standard output is thrown away. The flag is set off initially for each client execution. The setting is still maintained once the program that uses this function ends.
n = 0 returns setting 1 - On 0 Off
n = 1 sets no output on
n = -1 sets no output off

Sends string to the server. The string is a line of input.

Sends contents of buffer to the server.

Use if haven't waited for SEREXEC to complete or had a timeout on the execution. Returns same completion codes as SEREXEC

PQLServer side function that writes lines to output regardless of setting of SERVER NOOUTPUT flag

Sets the client autokill limit in minutes. If a client is idle for the given number of minutes then they will be automatically disconnected by master. If master has been started with a password, this must match the quoted password, otherwise any name can be used.

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 can be used.

Sets the default directory.

Sets the position of a multi-line GUI control.

Sets the range of a GUI spin/slider/progress control.

Sets the maximum number of characters allowed in an edit or text field (ignoring the minimum parameter).

Sets the return code SIR/XS sends 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 that 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.

Sets/returns the current SIR/XS user name. This is written to the database journal to identify the person who entered particular sets of database updates. Specify a name (up to 32 characters). No translation (upper/lower case, etc.) is done. Simply to return the current user name, pass either a missing name or a zero length name.

USER = SIRUSER ('') returns existing name to string variable USER
USER = SIRUSER ('Mr. J. Smith') sets the name and returns it to string variable USER.

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 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 is in the third position.

Pops up a box on the screen with the specified prompt and returns a string from the user. The maximum input is 4094 characters; long strings are scrolled horizontally.

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

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.

Checks if name is standard and puts curly brackets around non-standard names. The function ignores leading and trailing spaces and any trailing characters over legal name length. If the name has leading or trailing quotes or curly brackets, these are stripped off. A standard name starts with an uppercase letter and contains only uppercase letters, digits or the four characters $, #, @, and _. If it is a standard name, it is returned left justified. (Note that this function does NOT translate lower case letters to uppercase. Any lowercase means that the name is non-standard.
If it is a non-standard name, it is wrapped in curly brackets and returned left justified. If a name has embedded curly brackets, 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.

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

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 the access type a(uto),r(ead) w(rite) of nth tabfile.

Returns the internal attribute name of nth tabfile. This is the same as the tabfile name.

Returns the number of connected tabfiles.

Returns the filename of nth tabfile.

Returns the group name of nth tabfile.

Returns the group password of nth tabfile.

Returns the journal name of nth tabfile.

Returns the name of nth tabfile.

Returns the number of tables on nth tabfile.

Returns an integer that 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)

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

Deletes trailing blanks from the string expression.

Deletes leading blanks from the string expression.

Deletes leading and trailing blanks from the string expression.

See TRIM function.

See AINT function.

Takes a real*8 timestamp (produced by DTTOTS and returns the date integer component.

Takes a real*8 timestamp (produced by DTTOTS and returns the time integer component.

Writes the specified string expression to the scrolled output window. This function can be useful when a program is run with an alternate output file (i.e., with the interactive SET OUTPUT command) and 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
7 = String too long / extra data after number - truncated

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 nth line of documentation for a variable. Use the NVARDOC function to find total number of lines. (Note that variable documentation does not apply to summary variables. Simply use comment lines in programs to document.) In the following example, if MARSTAT in record type 1 has a single line of variable documentation of "Current marital status of employee", then COLDESC is set to that string value.".
COLDESC = VARDOCSN (1, 'MARSTAT', 1)

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 that 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

Note: VARGET does not return original missing values. If you need to get the original missing value of a variable named at run time then you could either use the EVALUATE command (slow) or use SGET/NGET to copy the variable into a predefined variable with known missing values. Then use the MISNUM function on these known variables and MISS on the original variable with the result of MISNUM: real* 8 temp# string temp$ missing values temp# (blank,blank,blank) missing values temp$ (blank,blank,blank) ... c c Get the original missing value of local variable who's name is in the variable VARNAME c . IFTHEN (VTYPE(-1,varname) gt 1) . COMPUTE temp#=NGET(varname); COMPUTE missnum =MISNUM(temp#) . ELSE . COMPUTE temp$=SGET(varname); COMPUTE missnum =MISNUM(temp$) . ENDIF . IF (EXISTS(missnum) = 0 or missnum > 0) WRITE [FST(VARGET(varname),MISS(-1,varname,missnum))] ...

Returns the variable label (up to 78 characters) for a variable. If there is no label defined for the variable, the variable name is returned. The argument is a variable name, not a constant or expression. If the function is compiled in a record block, a record variable can be specified. If the function is compiled in a case block, a common variable can be specified. If the function is outside and record or case block, then specify a local, summary variable.

Returns the label (up to 78 characters) for a variable. If there is no label defined for the variable, the variable name is returned. The first argument is the record number where the variable occurs or 0 for common variables. Use a negative value to specify a local, summary variable. The second argument is a constant (in quotes) or an expression that resolves to a variable name. In the following example, if MARSTAT in record type 1 has a variable label of "Marital Status", then COLHEAD equals "Marital Status".
COLHEAD = VARLABSC (1, 'MARSTAT')

Returns the name of the specified variable. The first argument is the record number where the variable occurs or 0 for common variables. Use a negative value to specify a local, summary variable. The second argument is the number of the variable (not including common vars). Variables are numbered in the order they are defined in the record schema definition. If the name is a non-standard name, it is returned enclosed in curly brackets {}.

Returns the name of the specified variable. The first argument is the record number where the variable occurs; the second argument is the number of the variable (including common vars). Variables are numbered in the order they are defined in the record schema definition.

Returns the data length of the specified variable. The first argument is the record number where 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 where 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
7 = String too long / extra data after number - truncated

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 the scaling factor of the variable if the variable is a scaled integer. Otherwise it returns 0 for a non scaled variable or undefined if the variable does not exist.

SFAC = VSCALE (3,'AMOUNT')

Returns the variable format exactly as specified in the schema DATA LIST. This is different from VFORMAT in that VFORMAT returns an external print format whereas the formats in the DATA LIST are a mix of input formats (eg DATE'MM/DD/YYYY')) and internal formats (eg I2 a two byte integer). A negative rtnum will return a schema like format for a local variable.

SCHFMT = VSCHFMT (1,'BIRTHDAY')

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 number of character required to print or display a 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 returns 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.