Caché SQL Reference
Contents
 
   
Server:docs2
Instance:LATEST
User:UnknownUser
 
-
Go to:
Search:    

 
Contents

Preface : 
 
 
Reference A: 
 
  • Symbols Used in Caché SQL — A table of characters used in Caché SQL as operators, etc.
  • Syntax Conventions Used in this Manual —  Specifies conventions used in the SQL Command Reference.
  • Reference B: 
     
  • ALTER TABLE — Modifies a table.
  • ALTER USER — Changes a user’s password.
  • ALTER VIEW — Modifies a view.
  • CALL — Invokes a stored procedure.
  • CASE — Chooses one of a specified set of values depending on some condition.
  • %CHECKPRIV — Checks whether the user holds a specified privilege.
  • CLOSE — Closes a cursor.
  • COMMIT — Commits work performed during a transaction.
  • CREATE DATABASE — Creates a database (namespace).
  • CREATE FUNCTION — Creates a function as a method in a class.
  • CREATE INDEX — Creates an index for a table.
  • CREATE METHOD — Creates a method in a class.
  • CREATE PROCEDURE — Creates a method or query which is exposed as an SQL stored procedure.
  • CREATE QUERY — Creates a query.
  • CREATE ROLE — Creates a role.
  • CREATE TABLE — Creates a table definition.
  • CREATE TRIGGER — Creates a trigger.
  • CREATE USER — Creates a user account.
  • CREATE VIEW — Creates a view.
  • DECLARE — Declares a cursor.
  • DELETE — Removes rows from a table.
  • DISTINCT — A SELECT clause that specifies to return only distinct values.
  • DROP DATABASE — Deletes a database (namespace).
  • DROP FUNCTION —  Deletes a function.
  • DROP INDEX — Removes an index.
  • DROP METHOD — Deletes a method.
  • DROP PROCEDURE — Deletes a procedure.
  • DROP QUERY — Deletes a query.
  • DROP ROLE — Deletes a role.
  • DROP TABLE — Deletes a table and (optionally) its data.
  • DROP TRIGGER — Deletes a trigger.
  • DROP USER — Removes a user account.
  • DROP VIEW — Deletes a view.
  • FETCH — Repositions a cursor, and retrieves data from it.
  • FROM — A SELECT clause that specifies one or more tables to query.
  • GRANT — Grants privileges to a user or role.
  • GROUP BY — A SELECT clause that groups the resulting rows of a query according to one or more columns.
  • HAVING — A SELECT clause that specifies one or more restrictive conditions.
  • INSERT — Adds a new row (or rows) to a table.
  • INSERT OR UPDATE — Adds a new row or updates an existing row in a table.
  • INTO — A SELECT clause that specifies the storing of selected values in host variables.
  • %INTRANSACTION — Shows transaction state.
  • JOIN — A SELECT subclause that creates a table based on the data in two tables.
  • LOCK — Locks a table.
  • OPEN — Opens a cursor.
  • ORDER BY — A SELECT clause that specifies the sorting of rows in a result set.
  • REVOKE — Removes privileges from a user or role.
  • ROLLBACK — Rolls back a transaction.
  • SAVEPOINT — Marks a point within a transaction.
  • SELECT — Retrieves rows from one or more tables within a database.
  • SET OPTION — Sets an execution option.
  • SET TRANSACTION — Sets parameters for transactions.
  • START TRANSACTION — Begins a transaction.
  • TOP — A SELECT clause that specifies how many rows to return.
  • TRUNCATE TABLE — Removes all data from a table and resets counters.
  • UNION — Combines two or more SELECT statements.
  • UNLOCK — Unlocks a table.
  • UPDATE — Sets new values for specified columns in a specified table.
  • USE DATABASE — Sets the current namespace and database.
  • VALUES — An INSERT/UPDATE clause that specifies data values for use in fields.
  • WHERE — A SELECT clause that specifies one or more restrictive conditions.
  • WHERE CURRENT OF — An UPDATE/DELETE clause that specifies the current row using a cursor.
  • Reference C: 
     
  • Overview of Predicates — Describes logical conditions that evaluate to either true or false.
  • ALL — Matches a value with all corresponding values from a subquery.
  • ANY — Matches a value with at least one matching value from a subquery.
  • BETWEEN — Matches a value to a range of values.
  • %CONTAINS — Matches a value to one or more phrases using word-aware matching.
  • %CONTAINSTERM — Matches a value to one or more words using word-aware matching.
  • EXISTS — Checks a table for the existence of at least one corresponding row.
  • %FIND — Matches a value to a set of generated values with bitmap chunks iteration.
  • FOR SOME — Determines whether to return a record based on a condition test of field values.
  • FOR SOME %ELEMENT — Matches list element values or the number of list elements with a predicate.
  • IN — Matches a value to items in an unstructured comma-separated list.
  • %INLIST — Matches a value to the elements in a %List structured list.
  • %INSET — Matches a value to a set of generated values.
  • LIKE — Matches a value with a pattern string containing literals and wildcards.
  • %MATCHES — Matches a value with a pattern string containing literals, wildcards, and ranges.
  • NULL — Determines if a data value is NULL.
  • %PATTERN — Matches a value with a pattern string containing literals, wildcards, and character type codes.
  • SOME — Matches a value with at least one matching value from a subquery.
  • %STARTSWITH — Matches a value with a substring specifying initial characters.
  • Reference D: 
     
  • Overview of Aggregate Functions —  Functions that evaluate all of the values of a column and return a single aggregate value.
  • AVG — An aggregate function that returns the average of the values of the specified column.
  • COUNT — An aggregate function that returns the number of rows in a table or a specified column.
  • %DLIST — An aggregate function that creates a Caché list of values.
  • JSON_ARRAYAGG — An aggregate function that creates a JSON format array of values.
  • LIST — An aggregate function that creates a comma-separated list of values.
  • MAX — An aggregate function that returns the maximum data value in a specified column.
  • MIN — An aggregate function that returns the minimum data value in a specified column.
  • STDDEV, STDDEV_SAMP, STDDEV_POP — Aggregate functions that return the statistical standard deviation of a data set.
  • SUM — An aggregate function that returns the sum of the values of a specified column.
  • VARIANCE, VAR_SAMP, VAR_POP — Aggregate functions that return the statistical variance of a data set.
  • XMLAGG — An aggregate function that creates a concatenated string of values.
  • Reference E: 
     
  • ABS — A numeric function that returns the absolute value of a numeric expression.
  • ACOS — A scalar numeric function that returns the arc-cosine, in radians, of a given cosine.
  • %ALPHAUP — Deprecated. A collation function that converts alphabetic characters to the ALPHAUP collation format.
  • ASCII — A string function that returns the integer ASCII code value of the first (leftmost) character of a string expression.
  • ASIN — A scalar numeric function that returns the arc-sine, in radians, of the sine of an angle.
  • ATAN — A scalar numeric function that returns the arc-tangent, in radians, of the tangent of an angle.
  • CAST — A function that converts a given expression to a specified data type.
  • CEILING — A numeric function that returns the smallest integer greater than or equal to a given numeric expression.
  • CHAR — A string function that returns the character that has the ASCII code value specified in a string expression.
  • CHARACTER_LENGTH — A function that returns the number of characters in an expression.
  • CHARINDEX — A string function that returns the position of a substring within a string, with optional search start point.
  • CHAR_LENGTH — A function that returns the number of characters in an expression.
  • COALESCE — A function that returns the value of the first expression that is not NULL.
  • CONCAT — A scalar string function that returns a character string as a result of concatenating two character expressions.
  • CONVERT — A function that converts a given expression to a specified data type.
  • COS — A scalar numeric function that returns the cosine, in radians, of an angle.
  • COT — A scalar numeric function that returns the cotangent, in radians, of an angle.
  • CURDATE — A scalar date/time function that returns the current local date.
  • CURRENT_DATE — A date/time function that returns the current local date.
  • CURRENT_TIME — A date/time function that returns the current local time.
  • CURRENT_TIMESTAMP — A date/time function that returns the current local date and time.
  • CURTIME — A scalar date/time function that returns the current local time.
  • DATABASE — A scalar string function that returns the database name qualifier.
  • DATALENGTH — A function that returns the number of characters in an expression.
  • DATE — A function that takes a timestamp and returns a date.
  • DATEADD — A date/time function that returns a date calculated by adding an integer number of date part units (such as hours or days) to a specified timestamp.
  • DATEDIFF — A date/time function that returns an integer difference for a specified datepart between two dates.
  • DATENAME — A date/time function that returns a string representing the value of the specified part of a date/time expression.
  • DATEPART — A date/time function that returns an integer representing the value of the specified part of a date/time expression.
  • DAY — A date function that returns the day of the month for a date expression.
  • DAYNAME — A date function that returns the name of the day of the week for a date expression.
  • DAYOFMONTH — A date function that returns the day of the month for a date expression.
  • DAYOFWEEK — A date function that returns the day of the week as an integer for a date expression.
  • DAYOFYEAR — A date function that returns the day of the year as an integer for a date expression.
  • DECODE — A function that evaluates a given expression and returns a specified value.
  • DEGREES — A numeric function that converts radians to degrees.
  • %EXACT — A collation function that converts characters to the EXACT collation format.
  • EXP — A scalar numeric function that returns the exponential (inverse of natural logarithm) of a number.
  • %EXTERNAL — A format-transformation function that returns an expression in DISPLAY format.
  • $EXTRACT — A string function that extracts characters from a string by position.
  • $FIND — A string function that returns the end position of a substring within a string, with optional search start point.
  • FLOOR — A numeric function that returns the largest integer less than or equal to a given numeric expression.
  • GETDATE — A timestamp function that returns the current local date and time.
  • GETUTCDATE — A timestamp function that returns the current UTC date and time.
  • GREATEST — A function that returns the greatest value from a list of values.
  • HOUR — A time function that returns the hour for a datetime expression.
  • IFNULL — A function that tests for NULL and returns the appropriate expression.
  • INSTR — A string function that returns the position of a substring within a string, with optional search start point and occurrence count.
  • %INTERNAL — A format-transformation function that returns an expression in LOGICAL format.
  • ISNULL — A function that tests for NULL and returns the appropriate expression.
  • ISNUMERIC — A numeric function that tests for a valid number.
  • JSON_ARRAY — A conversion function that returns data as a JSON array.
  • JSON_OBJECT — A conversion function that returns data as a JSON object.
  • $JUSTIFY — A function that right-aligns a value within a specified width, optionally rounding to a specified number of fractional digits.
  • LAST_DAY — A date function that returns the date of the last day of the month for a date expression.
  • LAST_IDENTITY — A scalar function that returns the identity of the last row inserted, updated, deleted, or fetched.
  • LCASE — A case-transformation function that converts all uppercase letters in a string to lowercase letters.
  • LEAST — A function that returns the least value from a list of values.
  • LEFT — A scalar string function that returns a specified number of characters from the beginning (leftmost position) of a string expression.
  • LEN — A string function that returns the number of characters in a string expression.
  • LENGTH — A string function that returns the number of characters in a string expression.
  • $LENGTH — A string function that returns the number of characters or the number of delimited substrings in a string.
  • $LIST — A list function that returns elements in a list.
  • $LISTBUILD — A list function that builds a list from strings.
  • $LISTDATA — A list function that indicates whether the specified element exists and has a data value.
  • $LISTFIND — A list function that searches a specified list for the requested value.
  • $LISTFROMSTRING — A list function that creates a list from a string.
  • $LISTGET — A list function that returns an element in a list or a specified default value.
  • $LISTLENGTH — A list function that returns the number of elements in a specified list.
  • $LISTSAME — A list function that compares two lists and returns a boolean value.
  • $LISTTOSTRING — A list function that creates a string from a list.
  • LOG — A scalar numeric function that returns the natural logarithm of a given numeric expression.
  • LOG10 — A scalar numeric function that returns the base-10 logarithm of a given numeric expression.
  • LOWER — A case-transformation function that converts all uppercase letters in a string expression to lowercase letters.
  • LPAD — A string function that returns a string left-padded to a specified length.
  • LTRIM — A string function that returns a string with the leading blanks removed.
  • %MINUS — A collation function that converts numbers to canonical collation format, then inverts the sign.
  • MINUTE — A time function that returns the minute for a datetime expression.
  • MOD — A scalar numeric function that returns the modulus (remainder) of a number divided by another.
  • MONTH — A date function that returns the month as an integer for a date expression.
  • MONTHNAME — A date function that returns the name of the month for a date expression.
  • NOW — A scalar date/time function that returns the current local date and time.
  • NULLIF — A function that returns NULL if an expression is true.
  • NVL — A function that tests for NULL and returns the appropriate expression.
  • %OBJECT — A scalar function that opens a stream object and returns the corresponding oref.
  • %ODBCIN — A format-transformation function that returns an expression in Logical format.
  • %ODBCOUT — A format-transformation function that returns an expression in ODBC format.
  • %OID — A scalar function that returns OID of an ID field.
  • PI — A scalar numeric function that returns the constant value of pi.
  • $PIECE — A string function that returns a substring identified by a delimiter.
  • %PLUS — A collation function that converts numbers to canonical collation format.
  • POSITION — A string function that returns the position of a substring within a string.
  • POWER — A numeric function that returns the value of a given expression raised to the specified power.
  • QUARTER — A date function that returns the quarter of the year as an integer for a date expression.
  • RADIANS — A numeric function that converts degrees to radians.
  • REPEAT — A string function that repeats a string a specified number of times.
  • REPLACE — A string function that replaces a substring within a string.
  • REPLICATE — A string function that repeats a string a specified number of times.
  • REVERSE — A scalar string function that returns a character string in reverse character order.
  • RIGHT — A scalar string function that returns a specified number of characters from the end (rightmost position) of a string expression.
  • ROUND — A numeric function that rounds or truncates a number at a specified number of digits.
  • RPAD — A string function that returns a string right-padded to a specified length.
  • RTRIM — A string function that returns a string with the trailing blanks removed.
  • SEARCH_INDEX — A function that returns a set of values from the index’s Find() method.
  • SECOND — A time function that returns the second for a datetime expression.
  • SIGN — A numeric function that returns the sign of a given numeric expression.
  • %SIMILARITY — Returns a number indicating the similarity of a field value to a text.
  • SIN — A scalar numeric function that returns the sine, in radians, of an angle.
  • SPACE — A string function that returns a string of spaces.
  • %SQLSTRING — A collation function that sorts values as strings.
  • %SQLUPPER — A collation function that sorts values as uppercase strings.
  • SQRT — A numeric function that returns the square root of a given numeric expression.
  • SQUARE — A scalar numeric function that returns the square of a number.
  • STR — A function that converts a numeric to a string.
  • STRING — A function that converts and concatenates expressions into a string.
  • %STRING — Deprecated. A collation function that converts characters to the STRING collation format.
  • STUFF — A string function that replaces a substring within a string.
  • SUBSTR — A string function that returns a substring that is derived from a specified string expression.
  • SUBSTRING — A string function that returns a substring from a larger character string.
  • SYSDATE — A date/time function that returns the current local date and time.
  • TAN — A scalar numeric function that returns the tangent, in radians, of an angle.
  • TIMESTAMPADD — A scalar date/time function that returns a new timestamp calculated by adding a number of intervals of a specified type to a specified timestamp.
  • TIMESTAMPDIFF — A scalar date/time function that returns the integer number of intervals of a specified type between two timestamps.
  • TO_CHAR — A string function that converts a date, timestamp, or number to a formatted character string.
  • TO_DATE — A date function that converts a formatted string to a date.
  • TO_NUMBER — A string function that converts a given string expression to a value of NUMBER data type.
  • TO_TIMESTAMP — A date function that converts a formatted string to a timestamp.
  • $TRANSLATE — A string function that performs character-for-character replacement.
  • TRIM — A string function that returns a character string with leading and/or trailing characters removed.
  • TRUNCATE — A scalar numeric function that truncates a number at a specified number of digits.
  • %TRUNCATE — A collation function that truncates a string to the specified length and applies EXACT collation.
  • $TSQL_NEWID — A function that returns a globally unique ID.
  • UCASE — A case-transformation function that converts all lowercase letters in a string to uppercase letters.
  • UPPER — A case-transformation function that converts all lowercase letters in a string expression to uppercase letters.
  • %UPPER — Deprecated. A collation function that converts alphabetic characters to the UPPER collation format.
  • USER — A function that returns the user name of the current user.
  • WEEK — A date function that returns the week of the year as an integer for a date expression.
  • XMLCONCAT — A function that concatenates XML elements.
  • XMLELEMENT — A function that tags values returned from a specified column or columns.
  • XMLFOREST — A function that tags values returned from multiple columns.
  • YEAR — A date function that returns the year for a date expression.
  • Reference F: 
     
  • $MVFMT — A MultiValue formatting function for a string.
  • $MVFMTS — A MultiValue formatting function for dynamic array elements.
  • $MVICONV — A MultiValue external-to-internal conversion function.
  • $MVICONVS — A MultiValue external-to-internal conversion function for dynamic arrays.
  • $MVOCONV — A MultiValue internal-to-external conversion function.
  • $MVOCONVS — A MultiValue internal-to-external conversion function for dynamic arrays.
  • %MVR — A MultiValue collation sequence function.
  • Reference G: 
     
  • - (Negative) — A unary operator that returns an expression as a negative, numeric value.
  • + (Positive) — A unary operator that returns an expression as a positive, numeric value.
  • Reference H: 
     
  • Data Types — Specifies the kind of data that an SQL entity (such as a column) can contain.
  • Date and Time Constructs — Validates and converts an ODBC date, time, or timestamp.
  • Default user name and password —  Provides default login identity.
  • Field constraint — Specifies rules about a field’s contents.
  • Reserved words — A list of SQL reserved words.
  • Special Variables — System-supplied variables.
  • String Manipulation — String manipulation functions and operators.