Caché SQL Reference

	Contents
Reference A:	Symbols
	Symbols Used in Caché SQL — A table of characters used in Caché SQL as operators, etc.
Reference B:	SQL Commands and Functions
	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. ALL — Compares with all values in a query. %ALPHAUP — Deprecated. A case-transformation function that converts alphabetic characters to the ALPHAUP collation format. ALTER TABLE — Modifies a table. ALTER USER — Alters a user's password. ALTER VIEW — Modifies a view. ANY — Compares with values in a query. 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. AVG — An aggregate function that returns the average of the values of the specified column. CALL — Invokes a stored procedure. CASE — Returns one of a specified set of values depending on some condition. 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 scalar string function that returns the character that has the ASCII code value specified in a string expression. CHARACTER_LENGTH — A string function that returns the number of characters in a string expression. CHARINDEX — A string function that finds a substring by value and returns its start position. CHAR_LENGTH — A string function that returns the number of characters in a string expression. %CHECKPRIV — Checks whether the user holds a specified privilege. CLOSE — Closes a cursor. COALESCE — A function that returns the value of the first expression that is not NULL. COMMIT — Marks the end of a transaction. CONCAT — A scalar string function that returns a character string as a result of concatenating two character expressions. %CONTAINS — Compares a text to one or more word-aware search words or phrases. %CONTAINSTERM — Compares a text to one or more word-aware search terms. 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. COUNT — An aggregate function that returns the number of rows in a table or a specified column. 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. CREATE TRIGGER — Creates a trigger. CREATE USER — Creates a user account. CREATE VIEW — Creates a view. CURDATE — A scalar date/time function that returns the current date. CURRENT_DATE — A date/time function that returns the current date. CURRENT_TIME — A date/time function that returns the current local time. CURRENT_TIMESTAMP — A date/time function that returns the current 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 string function that returns the number of characters in a string expression. DATEADD — A date/time function that returns a date calculated by adding a certain number of date parts (such as hours or days) to a specified timestamp. DATEDIFF — A date/time function that returns the integer number of intervals of a specified type between two dates. DATENAME — A date/time function that returns a CHARACTER STRING containing the name of the value of a 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/time function that returns an integer from 1 to 31 that corresponds to the day of the month in a given date expression. DAYNAME — A scalar date/time function that returns a character string containing the name of the day in a given date expression. DAYOFMONTH — A scalar date/time function that returns an integer from 1 to 31 that corresponds to the day of the month in a given date expression. DAYOFWEEK — A scalar date/time function that returns an integer from 1 to 7 that corresponds to the day of the week in a given date expression. DAYOFYEAR — A scalar date/time function that returns an integer that corresponds to the day of the year in a given date expression. DECLARE — Declares a cursor. DECODE — A function that evaluates a given expression and returns a specified value. DELETE — Removes rows from a table. 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 its data. DROP TRIGGER — Deletes a trigger. DROP USER — Removes a user account. DROP VIEW — Deletes a view. %EXACT — A case-transformation function that converts characters to the EXACT collation format. EXISTS — Checks for the existence of an element. EXP — A scalar numeric function that returns the log exponential (base e ) value of a given numeric expression. %EXTERNAL — A format-transformation function that returns an expression in DISPLAY format. $EXTRACT — A string function that extracts characters from a string by position. FETCH — Repositions a cursor, and retrieves data from it. $FIND — A string function that finds a substring by value and returns its end position. FLOOR — A numeric function that returns the largest integer less than or equal to a given numeric expression. FROM — A SELECT clause that specifies one or more tables to query. GETDATE — A date/time function that returns the current date and time. GRANT — Gives a user or role privileges. 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. HOUR — A scalar date/time function that returns an integer from 0 to 23 that corresponds to the hour component in a given date-time expression. IFNULL — A function that tests for NULL and returns the appropriate expression. INSERT — Adds a new row (or rows) to a table. %INTERNAL — A format-transformation function that returns an expression in LOGICAL format. INTO — A SELECT clause that specifies the storing of selected values in host variables. ISNULL — A function that tests for NULL and returns the appropriate expression. ISNUMERIC — A numeric function that tests for a valid number. JOIN — Creates a table based on the data in two tables. LAST_IDENTITY — A scalar function that returns the identity of the last row inserted, updated, or deleted. LCASE — A scalar string function that converts all uppercase letters in a string to lowercase letters. 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 — An aggregate function that creates a comma-separated list of values. $LIST — A list function that returns elements in a list. $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. $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. 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 string function that converts all uppercase letters in a string expression to lowercase letters. LTRIM — A string function that returns a string with the leading blanks removed. 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. MINUTE — A scalar date/time function that returns an integer from 0 to 59 that corresponds to the minute component in a given date-time expression. MOD — A scalar numeric function that returns the modulus (remainder) of a number divided by another. MONTH — A date/time function that returns an integer from 1 to 12 that corresponds to the month in a given date expression. MONTHNAME — A scalar date/time function that returns a character string containing the name of the month in a given date expression. %MVR — A case-transformation function that converts characters to the MultiValue collation format. - (Negative) — A unary operator that returns an expression as a negative, numeric value. NOW — A scalar date/time function that returns the current 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. %ODBCIN — A format-transformation function that returns an expression in Logical format. %ODBCOUT — A format-transformation function that returns an expression in ODBC format. OPEN — Opens a cursor. ORDER BY — A SELECT clause that specifies the sorting of rows in a result set. %PATTERN — Compares a pattern match string to each data value in a query. PI — A scalar numeric function that returns the constant value of pi. $PIECE — A string function that returns a substring identified by a delimiter. POSITION — A string function that returns the position of a string expression within a string. + (Positive) — A unary operator that returns an expression as a positive, numeric value. POWER — A numeric function that returns the value of a given expression raised to the specified power. QUARTER — A scalar date/time function that returns an integer from 1 through 4 that corresponds to the quarter of the year in a given date expression. 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. REVOKE — Removes privileges from a user or role. RIGHT — A scalar string function that returns a specified number of characters from the end (rightmost position) of a string expression. ROLLBACK — Rolls back a transaction. ROUND — A numeric function that rounds or truncates a number at a specified number of digits. RTRIM — A string function that returns a string with the trailing blanks removed. SAVEPOINT — Marks a point within a transaction. SECOND — A scalar date/time function that returns an integer from 0 to 59 that corresponds to the second component in a given date-time expression. SELECT — Retrieves rows from one or more tables within a database. SET OPTION — Set execution options. SET TRANSACTION — Sets up parameters for transactions. SIGN — A numeric function that returns the sign of a given numeric expression. SIN — A scalar numeric function that returns the sine, in radians, of an angle. SOME — Compares with values in a query. 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 upper-case strings. SQRT — A scalar numeric function that returns the square root of a given numeric expression. %STARTSWITH — Compares the initial characters of a data value in a query. START TRANSACTION — Marks the beginning of a transaction. STR — A function that converts a numeric to a string. STRING — A function that converts and concatenates expressions into a string. %STRING — Deprecated. A case-transformation 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. SUM — An aggregate function that returns the sum of the values of a specified column. SYSDATE — A date/time function that returns the current 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 or a number to a character string. TO_DATE — A date function that converts a formatted date string to a date. TO_NUMBER — A string function that converts a given string expression to a value of NUMBER data type. 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 TABLE — Removes all data from a table. UCASE — A string function that converts all lowercase letters in a string to uppercase letters. UNION — Combines one or more SELECT statements. UPDATE — Sets new values for specified columns in a specified table. UPPER — Deprecated. A case-transformation function that converts alphabetic characters to the UPPER collation format. %UPPER — Deprecated. A case-transformation function that converts alphabetic characters to the UPPER collation format. USER — A function that returns the user name of the current user. VALUES — Specifies data values for use in fields. WEEK — A scalar date/time function that returns an integer from 1 to 53 that corresponds to the week of the year in a given date expression. WHERE — A SELECT clause that specifies one or more restrictive conditions. WHERE CURRENT OF — A clause that specifies the current row using a cursor. XMLAGG — An aggregate function that creates a concatenated string of values. 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/time function that returns the year in a given date expression.
Reference C:	SQL Concepts
	Aggregate functions — Functions that operate on a set of values and their aggregated values. Arithmetic expressions — How Caché SQL parses arithmetic expressions. Calling Functions — How to call user-defined SQL functions from SQL statements. Column — Specifies the name of a column. Comment — Allows comments in SQL code. Condition expression — Specifies a logical condition that evaluates to either true or false. One or more linked condition expressions compose a search-condition. Configuration Settings — How to configure SQL systemwide defaults. Conventions — Specifies conventions used in the SQL Command Reference. Cursor — A pointer to data that allows manipulation of one row at a time. Data Types — Specifies the kind of data that an SQL entity (such as a column) can contain. Date and Time Constructs — Formats a string as date, time, or timestamp. DDL — Specification for the data definition language (DDL). Default specification — Sets a default field value. Default user name and password — Provides default login identity. Error Codes — Lists SQL error codes and messages. Field constraint — Specifies rules about a field's contents. Host variable — Refers to an element in the language hosting SQL. Identifier — Provides a label for a table, column, or other entity. Literal — Represents an actual (literal) value. NULL and the Empty String — The NULL and the empty string data values. Query — Retrieves data using one or more SELECT statements. Reserved words — A list of SQL reserved words. Scalar expression — Specifies a mathematical expression that describes a scalar value. Stored Procedure — Specifies an SQL statement stored and invocable by name. String Manipulation — String manipulation functions and operators. Table Reference — Provides a reference to organized data. Transaction — Specifies a set of operations that forms a unit. Triggered action — When invoked, performs a specified task in a specified manner. User — Specifies a user account on the system. Variables — Variables used in embedded SQL. View — A virtual table based on data retrieved from one or more tables.