Reserved words (SQL)
Synopsis
%AFTERHAVING | %ALLINDEX | %ALPHAUP | %ALTER | %BEGTRANS |
%CHECKPRIV | %CLASSNAME | %CLASSPARAMETER | %DBUGFULL | %DELDATA |
%DESCRIPTION | %EXACT | %EXTERNAL | %FILE | %FIRSTTABLE | %FLATTEN |
%FOREACH | %FULL | %ID | %IDADDED | %IGNOREINDEX | %IGNOREINDICES |
%INLIST | %INORDER | %INTERNAL | %INTEXT | %INTRANS | %INTRANSACTION |
%KEY | %MATCHES | %MCODE | %MERGE | %MINUS | %MVR | %NOCHECK |
%NODELDATA | %NOFLATTEN | %NOFPLAN | %NOINDEX | %NOLOCK |
%NOMERGE | %NOPARALLEL | %NOREDUCE | %NORUNTIME | %NOSVSO | %NOTOPOPT |
%NOTRIGGER | %NOUNIONOROPT | %NUMROWS | %ODBCIN | %ODBCOUT |
%PARALLEL | %PLUS | %PROFILE | %PROFILE_ALL | %PUBLICROWID | %ROUTINE |
%ROWCOUNT | %RUNTIMEIN | %RUNTIMEOUT | %STARTSWITH |
%STARTTABLE | %SQLSTRING | %SQLUPPER | %STRING | %TABLENAME |
%TRUNCATE | %UPPER | %VALUE | %VID
ABSOLUTE | ADD | ALL | ALLOCATE | ALTER | AND | ANY | ARE | AS |
ASC | ASSERTION | AT | AUTHORIZATION | AVG | BEGIN | BETWEEN |
BIT | BIT_LENGTH | BOTH | BY | CASCADE | CASE | CAST |
CHAR | CHARACTER | CHARACTER_LENGTH | CHAR_LENGTH |
CHECK | CLOSE | COALESCE | COLLATE | COMMIT | CONNECT |
CONNECTION | CONSTRAINT | CONSTRAINTS | CONTINUE | CONVERT |
CORRESPONDING | COUNT | CREATE | CROSS | CURRENT |
CURRENT_DATE | CURRENT_TIME | CURRENT_TIMESTAMP |
CURRENT_USER | CURSOR | DATE | DEALLOCATE | DEC | DECIMAL |
DECLARE | DEFAULT | DEFERRABLE | DEFERRED | DELETE | DESC |
DESCRIBE | DESCRIPTOR | DIAGNOSTICS | DISCONNECT | DISTINCT |
DOMAIN | DOUBLE | DROP | ELSE | END | ENDEXEC | ESCAPE | EXCEPT |
EXCEPTION | EXEC | EXECUTE | EXISTS | EXTERNAL | EXTRACT |
FALSE | FETCH | FIRST | FLOAT | FOR | FOREIGN | FOUND | FROM | FULL |
GET | GLOBAL | GO | GOTO | GRANT | GROUP | HAVING | HOUR |
IDENTITY | IMMEDIATE | IN | INDICATOR | INITIALLY |
INNER | INPUT | INSENSITIVE | INSERT | INT | INTEGER | INTERSECT |
INTERVAL | INTO | IS | ISOLATION | JOIN | LANGUAGE | LAST |
LEADING | LEFT | LEVEL | LIKE | LOCAL | LOWER | MATCH | MAX | MIN |
MINUTE | MODULE | NAMES | NATIONAL | NATURAL | NCHAR |
NEXT | NO | NOT | NULL | NULLIF | NUMERIC | OCTET_LENGTH | OF | ON |
ONLY | OPEN | OPTION | OR | OUTER | OUTPUT | OVERLAPS |
PAD | PARTIAL | PREPARE | PRESERVE | PRIMARY | PRIOR | PRIVILEGES |
PROCEDURE | PUBLIC | READ | REAL | REFERENCES | RELATIVE |
RESTRICT | REVOKE | RIGHT | ROLE | ROLLBACK | ROWS |
SCHEMA | SCROLL | SECOND | SECTION | SELECT | SESSION_USER |
SET | SHARD | SMALLINT | SOME | SPACE | SQLERROR | SQLSTATE |
STATISTICS | SUBSTRING | SUM | SYSDATE | SYSTEM_USER | TABLE |
TEMPORARY | THEN | TIME | TIMEZONE_HOUR | TIMEZONE_MINUTE |
TO | TOP | TRAILING | TRANSACTION | TRIM | TRUE | UNION | UNIQUE |
UPDATE | UPPER | USER | USING | VALUES | VARCHAR | VARYING | WHEN |
WHENEVER | WHERE | WITH | WORK | WRITE
Description
Within SQL certain words are reserved. You cannot use an SQL reserved word as an SQL identifier (such as the name for a table, a column, an AS alias, or other entity), unless:
-
The word is delimited with double quotes ("word"), and
-
Delimited identifiers are supported. For further details, refer to Identifiers.
This list contains only those words that are reserved in this sense; it does not contain all SQL keywords. Several of the words listed above start with the "%" character, indicating that they are InterSystems SQL proprietary extension keywords. In general, it is not recommended to use words that begin with "%" as identifiers such as table and column names, because new InterSystems SQL extension keywords may be added in the future.
You can check if a word is an SQL reserved word by invoking the IsReservedWord()Opens in a new tab method, as shown in the following example. Specify the reserved word as a quoted string; reserved words are not case-sensitive. $SYSTEM.SQL.IsReservedWord() returns a boolean value.
WRITE !,"Reserved?: ",$SYSTEM.SQL.IsReservedWord("VARCHAR")
WRITE !,"Reserved?: ",$SYSTEM.SQL.IsReservedWord("varchar")
WRITE !,"Reserved?: ",$SYSTEM.SQL.IsReservedWord("VarChar")
WRITE !,"Reserved?: ",$SYSTEM.SQL.IsReservedWord("FRED")
This method can also be called as a stored procedure from ODBC or JDBC: %SYSTEM.SQL_IsReservedWord("nnnn").