Caché SQL Reference

	Contents
Reference A:	SQL Commands
	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 angle. 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 scalar 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 — Changes the type of an expression. CEILING — A scalar 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 general SQL function that returns the number of characters in a string expression. CHAR_LENGTH — A general SQL function that returns the number of characters in a string expression. CLOSE — Closes a cursor. COALESCE — A general SQL 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. CONVERT — A scalar system 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 general SQL date/time function that returns the current date. CURRENT_TIME — A general SQL date/time function that returns the current local time. CURRENT_TIMESTAMP — A general SQL 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 function that returns the database name qualifier. DATEADD — A general 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 general date/time function that returns the interval between two dates. DATENAME — A general date/time function that returns a CHARACTER STRING containing the name of the specified date part of a date/time value. DATEPART — A general date/time function that returns an INTEGER representing the specified date/time part of the specified date/time 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 from 1 to 366 that corresponds to the day of the year in a given date expression. DECLARE — Declares a cursor. DECODE — A general SQL 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 exponential value of a given numeric expression. %EXTERNAL — A format-transformation function that returns an expression in DISPLAY format. FETCH — Repositions a cursor, and retrieves data from it. FLOOR — A scalar numeric function that returns the largest integer less than or equal to a given numeric expression. FROM — Specifies a list of tables within a SELECT statement. GETDATE — A scalar date/time function that returns the current date and time. GRANT — Gives a user or role privileges. 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 — Tests for NULL and returns the appropriate expression. INSERT — Adds a new row to a table. %INTERNAL — A format-transformation function that returns an expression in LOGICAL format. ISNULL — A general SQL function that tests for NULL and returns the appropriate expression. JOIN — Creates a table based on the data in two or more tables. LCASE — A scalar string function that converts all upper-case characters in a string expression to lower case. LEFT — A scalar string function that returns a specified number of characters from the beginning (leftmost position) of a string expression. LENGTH — A scalar string function that returns the number of characters in a string expression. LIST — An aggregate function that creates a comma-delimited list of values. 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 general SQL function that converts all upper-case characters in a string expression to lower case. LTRIM — A scalar string function that returns a string expression with the leading blanks removed. MAX — An aggregate function that returns the maximum value used within a specified column. MIN — An aggregate function that returns the minimum value used within 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 scalar 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. - (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 — Returns NULL if an expression is true. NVL — A general SQL 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 — Specifies the sorting of rows in a result set. PI — A scalar numeric function that returns the constant value of PI. POSITION — A general SQL 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 scalar 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 to 4 that corresponds to the quarter of the year in a given date expression. REPEAT — 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 off a numeric expression. RTRIM — A string function that returns a string expression with the trailing blanks removed. 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 — 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. START TRANSACTION — Marks the beginning of a transaction. %STRING — Deprecated. A case-transformation function that converts characters to the STRING collation format. SUBSTR — A general SQL 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. TAN — A scalar numeric function that returns the tangent, in radians, of an angle. TIMESTAMPADD — Returns the timestamp calculated by adding a certain number of intervals of a certain type to a specified timestamp. TIMESTAMPDIFF — Returns the INTEGER value representing the number of intervals of a certain type by which one timestamp is greater than another. TO_CHAR — A general SQL string function that converts a given date or number value to a string. TO_DATE — A general SQL string function that converts a given string expression to a value of DATE data type. TO_NUMBER — A general SQL string function that converts a given string expression to a value of NUMBER data type. TRIM — A general SQL string function that returns a character string with leading and/or trailing characters removed. TRUNCATE — A scalar numeric function that truncates a numeric expression a specified number of places after the decimal point. UCASE — A scalar string function that converts all lower-case characters in a string expression to upper case. 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 general and scalar 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 — Specifies one or more restrictions within a SELECT statement. WHERE CURRENT OF — A clause that specifies the current row in a cursor. YEAR — A scalar date/time function that returns an integer in the range 1840-9999 that indicates the year in a given date expression.
Reference B:	SQL Concepts
	Aggregate functions — Functions that operate on a set of values and their aggregated values. Arithmetic expressions — How Caché SQL parses arithmetic expressions. 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. Conventions — Specifies conventions used in the SQL Command Reference. Cursor — A pointer to data that allows manipulation of one row at a time. Data type — Specifies the kind of data that an entity (such as a column) contains. 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 a 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. 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. Table — 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. View — A virtual table based on data retrieved from one or more tables.