Caché SQL Reference
Contents
[Home]  [Next]
InterSystems: The power behind what matters   
Class Reference   
Search:    

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.
IS JSON — Determines if a data value is in JSON format.
IS NULL — Determines if a data value is NULL.
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.
%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 timestamp calculated by adding or subtracting a number of date part units (such as hours or days) to a date or 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 date/time function that returns the current local date and time.
GETUTCDATE — A date/time 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 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 date part to a timestamp.
TIMESTAMPDIFF — A scalar date/time function that returns an integer count of the difference between two timestamps for a specified date part.
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 string expression to a value of NUMERIC 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.
UNIX_TIMESTAMP — A date/time function that converts a date expression to a UNIX timestamp.
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 formats an XML markup tag to enclose one or more expression values.
XMLFOREST — A function that formats multiple XML markup tags to enclose expression values.
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.