SQL Reference Guide

SQL Language Elements FairCom DB SQL Identifiers Conventional Identifiers Delimited Identifiers Users, Schemas and Roles Creating and Modifying Users and Groups Database Names and Filesystem Names Search Conditions Logical Operators: OR, AND, NOT Relational Operators Basic Predicate Quantified Predicate BETWEEN Predicate NULL Predicate LIKE Predicate CONTAINS Predicate EXISTS Predicate IN Predicate Outer Join Predicate Full Text Predicate Data Types Character Data Types Exact Numeric Data Types Exact Numeric Data Types Examples Approximate Numeric Data Types Date-Time Data Types Bit String Data Types Expressions Concatenated Character Expressions Numeric Arithmetic Expressions Date Arithmetic Expressions Conditional Expressions Scalar Sub-query Expressions Escape field name matching reserved words in c-tree expressions Query Expressions Cross Database Query Common Table Expressions (CTE) and Recursive Queries Table Valued Functions Inner Joins Outer Joins Date-Time Format Strings Date Format Strings Time Format Strings Functions Aggregate AVG AVG windowing function COUNT COUNT windowing function COUNT_BIG MAX MIN STDEV windowing function STDEVP windowing function SUM SUM windowing function VAR windowing function VARP windowing function Analytic CUME_DIST windowing function FIRST_VALUE windowing function LAG windowing function LEAD windowing function LAST_VALUE windowing function PERCENTILE_CONT windowing function PERCENTILE_DISC windowing function PERCENT_RANK windowing function Conversion CAST function (SQL-92 compatible) CONVERT function (extension) CONVERT function (ODBC compatible) TO_CHAR function (extension) TO_DATE function (extension) TO_NUMBER function (extension) TO_TIME function (extension) TO_TIMESTAMP function (extension) TRUNCATE function Date and Time ADD_MONTHS function (extension) CURDATE function (ODBC compatible) CURRENT_DATE SQL function CURTIME function (ODBC compatible) CURRENT_TIMESTAMP function DATEADD function DATEDIFF function DAYNAME function (ODBC compatible) DAYOFMONTH function (ODBC compatible) DAYOFWEEK function (ODBC compatible) DAYOFYEAR function (ODBC compatible) EXTRACT SQL Function GETDATE function (ODBC compatible) HOUR function (ODBC compatible) LAST_DAY function (extension) LOCALTIME SQL Function LOCALTIMESTAMP SQL Function MINUTE function (ODBC compatible) MONTHNAME function (ODBC compatible) MONTH function (ODBC compatible) MONTHS_BETWEEN function (extension) NEXT_DAY function (extension) NOW function (ODBC compatible) QUARTER function (ODBC compatible) SECOND function (ODBC compatible) SYSDATE function (extension) SYSTIME function (extension) SYSTIMESTAMP function (extension) TIMESTAMPADD function (ODBC compatible) TIMESTAMPDIFF function (ODBC compatible) WEEK function (ODBC compatible) YEAR function (ODBC compatible) Logical CASE (SQL-92 Compatible) COALESCE (SQL-92 compatible) DECODE function (extension) GREATEST function (extension) IFNULL function (ODBC compatible) ISNULL function LEAST function (extension) NVL function (extension) NULLIF (SQL-92 compatible) Math ABS function (ODBC compatible) ACOS function (ODBC compatible) ASIN function (ODBC compatible) ATAN function (ODBC compatible) ATAN2 function (ODBC compatible) COS function (ODBC compatible) COT SQL Scalar Function CEILING function (ODBC compatible) DEGREES function (ODBC compatible) EXP function (ODBC compatible) FLOOR function (ODBC compatible) ISNUMERIC function LOG SQL Scalar Function LOG10 function (ODBC compatible) MOD function (ODBC compatible) PI function (ODBC compatible) POWER function (ODBC compatible) RADIANS function (ODBC compatible) RAND function (ODBC compatible) ROUND function ROWID (extension) SIGN function (ODBC compatible) SIN function (ODBC compatible) SQRT function (ODBC compatible) TAN function (ODBC compatible) Ranking DENSE_RANK windowing function MAX windowing function MIN windowing function NTH_VALUE windowing function NTILE windowing function RANK windowing function ROW_NUMBER windowing function System and Metadata CHARTOROWID (extension) CURRENT_USER SQL Function DATABASE (ODBC compatible) DATALENGTH function DB_NAME (extension) LAST_IDENT function (extension) LAST_ROWID function OBJECT_ID function (extension) SESSION_USER SQL Function SUSER_NAME function (extension) USER function (ODBC compatible) USER_NAME function (extension) String ASCII function (ODBC compatible) CHAR function (ODBC compatible) CHR function (extension) CONCAT function (ODBC compatible) DIFFERENCE function (ODBC compatible) INITCAP function (extension) INSTR function (extension) INSERT function (ODBC compatible) LCASE function (ODBC compatible) LEFT function (ODBC compatible) LEN function (ODBC compatible) LENGTH function (ODBC compatible) LOWER function (SQL-92 compatible) LOCATE function (ODBC compatible) LPAD function (extension) LTRIM function (ODBC compatible) MONTH function (ODBC compatible) OCTET_LENGTH SQL Function OVERLAY SQL Function PREFIX function (extension) POSITION SQL Function REPEAT function (ODBC compatible) REPLACE function (ODBC compatible) RIGHT function (ODBC compatible) RPAD function (extension) ROWIDTOCHAR (extension) RTRIM function (ODBC compatible) SPACE function (ODBC compatible) SOUNDEX function (ODBC compatible) SUBSTR function (extension) SUBSTRING SQL Function (SQL-99 compatible) SUBSTRING function (ODBC compatible) SUFFIX function (extension) TRANSLATE function (extension) TRIM SQL Function UCASE function (ODBC compatible) UPPER function (SQL-92 compatible) SQL Statements Syntax Conventions ALTER INDEX ALTER SEQUENCE ALTER TABLE ALTER VIEW CALL COMMIT WORK CONNECT CREATE FUNCTION CREATE INDEX SQL full-text search examples using PORTER English stemming tokenizer SQL full-text search examples using SIMPLE tokenizer CREATE SEQUENCE Sequence Values CREATE PROCEDURE CREATE SYNONYM CREATE TABLE Column Constraints Table Constraints Storage Attributes SQL - Create [if not exists] and Drop [if exists] Assign Values to Auto-Increment Fields in INSERT CREATE TRIGGER CREATE VIEW DELETE DISCONNECT DROP FUNCTION DROP INDEX DROP PROCEDURE DROP SEQUENCE DROP SYNONYM DROP TABLE DROP TRIGGER DROP VIEW EXPLAIN PLAN GET DIAGNOSTICS GRANT INSERT LOCK TABLE ORDER BY RENAME REVOKE ROLLBACK WORK SELECT PIVOT UNPIVOT SET CONNECTION SET IDENTITY_INSERT SET TRANSACTION ISOLATION SET SCHEMA TRUNCATE TABLE UPDATE UPDATE STATISTICS FairCom DB SQL Built-in Stored Procedures fc_add_db( ) fc_check_file_tran_state( ) fc_create_db( ) fc_createdb( ) fc_create_user( ) fc_get_cachestats() fc_get_connstats() fc_get_dblist( ) fc_get_fcproclist( ) fc_get_filestats() fc_get_hosttablename() fc_get_iostats() fc_get_isamstats() fc_get_lockstats() fc_get_memstats() fc_get_min_card( ) fc_get_priority( ) fc_get_partbounds() fc_get_replstats() fc_get_selectivity( ) fc_get_server_version( ) fc_get_sqlstats() fc_get_sysconfig() fc_get_taskid( ) fc_get_transtats() fc_get_userlist( ) fc_ptadmin_num() fc_purge_db() fc_set_blockinglock( ) fc_set_debug( ) fc_set_file_tran_state( ) fc_set_impersonation( ) fc_set_min_card( ) fc_set_nodename( ) fc_set_priority( ) fc_set_rowid_visible() fc_set_selectivity( ) fc_set_sysconfig() FairCom DB SQL Reserved Words SQL Error Messages System Catalog Tables System Catalog Tables Definitions Glossary FairCom Typographical Conventions Copyright Notice Index