https://www.omnidex.com/docs/v50/ 2026-09-25T05:53:31+0000 https://www.omnidex.com/docs/v50/ https://www.omnidex.com/docs/v50/lib/tpl/simple/images/favicon.ico text/html 2012-10-26T14:28:01+0000 Anonymous (anonymous@undisclosed.example.com) https://www.omnidex.com/docs/v50/doku.php?id=dev:sql:functions:calc_date&rev=1351261681&do=diff Omnidex SQL Function: $CALC_DATE Omnidex SQL: Overview Statements Functions Examples Quick Ref OdxSQL: Overview Commands Settings Command Line Quick Ref Description Result = $calc_date(date, value [, unit [, options]) Returns a floating point value. Syntax $calc_date( <date_column_spec | 'date_string' > , value_expression [, date_unit [, options ] ) text/html 2012-10-26T14:28:01+0000 Anonymous (anonymous@undisclosed.example.com) https://www.omnidex.com/docs/v50/doku.php?id=dev:sql:functions:case&rev=1351261681&do=diff Omnidex SQL Function: CASE Omnidex SQL: Overview Statements Functions Examples Quick Ref OdxSQL: Overview Commands Settings Command Line Quick Ref Description The CASE function handles simple IF - THEN - ELSE logic at a select-item level. The WHEN conditions are evaluated in order until a condition proves TRUE, at which point the THEN expression is executed. All of the THEN expressions in a CASE statement must be of the same data type: numeric, character, or date. text/html 2012-10-26T14:28:01+0000 Anonymous (anonymous@undisclosed.example.com) https://www.omnidex.com/docs/v50/doku.php?id=dev:sql:functions:cast&rev=1351261681&do=diff Omnidex SQL Function: CASE Omnidex SQL: Overview Statements Functions Examples Quick Ref OdxSQL: Overview Commands Settings Command Line Quick Ref Description The CAST function converts expressions to a specified data type. Column formatting will be the default for the specified data type. Due to conflicts in specifications, the main OmniAccess data types are not supported here. The $CONVERT function should be used for OmniAccess data types and for more specific fu… text/html 2012-10-26T14:28:02+0000 Anonymous (anonymous@undisclosed.example.com) https://www.omnidex.com/docs/v50/doku.php?id=dev:sql:functions:char_length&rev=1351261682&do=diff Omnidex SQL Function: CHARCTER_LENGTH Omnidex SQL: Overview Statements Functions Examples Quick Ref OdxSQL: Overview Commands Settings Command Line Quick Ref Description Syntax Example Example 1 text/html 2012-10-26T14:28:02+0000 Anonymous (anonymous@undisclosed.example.com) https://www.omnidex.com/docs/v50/doku.php?id=dev:sql:functions:col_length&rev=1351261682&do=diff Omnidex SQL Function: CASE Omnidex SQL: Overview Statements Functions Examples Quick Ref OdxSQL: Overview Commands Settings Command Line Quick Ref Description The $COL_LEN and $COLUMN_LENGTH functions return the defined length of a column. The return data type is a 4-byte INTEGER. Both functions are identical. These functions differ from the CHAR_LENGTH and CHARACTER_LENGTH functions: text/html 2012-10-26T14:28:02+0000 Anonymous (anonymous@undisclosed.example.com) https://www.omnidex.com/docs/v50/doku.php?id=dev:sql:functions:concatenation&rev=1351261682&do=diff Omnidex SQL Function: CASE Omnidex SQL: Overview Statements Functions Examples Quick Ref OdxSQL: Overview Commands Settings Command Line Quick Ref Description The || (two vertical bars) function returns a single concatenated character string made up of two or more character strings. CHAR||CHAR will return a CHAR string; everything else will return a C STRING. text/html 2012-10-26T14:28:02+0000 Anonymous (anonymous@undisclosed.example.com) https://www.omnidex.com/docs/v50/doku.php?id=dev:sql:functions:convert&rev=1351261682&do=diff Omnidex SQL Function: $CONVERT Omnidex SQL: Overview Statements Functions Examples Quick Ref OdxSQL: Overview Commands Settings Command Line Quick Ref Description The $CONVERT function converts a scalar_expression from one data type to another of length n. This function may also be used for formatting data. See Also: CAST text/html 2012-10-26T14:28:02+0000 Anonymous (anonymous@undisclosed.example.com) https://www.omnidex.com/docs/v50/doku.php?id=dev:sql:functions:current_date&rev=1351261682&do=diff Omnidex SQL Function: CURRENT_DATE Omnidex SQL: Overview Statements Functions Examples Quick Ref OdxSQL: Overview Commands Settings Command Line Quick Ref Description The CURRENT_DATE function returns the current date from the system's time-of-day clock. The function is processed once at the beginning of execution of the SQL statement. A new value is not obtained with each reference or with each fetched row. text/html 2012-10-26T14:28:02+0000 Anonymous (anonymous@undisclosed.example.com) https://www.omnidex.com/docs/v50/doku.php?id=dev:sql:functions:current_row&rev=1351261682&do=diff Omnidex SQL Function: $CURRENT_ROW Omnidex SQL: Overview Statements Functions Examples Quick Ref OdxSQL: Overview Commands Settings Command Line Quick Ref Description Returns the current row as an ascending number from 1. This number is assigned after all other clauses are processed. This function may only be used as a select-item. It may NOT be used in a WHERE , GROUP BY, ORDER BY, or HAVING clause. text/html 2012-10-26T14:28:02+0000 Anonymous (anonymous@undisclosed.example.com) https://www.omnidex.com/docs/v50/doku.php?id=dev:sql:functions:current_time&rev=1351261682&do=diff Omnidex SQL Function: DATE_TIME Omnidex SQL: Overview Statements Functions Examples Quick Ref OdxSQL: Overview Commands Settings Command Line Quick Ref Description The CURRENT_TIME function returns the current date from the system's time-of-day clock. The function is processed once at the beginning of execution of the SQL statement. A new value is not obtained with each reference or with each fetched row. text/html 2012-10-26T14:28:02+0000 Anonymous (anonymous@undisclosed.example.com) https://www.omnidex.com/docs/v50/doku.php?id=dev:sql:functions:current_timestamp&rev=1351261682&do=diff Omnidex SQL Function: CURRENT_TIMESTAMP Omnidex SQL: Overview Statements Functions Examples Quick Ref OdxSQL: Overview Commands Settings Command Line Quick Ref Description The CURRENT_TIMESTAMP function returns the current timestamp from the system's time-of-day clock. The function is processed once at the beginning of execution of the SQL statement. A new value is not obtained with each reference or with each fetched row. text/html 2012-10-26T14:28:02+0000 Anonymous (anonymous@undisclosed.example.com) https://www.omnidex.com/docs/v50/doku.php?id=dev:sql:functions:current_user&rev=1351261682&do=diff Omnidex SQL Function: CURRENT_USER Omnidex SQL: Overview Statements Functions Examples Quick Ref OdxSQL: Overview Commands Settings Command Line Quick Ref Description The USER, CURRENT_USER, SESSION_USER functions return the current user of Omnidex as entered when first connected to the current open environment. If no user was entered, the user of text/html 2012-10-26T14:28:02+0000 Anonymous (anonymous@undisclosed.example.com) https://www.omnidex.com/docs/v50/doku.php?id=dev:sql:functions:distance&rev=1351261682&do=diff Omnidex SQL Function: $DISTANCE Omnidex SQL: Overview Statements Functions Examples Quick Ref OdxSQL: Overview Commands Settings Command Line Quick Ref Description $DISTANCE calculates the distance between two geographical points. Syntax $distance(lat1, long1, lat2, long2, options) Options: * Units of measure * MILES (default) text/html 2012-10-26T14:28:02+0000 Anonymous (anonymous@undisclosed.example.com) https://www.omnidex.com/docs/v50/doku.php?id=dev:sql:functions:external&rev=1351261682&do=diff Omnidex SQL Function: $EXTERNAL Omnidex SQL: Overview Statements Functions Examples Quick Ref OdxSQL: Overview Commands Settings Command Line Quick Ref Description The $EXTERNAL function provides a gateway to user-provided functions, thereby allowing customized functions tailored specifically to a user's needs. This function will dynamically load routines from the shared-library specified in the library parameter, passing the remaining parameters. text/html 2012-10-26T14:28:02+0000 Anonymous (anonymous@undisclosed.example.com) https://www.omnidex.com/docs/v50/doku.php?id=dev:sql:functions:extract&rev=1351261682&do=diff Omnidex SQL Function: EXTRACT Omnidex SQL: Overview Statements Functions Examples Quick Ref OdxSQL: Overview Commands Settings Command Line Quick Ref Description The EXTRACT function extracts an individual field value from a specified date_class data type. Syntax EXTRACT(date_field FROM date) date_field Required. The date part that is to be extracted. See below for a complete list of date parts that can be used in this parameter. Click to see a list of valid datef… text/html 2012-10-26T14:28:02+0000 Anonymous (anonymous@undisclosed.example.com) https://www.omnidex.com/docs/v50/doku.php?id=dev:sql:functions:home&rev=1351261682&do=diff Omnidex SQL: Functions Omnidex SQL: Overview Statements Functions Examples Quick Ref OdxSQL: Overview Commands Settings Command Line Quick Ref Standard SQL Functions The Omnidex SQL Language supports several standard SQL Functions. These functions operate as defined in the SQL specification. SQL Functions Description text/html 2012-10-26T14:28:02+0000 Anonymous (anonymous@undisclosed.example.com) https://www.omnidex.com/docs/v50/doku.php?id=dev:sql:functions:ifnull&rev=1351261682&do=diff Omnidex SQL Function: $IFNULL Omnidex SQL: Overview Statements Functions Examples Quick Ref OdxSQL: Overview Commands Settings Command Line Quick Ref Description $IFNULL specifies a return value for columns that contain null values. The column data type and the return value data type must match. If column-value is NULL then return specified-value, else return column-value text/html 2012-10-26T14:28:03+0000 Anonymous (anonymous@undisclosed.example.com) https://www.omnidex.com/docs/v50/doku.php?id=dev:sql:functions:lj&rev=1351261683&do=diff Omnidex SQL Function: $LJ Omnidex SQL: Overview Statements Functions Examples Quick Ref OdxSQL: Overview Commands Settings Command Line Quick Ref Description The $LJ function will left-justify a string by eliminating the leading white space. The return data type is C STRING. The return length is: the length of the string if the string is a C STRING column the length of the string +1 if the string is a CHAR column or a literal. text/html 2012-10-26T14:28:03+0000 Anonymous (anonymous@undisclosed.example.com) https://www.omnidex.com/docs/v50/doku.php?id=dev:sql:functions:lower&rev=1351261683&do=diff Omnidex SQL Function: LOWER Omnidex SQL: Overview Statements Functions Examples Quick Ref OdxSQL: Overview Commands Settings Command Line Quick Ref Description Converts a string to all lower case characters. Syntax LOWER( <column_spec | "string"> ) Example Example 1 select LOWER (contact) as low from customers where state = 'co' low ------------------------- john doe jane johnson jim smith text/html 2012-10-26T14:28:03+0000 Anonymous (anonymous@undisclosed.example.com) https://www.omnidex.com/docs/v50/doku.php?id=dev:sql:functions:lpad&rev=1351261683&do=diff Omnidex SQL Function: $LPAD Omnidex SQL: Overview Statements Functions Examples Quick Ref OdxSQL: Overview Commands Settings Command Line Quick Ref Description The $LPAD function adds pad characters to the left of a character string up to the specified total length. The return data type is C STRING of length specified in the return_length parameter + 1. text/html 2012-10-26T14:28:03+0000 Anonymous (anonymous@undisclosed.example.com) https://www.omnidex.com/docs/v50/doku.php?id=dev:sql:functions:mod&rev=1351261683&do=diff Omnidex SQL Function: $MOD Omnidex SQL: Overview Statements Functions Examples Quick Ref OdxSQL: Overview Commands Settings Command Line Quick Ref Description Returns “n modulus y”. This is the remainder of n divided by y. “n” and “y” may be a column , expression, or literal. The return value is INTEGER length 4. text/html 2012-10-26T14:28:03+0000 Anonymous (anonymous@undisclosed.example.com) https://www.omnidex.com/docs/v50/doku.php?id=dev:sql:functions:position&rev=1351261683&do=diff Omnidex SQL Function: POSITION Omnidex SQL: Overview Statements Functions Examples Quick Ref OdxSQL: Overview Commands Settings Command Line Quick Ref Description Find the position of string1 in string2. The POSITION function returns the position of a string (string1) within another string (string2). If string2 is of length zero, the result is 1. If string1 occurs as a substring within string2, the character position where string1 first occurs is returned. If neithe… text/html 2012-10-26T14:28:03+0000 Anonymous (anonymous@undisclosed.example.com) https://www.omnidex.com/docs/v50/doku.php?id=dev:sql:functions:proper&rev=1351261683&do=diff Omnidex SQL Function: CASE Omnidex SQL: Overview Statements Functions Examples Quick Ref OdxSQL: Overview Commands Settings Command Line Quick Ref Description The $PROPER function shifts the first letter of each word in a string to upper case and all other letters in the string to lower case. Words are delimited by white space (tabs, newlines, blanks) or punctuation (: ; , . ! ?). text/html 2012-10-26T14:28:03+0000 Anonymous (anonymous@undisclosed.example.com) https://www.omnidex.com/docs/v50/doku.php?id=dev:sql:functions:random&rev=1351261683&do=diff Omnidex SQL Function: $RANDOM Omnidex SQL: Overview Statements Functions Examples Quick Ref OdxSQL: Overview Commands Settings Command Line Quick Ref Description Returns a pseudo-random number. The term “pseudo-random” is used as randomization is done using the Posix rand and srand functions. While these routines do provide satisfactory randomization for most applications, the numbers generated are not entirely random. text/html 2012-10-26T14:28:03+0000 Anonymous (anonymous@undisclosed.example.com) https://www.omnidex.com/docs/v50/doku.php?id=dev:sql:functions:rj&rev=1351261683&do=diff Omnidex SQL Function: $RJ Omnidex SQL: Overview Statements Functions Examples Quick Ref OdxSQL: Overview Commands Settings Command Line Quick Ref Description The $RJ function will right-justify a string by eliminating the trailing white space and inserting leading spaces as needed. The return data type is C STRING. Syntax text/html 2012-10-26T14:28:03+0000 Anonymous (anonymous@undisclosed.example.com) https://www.omnidex.com/docs/v50/doku.php?id=dev:sql:functions:round&rev=1351261683&do=diff Omnidex SQL Function: CASE Omnidex SQL: Overview Statements Functions Examples Quick Ref OdxSQL: Overview Commands Settings Command Line Quick Ref Description The $ROUND function returns a numeric expression rounded to a specified number of decimal places. If the value specified by n is positive and longer than the digits after the decimal point, 0 is added after the fraction digits. text/html 2012-10-26T14:28:03+0000 Anonymous (anonymous@undisclosed.example.com) https://www.omnidex.com/docs/v50/doku.php?id=dev:sql:functions:rpad&rev=1351261683&do=diff Omnidex SQL Function: $RPAD Omnidex SQL: Overview Statements Functions Examples Quick Ref OdxSQL: Overview Commands Settings Command Line Quick Ref Description The $RPAD function adds pad characters to the left of a character string up to the specified total length. The return data type is C STRING of length specified in the return_length parameter + 1. text/html 2012-10-26T14:28:03+0000 Anonymous (anonymous@undisclosed.example.com) https://www.omnidex.com/docs/v50/doku.php?id=dev:sql:functions:score&rev=1351261683&do=diff Omnidex SQL Function: $SCORE Omnidex SQL: Overview Statements Functions Examples Quick Ref OdxSQL: Overview Commands Settings Command Line Quick Ref Description The $SCORE function returns the “score” used to determine rank and relevancy of qualified text. Most textual applications are designed to qualify a large selection of rows and then present them in order of relevancy. A scoring algorithm ranks qualified text according to occurrence frequency, proximity, and s… text/html 2012-10-26T14:28:03+0000 Anonymous (anonymous@undisclosed.example.com) https://www.omnidex.com/docs/v50/doku.php?id=dev:sql:functions:session_user&rev=1351261683&do=diff Omnidex SQL Function: SESSION_USER Omnidex SQL: Overview Statements Functions Examples Quick Ref OdxSQL: Overview Commands Settings Command Line Quick Ref Description The USER, CURRENT_USER, SESSION_USER functions return the current user of Omnidex as entered when first connected to the current open environment. If no user was entered, the user of text/html 2012-10-26T14:28:04+0000 Anonymous (anonymous@undisclosed.example.com) https://www.omnidex.com/docs/v50/doku.php?id=dev:sql:functions:soundex&rev=1351261684&do=diff Omnidex SQL Function: $SOUNDEX Omnidex SQL: Overview Statements Functions Examples Quick Ref OdxSQL: Overview Commands Settings Command Line Quick Ref Description Returns the Soundex equivalent to a character string (“string” may be a CHARACTER or C STRING column, or a string literal). The algorithm used by this function is identical to that applied to keywords in MDK index using the ;SX option. The routine only pays attention to the first 24 characters of the input… text/html 2012-10-26T14:28:04+0000 Anonymous (anonymous@undisclosed.example.com) https://www.omnidex.com/docs/v50/doku.php?id=dev:sql:functions:substring&rev=1351261684&do=diff Omnidex SQL Function: SUBSTRING Omnidex SQL: Overview Statements Functions Examples Quick Ref OdxSQL: Overview Commands Settings Command Line Quick Ref Description The SUBSTRING function returns a new character string that is a portion of another column or string. The new string begins at the specified starting point in the original column or string and extends the number of characters specified in the length parameter. If the length parameter is omitted, the new … text/html 2012-10-26T14:28:04+0000 Anonymous (anonymous@undisclosed.example.com) https://www.omnidex.com/docs/v50/doku.php?id=dev:sql:functions:system_user&rev=1351261684&do=diff Omnidex SQL Function: SYSTEM_USER Omnidex SQL: Overview Statements Functions Examples Quick Ref OdxSQL: Overview Commands Settings Command Line Quick Ref Description The SYSTEM_USER function returns a string containing the operating system login. The return data type is C STRING maximum length 255. Syntax SYSTEM_USER Example text/html 2012-10-26T14:28:04+0000 Anonymous (anonymous@undisclosed.example.com) https://www.omnidex.com/docs/v50/doku.php?id=dev:sql:functions:trim&rev=1351261684&do=diff Omnidex SQL Function: TRIM Omnidex SQL: Overview Statements Functions Examples Quick Ref OdxSQL: Overview Commands Settings Command Line Quick Ref Description The TRIM function removes any leading and/or trailing pad characters from a string. The pad character is a single character and defaults to a space character. Syntax text/html 2012-10-26T14:28:04+0000 Anonymous (anonymous@undisclosed.example.com) https://www.omnidex.com/docs/v50/doku.php?id=dev:sql:functions:trunc&rev=1351261684&do=diff Omnidex SQL Function: $TRUNC Omnidex SQL: Overview Statements Functions Examples Quick Ref OdxSQL: Overview Commands Settings Command Line Quick Ref Description The $TRUNC function returns a numeric expression truncated to a specified number of digits to the right of the decimal point. If the value specified by n is positive and longer than the digits after the decimal point, 0 is added after the fraction digits. text/html 2012-10-26T14:28:04+0000 Anonymous (anonymous@undisclosed.example.com) https://www.omnidex.com/docs/v50/doku.php?id=dev:sql:functions:upper&rev=1351261684&do=diff Omnidex SQL Function: UPPER Omnidex SQL: Overview Statements Functions Examples Quick Ref OdxSQL: Overview Commands Settings Command Line Quick Ref Description Converts a string to all upper case characters. Syntax UPPER(string) string Required. The character string that is to be upshifted. Example Example 1 select LOWER (contact) as low, UPPER(contact) as up from customers where state = 'co' text/html 2012-10-26T14:28:04+0000 Anonymous (anonymous@undisclosed.example.com) https://www.omnidex.com/docs/v50/doku.php?id=dev:sql:functions:user&rev=1351261684&do=diff Omnidex SQL Function: USER Omnidex SQL: Overview Statements Functions Examples Quick Ref OdxSQL: Overview Commands Settings Command Line Quick Ref Description The USER, CURRENT_USER, SESSION_USER functions return the current user of Omnidex as entered when first connected to the current open environment. If no user was entered, the user of