InterSystems SQL Reference Symbols and Syntax Conventions Symbols Used in InterSystems SQL Syntax Conventions Used in this Manual SQL Commands ALTER FOREIGN SERVER ALTER FOREIGN TABLE ALTER ML CONFIGURATION ALTER MODEL ALTER TABLE ALTER USER ALTER VIEW BUILD INDEX CALL CANCEL QUERY CASE %CHECKPRIV CLOSE COMMIT CREATE AGGREGATE CREATE DATABASE CREATE FOREIGN SERVER CREATE FOREIGN TABLE CREATE FUNCTION CREATE INDEX CREATE METHOD CREATE ML CONFIGURATION CREATE MODEL CREATE PROCEDURE CREATE QUERY CREATE ROLE CREATE SCHEMA CREATE TABLE CREATE TRIGGER CREATE USER CREATE VIEW DECLARE DELETE DROP AGGREGATE DROP DATABASE DROP FOREIGN SERVER DROP FOREIGN TABLE DROP FUNCTION DROP INDEX DROP METHOD DROP ML CONFIGURATION DROP MODEL DROP PROCEDURE DROP QUERY DROP ROLE DROP SCHEMA DROP TABLE DROP TRIGGER DROP USER DROP VIEW EXPLAIN FETCH FREEZE PLANS GRANT INSERT INSERT OR UPDATE %INTRANSACTION JOIN LOAD DATA LOCK OPEN PURGE CACHED QUERIES REVOKE ROLLBACK SAVEPOINT SELECT SET ML CONFIGURATION SET OPTION SET TRANSACTION START TRANSACTION TRAIN MODEL TRUNCATE TABLE TUNE TABLE UNFREEZE PLANS UNLOCK UPDATE USE DATABASE VALIDATE MODEL SQL Clauses DISTINCT FROM GROUP BY HAVING INTO ORDER BY TOP UNION VALUES WHERE WHERE CURRENT OF SQL Predicate Conditions Overview of Predicates ALL ANY BETWEEN EXISTS %FIND FOR SOME FOR SOME %ELEMENT IN %INLIST %INSET IS JSON IS NULL LIKE %MATCHES %PATTERN SOME %STARTSWITH SQL Aggregate Functions Overview of Aggregate Functions AVG COUNT %DLIST JSON_ARRAYAGG LIST MAX MIN STDDEV, STDDEV_SAMP, STDDEV_POP SUM VARIANCE, VAR_SAMP, VAR_POP XMLAGG SQL Window Functions Overview of Window Functions AVG COUNT CUME_DIST() DENSE_RANK() FIRST_VALUE LAG LAST_VALUE LEAD MAX MIN NTH_VALUE NTILE PERCENT_RANK() RANK() ROW_NUMBER() SUM SQL Functions ABS ACOS ASCII ASIN ATAN ATAN2 CAST CEILING CHAR CHARACTER_LENGTH CHARINDEX CHAR_LENGTH COALESCE CONCAT CONVERT COS COT CURDATE CURRENT_DATE CURRENT_TIME CURRENT_TIMESTAMP CURTIME DATABASE DATALENGTH DATE DATEADD DATEDIFF DATENAME DATEPART DATE_TRUNC DAY DAYNAME DAYOFMONTH DAYOFWEEK DAYOFYEAR DECODE DEGREES %EXACT EXP %EXTERNAL $EXTRACT $FIND FLOOR GETDATE GETUTCDATE GREATEST HOUR IFNULL INSTR %INTERNAL ISNULL ISNUMERIC JSON_ARRAY JSON_OBJECT $JUSTIFY LAST_DAY LAST_IDENTITY LCASE LEAST LEFT LEN LENGTH $LENGTH $LIST $LISTBUILD $LISTDATA $LISTFIND $LISTFROMSTRING $LISTGET $LISTLENGTH $LISTSAME $LISTTOSTRING LOG LOG10 LOWER LPAD LTRIM %MINUS MINUTE MOD MONTH MONTHNAME NOW NULLIF NVL %OBJECT %ODBCIN %ODBCOUT %OID PI $PIECE %PLUS POSITION POWER PREDICT PROBABILITY QUARTER RADIANS REPEAT REPLACE REPLICATE REVERSE RIGHT ROUND RPAD RTRIM SEARCH_INDEX SECOND SIGN SIN SPACE %SQLSTRING %SQLUPPER SQRT SQUARE STR STRING STUFF SUBSTR SUBSTRING SYSDATE %SYSTEM_SQL.DefaultSchema() TAN TIMESTAMPADD TIMESTAMPDIFF TO_CHAR TO_DATE TO_NUMBER TO_POSIXTIME TO_TIMESTAMP $TRANSLATE TRIM TRUNCATE %TRUNCATE $TSQL_NEWID UCASE UNIX_TIMESTAMP UPPER USER WEEK XMLCONCAT XMLELEMENT XMLFOREST YEAR SQL Unary Operators - (Negative) + (Positive) SQL Reference Material Data Types Date and Time Constructs Default user name and password SQLCODE Error Codes Field constraint Reserved words Special Variables String Manipulation
