Introduction

 Top Next

Introduction

Welcome to NexusDB SQL Reference

By choosing NexusDB as your RDBMS, you have acquired access to a modern, state of the art database technology, exposed through standard client data access technologies like Microsoft ODBC and ADO.NET, and Borland VCL and DBExpress.

NexusDB SQL is an interface to the NexusDB core-engine, an advanced and highly efficient relational database management server. This manual provides the information needed to take advantage of the built-in SQL language in NexusDB.

Featuring SQL:2003

NexusDB implements a large subset of the international standard ISO/IEC 9075 - SQL:2003, including most of the Core SQL functionality as well as many of the additional features defined in the standard. In addition, NexusDB augments SQL:2003 with extensions to expose vendor-specific functionality.

While NexusDB does not claim official conformance to Core SQL, the grammar conforms closely to the syntax and processing rules defined in SQL:2003. Any difference between NexusDB SQL and SQL:2003 is documented in the Conformance summary at the bottom of each topic page.

The advantage of using standard SQL

While SQL has been the dominant database language for more than three decades, and also being standardized since 1986, the major RDBMS vendors have implemented proprietary grammar and SQL dialects to meet demands in the market not covered by the standard at the time. The result has been that SQL statements tuned to execute efficiently on a particular RDBMS, will often fail when ported to a different RDBMS.

The recent SQL standards, starting with SQL-92, and particularly the SQL:1999 major revision and the latest SQL:2003 minor revision, implement all commercially accepted functionality found in the proprietary implementations, and probably more than a single vendor will ever offer.

It is expected that major RDBMS vendors are currently working on adopting their implementations to the recent standards. By exposing SQL grammar that conforms to SQL:2003 now, NexusDB users have the advantage of being able to write standard SQL statements that can target a variety of RDBMS in a foreseeable future.

NexusDB SQL main features

§

Standard SQL:2003

§

Views

§

Referential Integrity

§

Triggers - Active Database

§

Stored Procedures and Functions

§

SQL/PSM Procedure Language

§

Transaction Management

§

Fulltext Index Support

About NexusDB SQL Reference


About NexusDB SQL Reference

 Top Previous Next

About NexusDB SQL Reference

The intention of this manual is to provide a complete reference of the SQL language used in NexusDB, with detailed documentation of the syntax and proper explanation of the usage.

It is assumed that readers of the manual have at least basic knowledge of SQL. While the many examples throughout the manual are well suited as small tutorials, illustrating how the grammar and syntactic elements are used in SQL statements, we recommend that users who want to learn SQL well, read a good text book about standard SQL.

Since NexusDB has a modern SQL implementation based on the latest SQL:2003 standard, books describing SQL:1999 or SQL:2003 would be an excellent source. Application programmers and database administrators who want a more comprehensive presentation and documentation of standard SQL, might want to acquire a copy of the excellent SQL:1999 book by Jim Melton (the editor of SQL-92, SQL:1999 and SQL:2003), or even the SQL:2003 standard itself:

§

SQL:1999 Understanding Relational Language Components

Jim Melton, Alan R. Simon

ISBN 1-55860-456-1

§

ISO/IEC 9075:2003

http://www.iso.org

Organization and layout of topics

The table of contents (TOC) has been organized in chapters and related sub-chapters following the structure of the main parts of the SQL language.

The topic pages have generally been structured as follows:

§

Syntax

§

Usage

§

Notes

§

Examples

§

Conformance summary

In some of the major topic pages, like the CREATE TABLE statement and the SELECT statement, the Notes section has been split into several parts to detail the documentation of complex syntax and functionality.

The Conformance summary at the bottom of each topic page documents the level of conformance to SQL:2003, differences between the standard and NexusDB SQL, and vendor-specific extensions.

BNF conventions

The syntax of the SQL grammar is documented by using a variation of BNF (Backus Normal Form). A BNF production consists of three parts, a non-terminal symbol enclosed in angle brackets, a definition operator (::=) and a production rule defining the syntactic elements on the left side. The production rule may include one or more non-terminal symbols that are resolved in separate BNF productions. The following example shows a simple BNF production:

<current time value function> ::= CURRENT_TIME | LOCALTIME

Since NexusDB SQL is an implementation of SQL:2003, most of the BNF productions are based on standard SQL definitions, but simplified when symbols are self-explanatory. For example, instead of defining the non-terminal symbol <table name> in a separate BNF production, we are using the terminal symbol table-name.

Symbols used in BNF

Symbol | Description
< > | A character string enclosed in angle brackets is the name of a non-terminal symbol.
::= | The definition operator is used in a production rule to separate the element defined by the rule from its definition. The element being defined appears to the left of the operator and the formula that defines the element appears to the right.
[ ] | Square brackets indicate optional elements in a formula. The portion of the formula within the brackets may be explicitly specified or may be omitted.
{ } | Braces group elements in a formula. The portion of the formula within the braces shall be explicitly specified.
| | The alternative operator. The vertical bar indicates that the portion of the formula following the bar is an alternative to the portion preceding the bar. If the vertical bar appears at a position where it is not enclosed in braces or square brackets, it specifies a complete alternative for the element defined by the production rule. If the vertical bar appears in a portion of a formula enclosed in braces or square brackets, it specifies alternatives for the contents of the innermost pair of such braces or brackets.
. . . | The ellipsis indicates that the element to which it applies in a formula may be repeated any number of times. If the ellipsis appears immediately after a closing brace, then it applies to the portion of the formula enclosed between that closing brace and the corresponding opening brace . If an ellipsis appears after any other element, then it applies only to that element.

While the BNF productions may look rather complex at first, they provide a very precise documentation of the SQL grammar. We believe that users who are unfamiliar with BNF, will gain a better understanding of the flexibility and power of NexusDB SQL by spending some time on studying the BNF for frequently used SQL statements and other syntactic elements.

In the online version of this manual, all BNF non-terminal symbols that are part of a production rule, appear as green colored hot-links. Just click the indexed word with the mouse to jump directly to the topic page where the symbol is resolved.

Examples

A large part of the examples provided in the manual are referencing the "Students Database", a small demo database that ships with the NexusDB installation. The "Students Database" is a collection of the following tables:

§

students

§

courses

§

enrolls

§

sections

§

teachers

Before executing example statements that are making changes to these tables, it is recommended to backup the "Students Database" first. Another way of working with the sample data without persisting any changes, is to make temporary versions of the tables using either a CREATE TABLE statement with the AS subquery clause, or a SELECT INTO statement.

In the examples throughout the manual, we have chosen the notation of writing keywords in upper-case and identifiers in lower-case or mixed case to clearly distinguish between two in the text. However, the SQL language is case-insensitive to keywords and regular identifiers, and in NexusDB SQL also to delimited identifiers, meaning that SQL statements can be written in any case.


SQL Language Elements

SQL Language Elements

This section describes the language elements used in SQL statements.

Keywords

Identifiers

Comments

Operators

Null Values

Literals

Value Expressions

Parameters


Keywords

Keywords

Keywords are words recognized by SQL to have a distinct meaning within the context in which they appear. Keywords are used in SQL statements to identify the statement itself, the SQL grammar that specifies processing actions and the SQL elements used by the statement.

The SQL language is case-insensitive. Therefore, keywords, identifiers and other names written in upper case, lower case or mixed case with the same spelling mean the same thing. Throughout this manual, keywords are written in upper case to distinguish keywords from identifiers.

Many SQL keywords are reserved words. Reserved words cannot be used as names for database objects such as tables, columns, views etc. unless the word is enclosed by double quotes. For example, "Date" is a valid column name, while Date would cause an exception.

Overview of keywords in NexusDB SQL

Keyword

Res.

Conformance

Keyword

Res.

Conformance

A |  |  |  |  |
ABS | ü | SQL:2003 | ADD |  | SQL:2003
AFTER |  | SQL:2003 | ALL | ü | SQL:2003
ALTER | ü | SQL:2003 | AND | ü | SQL:2003
ANY | ü | SQL:2003 | AS | ü | SQL:2003
ASC |  | SQL:2003 | ASSEMBLY | ü | NexusDB ext
ASSERT | ü | NexusDB ext | ATAN | ü | NexusDB ext
ATAN2 | ü | NexusDB ext | ATN2 | ü | NexusDB ext
ATOMIC | ü | SQL:2003 | AUTHORIZATION | ü | SQL:2003
AUTOINC | ü | NexusDB ext | AVG | ü | SQL:2003
B |  |  |  |  |
BEFORE |  | SQL:2003 | BEGIN | ü | SQL:2003
BETWEEN | ü | SQL:2003 | BIGINT | ü | SQL:2003
BINARY | ü | SQL:2003 | BLOB | ü | SQL:2003
BLOCK |  | NexusDB ext | BLOCKSIZE |  | NexusDB ext
BOOL | ü | NexusDB ext | BOOLEAN | ü | SQL:2003
BOTH | ü | SQL:2003 | BROUND | ü | NexusDB ext
BY | ü | SQL:2003 | BYTE | ü | NexusDB ext
BYTEARRAY | ü | NexusDB ext |  |  |
C |  |  |  |  |
CALL | ü | SQL:2003 | CALLED | ü | SQL:2003
CASCADE |  | SQL:2003 | CASE | ü | SQL:2003
CAST | ü | SQL:2003 | CATCH | ü | NexusDB ext
CEIL | ü | SQL:2003 | CEILING | ü | SQL:2003
CHAR | ü | SQL:2003 | CHAR_LENGTH | ü | SQL:2003
CHARACTER | ü | SQL:2003 | CHARACTER_LENGTH | ü | SQL:2003
CHARACTERS |  | SQL:2003 | CHECK | ü | SQL:2003
CHR | ü | NexusDB ext | CLOB | ü | SQL:2003
CLR |  | NexusDB ext | COALESCE | ü | SQL:2003
CODEPAGE |  | NexusDB ext | COLLATE | ü | SQL:2003
COLLATION |  | SQL:2003 | COLUMN | ü | SQL:2003
COMMIT | ü | SQL:2003 | CONSTRAINT | ü | SQL:2003
CONTAINS |  | SQL:2003 | COS | ü | NexusDB ext
COUNT | ü | SQL:2003 | CREATE | ü | SQL:2003
CROSS | ü | SQL:2003 | CURRENT_DATE | ü | SQL:2003
CURRENT_TIME | ü | SQL:2003 | CURRENT_TIMESTAMP | ü | SQL:2003
CURRENT_USER | ü | SQL:2003 |  |  |
D |  |  |  |  |
DATA |  | SQL:2003 | DATE | ü | SQL:2003
DATETIME | ü | NexusDB ext | DAY | ü | SQL:2003
DEC | ü | SQL:2003 | DECIMAL | ü | SQL:2003
DECLARE | ü | SQL:2003 | DEFAULT | ü | SQL:2003
DELETE | ü | SQL:2003 | DELETING | ü | NexusDB ext
DESC |  | SQL:2003 | DESCRIPTION |  | NexusDB ext
DETERMINISTIC | ü | SQL:2003 | DISTINCT | ü | SQL:2003
DO | ü | SQL:2003 | DOUBLE | ü | SQL:2003
DROP | ü | SQL:2003 | DWORD | ü | NexusDB ext
E |  |  |  |  |
EACH | ü | SQL:2003 | ELSE | ü | SQL:2003
ELSEIF | ü | NexusDB ext | EMPTY | ü | NexusDB ext
ENCRYPT |  | NexusDB ext | ENCRYPTION |  | NexusDB ext
END | ü | SQL:2003 | ENGINE |  | NexusDB ext
EQUIVALENT | ü | NexusDB ext | ERROR_MESSAGE | ü | NexusDB ext
ESCAPE | ü | SQL:2003 | EXCEPT | ü | SQL:2003
EXISTS | ü | SQL:2003 | EXP | ü | SQL:2003
EXTENDED | ü | NexusDB ext | EXTERNAL | ü | SQL:2003
EXTRACT | ü | SQL:2003 |  |  |
F |  |  |  |  |
FALSE | ü | SQL:2003 | FIRST |  | SQL:2003
FLOAT | ü | SQL:2003 | FLOOR | ü | SQL:2003
FOR | ü | SQL:2003 | FOREIGN | ü | SQL:2003
FROM | ü | SQL:2003 | FULL | ü | SQL:2003
FUNCTION | ü | SQL:2003 |  |  |
G |  |  |  |  |
GLOBAL | ü | SQL:2003 | GROUP | ü | SQL:2003
GROW |  | NexusDB ext | GROWSIZE |  | NexusDB ext
GUID | ü | NexusDB ext |  |  |
H |  |  |  |  |
HAVING | ü | SQL:2003 | HOUR | ü | SQL:2003
I |  |  |  |  |
IDENTITY | ü | SQL:2003 | IF | ü | SQL:2003
IGNORE | ü | NexusDB ext | IMAGE | ü | NexusDB ext
IN | ü | SQL:2003 | INDEX | ü | NexusDB ext
INITIAL |  | NexusDB ext | INITIALSIZE |  | NexusDB ext
INNER | ü | SQL:2003 | INOUT | ü | SQL:2003
INPUT |  | SQL:2003 | INSERT | ü | SQL:2003
INSERTING | ü | NexusDB ext | INT | ü | SQL:2003
INTEGER | ü | SQL:2003 | INTERSECT | ü | SQL:2003
INTERVAL | ü | SQL:2003 | INTO | ü | SQL:2003
IS | ü | SQL:2003 | ITERATE | ü | SQL:2003
J |  |
JOIN | ü | SQL:2003
K |  |  |
KANA | NexusDB ext | KEY | SQL:2003
L |  |  |  |  |
LANGUAGE | ü | SQL:2003 | LARGE | ü | SQL:2003
LARGEINT | ü | NexusDB ext | LAST |  | SQL:2003
LASTAUTOINC | ü | NexusDB ext | LEADING | ü | SQL:2003
LEAVE | ü | SQL:2003 | LEFT | ü | SQL:2003
LIKE | ü | SQL:2003 | LIST | ü | NexusDB ext
LN | ü | SQL:2003 | LOCAL | ü | SQL:2003
LOCALE | ü | NexusDB ext | LOCALTIME | ü | SQL:2003
LOCALTIMESTAMP | ü | SQL:2003 | LOWER | ü | SQL:2003
M |  |  |  |  |
MATCH | ü | SQL:2003 | MAX | ü | SQL:2003
MED | ü | NexusDB ext | MIN | ü | SQL:2003
MINUTE | ü | SQL:2003 | MOD | ü | SQL:2003
MODIFIES | ü | SQL:2003 | MONEY | ü | NexusDB ext
MONTH | ü | SQL:2003 |  |  |
N |  |  |  |  |
NAME |  | SQL:2003 | NATIONAL | ü | SQL:2003
NATURAL | ü | SQL:2003 | NCHAR | ü | SQL:2003
NCLOB | ü | SQL:2003 | NEW | ü | SQL:2003
NEWGUID | ü | NexusDB ext | NO | ü | SQL:2003
NONSPACE |  | NexusDB ext | NOT | ü | SQL:2003
NSINGLECHAR | ü | NexusDB ext | NULL | ü | SQL:2003
NULLIF | ü | SQL:2003 | NULLS |  | SQL:2003
NULLSTRING | ü | NexusDB ext | NUMERIC | ü | SQL:2003
NVARCHAR | ü | NexusDB ext |  |  |
O |  |  |  |  |
OBJECT |  | SQL:2003 | OCTET_LENGTH | ü | SQL:2003
OCTETS |  | SQL:2003 | ODD | ü | NexusDB ext
OF | ü | SQL:2003 | OLD | ü | SQL:2003
ON | ü | SQL:2003 | OR | ü | SQL:2003
ORD | ü | NexusDB ext | ORDER | ü | SQL:2003
OUT | ü | SQL:2003 | OUTER | ü | SQL:2003
P |  |  |  |  |
PARTIAL |  | SQL:2003 | PASSWORDS | ü | NexusDB ext
PERCENT |  | NexusDB ext | PI | ü | NexusDB ext
POSITION | ü | SQL:2003 | POWER | ü | SQL:2003
PRECISION | ü | SQL:2003 | PRIMARY | ü | SQL:2003
PROCEDURE | ü | SQL:2003 |  |  |
R |  |  |  |  |
RAND | ü | NexusDB ext | READS | ü | SQL:2003
REAL | ü | SQL:2003 | RECREV | ü | NexusDB ext
REFERENCES | ü | SQL:2003 | REFERENCING | ü | SQL:2003
REMOVE |  | NexusDB ext | REPEAT | ü | SQL:2003
RESTRICT |  | SQL:2003 | RETURN | ü | SQL:2003
RETURNS | ü | SQL:2003 | RIGHT | ü | SQL:2003
ROLLBACK | ü | SQL:2003 | ROUND | ü | NexusDB ext
ROUTINE |  | SQL:2003 | ROW | ü | SQL:2003
ROWSAFFECTED | ü | NexusDB ext | ROWSREAD | ü | NexusDB ext
S |  |  |  |  |
SECOND | ü | SQL:2003 | SELECT | ü | SQL:2003
SERIALIZABLE |  | SQL:2003 | SESSION_USER | ü | SQL:2003
SET | ü | SQL:2003 | SHORTINT | ü | NexusDB ext
SHORTSTRING | ü | NexusDB ext | SIGNAL | ü | SQL:2003
SIMPLE |  | SQL:2003 | SIN | ü | NexusDB ext
SINGLECHAR | ü | NexusDB ext | SMALLINT | ü | SQL:2003
SNAPSHOT |  | NexusDB ext | SOME | ü | SQL:2003
SORT |  | NexusDB ext | SQL | ü | SQL:2003
SQRT | ü | SQL:2003 | START | ü | SQL:2003
STD | ü | NexusDB ext | STORAGE |  | NexusDB ext
STRING |  | NexusDB ext | SUBSTRING | ü | SQL:2003
SUM | ü | SQL:2003 | SYMBOLS |  | NexusDB ext
SYSTEM_ROW# | ü | NexusDB ext |  |  |
T |  |  |  |  |
TABLE | ü | SQL:2003 | TEMPORARY |  | SQL:2003
TEXT | ü | NexusDB ext | THEN | ü | SQL:2003
TIME | ü | SQL:2003 | TIMESTAMP | ü | SQL:2003
TINYINT | ü | NexusDB ext | TO | ü | SQL:2003
TOP |  | NexusDB ext | TOSTRING | ü | NexusDB ext
TOSTRINGLEN | ü | NexusDB ext | TRAILING | ü | SQL:2003
TRANSACTION |  | SQL:2003 | TRIGGER | ü | SQL:2003
TRIM | ü | SQL:2003 | TRUE | ü | SQL:2003
TRY | ü | NexusDB ext | TYPE |  | SQL:2003
U |  |  |  |  |
UNION | ü | SQL:2003 | UNIQUE | ü | SQL:2003
UNKNOWN | ü | SQL:2003 | UNTIL | ü | SQL:2003
UPDATE | ü | SQL:2003 | UPDATING | ü | NexusDB ext
UPPER | ü | SQL:2003 | USE |  | NexusDB ext
USER | ü | SQL:2003 | USING | ü | SQL:2003
V |  |  |  |  |
VALUES | ü | SQL:2003 | VARCHAR | ü | SQL:2003
VARYING | ü | SQL:2003 | VIEW |  | SQL:2003
W |  |  |  |  |
WHEN | ü | SQL:2003 | WHERE | ü | SQL:2003
WHILE | ü | SQL:2003 | WIDTH |  | NexusDB ext
WITH | ü | SQL:2003 | WORD | ü | NexusDB ext
WORK |  | SQL:2003 |  |  |
Y |  |
YEAR | ü | SQL:2003


Identifiers

Identifiers

SQL provides two types of identifiers that are used to specify the names of database objects, such as tables, columns, constraints and indexes, views, triggers, and user-defined procedures and functions.

NexusDB only allows codepage neutral ANSI characters in identifiers, that is characters having the same ordinal value regardless of codepage. The following table shows the valid characters that can be used in identifiers:

Character | Code | Symbol
Space | #32 |
Exclamation mark | #33 | !
Pound sign | #35 | #
Dollar sign | #36 | $
Percent | #37 | %
Ampersand | #38 | &
Left parenthesis | #40 | (
Right parenthesis | #41 | )
Plus sign | #43 | +
Minus sign | #45 | -
Digit | #48..#57 | 0..9
At sign | #64 | @
Upper case letter | #65..#90 | A..Z
Left bracket | #91 | [
Right bracket | #93 | ]
Circumflex | #94 | ^
Underscore | #95 | _
Lower case letter | #97..#122 | a..z
Left brace | #123 | {
Right brace | #125 | }
Tilde | #126 | ~

Regular identifiers

A regular identifier is an unquoted string of characters.

§

Regular identifiers cannot contain spaces or any of the following characters:

Percent (#37)

Ampersand (#38)

Left parenthesis (#40)

Right parenthesis (#41)

Plus sign (#43)

Minus sign (#45)

Left bracket (#91)

Right bracket (#93)

§

Regular identifiers cannot begin with a decimal digit.

§

Regular identifiers are case-insensitive.

§

The maximum length of regular identifiers is 128 characters.

Delimited identifiers

A delimited identifier is a string of characters enclosed by double quotation marks.

§

Delimited identifiers can contain spaces, but not as leading or trailing characters.

§

Delimited identifiers are case-insensitive in NexusDB SQL, while they are case-sensitive in SQL:2003.

§

The maximum length of delimited identifiers is 128 characters.

Schema-qualified names

All persistent database objects, such as tables, views, triggers and user-defined procedures and functions, have a two-part name consisting of the schema name and the object name separated by a period. For example, a table with the name "students" that is stored in a database called "students database", can be referenced in either of the following ways:

§

"students database"."students"

§

"students database".students

§

"students"

§

students

§

Schema and database object names shall be a valid regular or delimited identifier.

§

NexusDB table names must additionally follow the requirements for valid file names in Windows.

§

A database object that is stored in the current database may be referenced by its unqualified one-part name, or the schema-qualified two-part name for clarity.

§

A database object that is stored in a database other than the current database, must be referenced by its schema-qualified two-part name.


Comments

Comments

Comments are text embedded in SQL statements without being executed as part of the statement.

Simple comments

NexusDB SQL supports two variations of simple comments. The first variation, which is the syntax defined by SQL:2003, starts with two minus signs (--). The second variation, which is known from traditional programming languages like C and others, starts with two slashes (//). Simple comments can only appear in single lines and are terminated by the end of the line.

The following example illustrates the use of simple comments:

// The three pound symbols define a global temporary table

CREATE TABLE ###Temp (

c1 AUTOINC NOT NULL PRIMARY KEY, -- We don't allow null values in this column

c2 VARCHAR(50) // This is an ordinary character string column

)

Bracketed comments

Bracketed comments start with a slash followed by an asterisk and end with an asterisk followed by a slash. Bracketed comments can be embedded anywhere in an SQL statement and may span multiple lines.

The following example illustrates the use of bracketed comments:

/*

SQL statements must be terminated with a semicolon when they're executed as a sequence (statement block).

We need to drop the table before recreating it.

*/

/* The IF EXISTS clause prevents an exception if the table doesn't exist */

DROP TABLE IF EXISTS /*IF EXISTS is a NexusDB extension*/ ###Temp;

CREATE TABLE ###Temp (

c1 AUTOINC NOT NULL PRIMARY KEY,

c2 VARCHAR(50)

);

Conformance

SQL:2003 standard

NexusDB extensions

-

-

-

Core SQL

Feature T351 "Bracketed comments"

C-style comments


Operators

Operators

Operators are symbols and keywords that specify an operation to be performed on one or more value expressions.

Arithmetic operators

The arithmetic operators perform operations on numeric values.

Operator | Symbol | Operation
<addition operator> | + | Addition
<subtraction operator> | - | Subtraction
<multiplication operator> | * | Multiplication
<division operator> | / | Division

Note: The addition (+) and subtraction (-) operators are also used in arithmetic operations on datetime values.

See also: Numeric Value Functions.

Concatenation operators

The concatenation operator is used to concatenate string values.

Operator | Symbol | Operation
<concatenation operator> | || | String concatenation

Note: The arithmetic addition (+) operator can be used as an alternate string concatenation operator.

See also: String Value Functions.

Comparison operators

The comparison operators are used in some predicates to specify comparison tests.

Operator | Symbol | Operation
<equals operator> | = | Equal to
<not equals operator> | <> | Not equal to
<less than operator> | < | Less than
<greater than operator> | > | Greater than
<less than or equals operator> | <= | Less than or equal to
<greater than or equals operator> | >= | Greater than or equal to

Note: The equals sign (=) is also used as an assignment operator.

Boolean operators

The boolean operators are used to combine predicates in search conditions.

Operator | Keyword | Operation
<boolean NOT operator> | NOT | Invert the result of the boolean test
<boolean AND operator> | AND | Test if both predicates are TRUE
<boolean OR operator> | OR | Test if either of the predicates is TRUE

Note: Check the truth tables to see how combined boolean expressions are evaluated.

Assignment operator

The equals sign is used for value assignments.

Operator | Symbol | Operation
<assignment operator> | = | Value assignment

Operator precedence

Operator precedence determines the order in which operations are performed during the execution of a complex value expression with multiple operators.

Operators are evaluated in the following order, ranked from highest to lowest precedence level:

§

* (multiplication), / (division)

§

+ (addition), - (subtraction)

§

||, + (concatenation)

§

=, <>, <, >, <=, >= (comparison operators)

§

NOT

§

AND

§

OR

Operators on the same precedence level are evaluated left to right based on their position in the value expression.

If a value expression has nested parentheses, the innermost expression is evaluated first.

Tip: Instead of relying on the defined operator precedence, it is recommended to use parentheses to explicitly specify the order of operations in complex value expressions with many operators. All operations inside parentheses are evaluated first to yield a single value before that value is used as an operand in other operations.

Conformance

SQL:2003 standard

NexusDB extensions

-

-

Core SQL

Plus sign as string concatenation operator


Null Values

Null Values

SQL uses a special value, the null value, to indicate that a data element does not have a value. Every data element, such as table columns, variables, parameters, value expressions, the result of functions, etc., can have a null value. An exception to this rule are columns with the NOT NULL constraint, which prohibits columns from containing the null value in any row.

The null value is different from any valid value, and is therefore not the same as zero, blanks or an empty string. This concept is often confusing to programmers more familiar with programming languages like C and Pascal that define a "null string" to mean a string with length 0. In SQL, an empty string is a known value (it has no characters), while null is an unknown value.

SQL provides the keyword NULL to represent a null value in SQL statements. However, since the keyword does not associate a data type with the null value, SQL also provides a syntactic ability to associate a data type with the null value by using an explicit cast:

CAST( NULL AS data-type )

As a consequence of the null value concept, SQL uses a three-valued logic (TRUE, FALSE, UNKNOWN) when evaluating boolean expressions.

The main characteristics of the null value are summarized below:

§

The null value is an unknown value.

§

Null values are not distinct, meaning that it's not possible to distinguish between two null values.

§

Null values are treated as a single group in grouping operations.

§

Null values are sorted before all non-null values by default. The null ordering sort option may however specify that nulls are sorted last.

§

Null values as operands of arithmetic operations and string concatenations, or as function arguments, will cause the result to be null.

§

Some of the predicates, but not all, will evaluate to UNKNOWN if one of the predicands is the null value.

Conformance

SQL:2003 standard

-

Core SQL


Literals

Literals

Specify a non-null value.

Syntax

<literal> ::=

|

<signed numeric literal>

<general literal>

<unsigned literal> ::=

|

<unsigned numeric literal>

<general literal>

<general literal> ::=

|

|

|

|

|

|

<character string literal>

<national character string literal>

<guid literal>

<binary string literal>

<datetime literal>

<interval literal>

<boolean literal>

Usage

Literals, commonly perceived as constants, can be used in SQL everywhere a value can be specified. Since literals have a data type, they can only be used in places where data of the particular type is permitted.

Conformance

SQL:2003 standard

-

-

-

-

-

Core SQL

Feature F421 "National character"

Feature T041 "Basic LOB data type support"

Feature F052 "Intervals and datetime arithmetic"

Feature T031 "BOOLEAN data type"

Numeric Literals

String Literals

DateTime Literals

Interval Literals

Boolean Literals


Numeric Literals

Numeric Literals

Syntax

<signed numeric literal> ::= [ + | - ] <unsigned numeric literal>

<unsigned numeric literal> ::=

|

<exact numeric literal>

<approximate numeric literal>

<exact numeric literal> ::=

|

<unsigned integer> [ . [ <unsigned integer> ] ]

. <unsigned integer>

<unsigned integer> ::= <digit>...

<digit> ::= 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9

<approximate numeric literal> ::= <mantissa> E <exponent>

<mantissa> ::= <exact numeric literal>

<exponent> ::= <signed integer>

<signed integer> ::= [ + | - ] <unsigned integer>

Notes

§

An exact numeric literal without a decimal fraction specified denotes an integer value. The implicit data type is BIGINT.

§

An exact numeric literal with a decimal fraction specified denotes a BCD value. The implicit data type is DECIMAL.

§

The number of digits following the decimal point is determining the implicit scale of an exact numeric literal. If the specified scale exceeds the maximum scale of 4, then the value is truncated to scale 4.

Examples

Data Type | Literal Examples
SHORTINT | 96
SMALLINT | 5247
INTEGER | 258476
BIGINT | 2147483648
NUMERIC | 14.99 15.
DECIMAL | 14.99 15.
FLOAT | 1.56E-4 200E10
REAL | 1.56E-4
DOUBLE PRECISION | 3.1415929265432E00
EXTENDED | 3.1415929265432E00

Conformance

SQL:2003 standard

-

Core SQL


String Literals

String Literals

Syntax

<character string literal> ::=

<quote> [ <character representation>... ] <quote>

[ { separator <quote> [ <character representation>... ] <quote> }... ]

<national character string literal> ::=

[ N ] <quote> [ <character representation>... ]

[ { separator <quote> [ <character representation>... ] <quote> }... ]

<guid literal> ::=

[ GUID ] <quote> <character representation>... <quote>

<binary string literal> ::=

X <quote> [ { <hexit> <hexit> }... ] <quote>

[ { separator <quote> [ { <hexit> <hexit> }... ] <quote> }... ]

<character representation> ::=

|

nonquote-character

<quote symbol>

<quote symbol> ::= <quote><quote>

<quote> ::= '

<hexit> ::= <digit> | A | B | C | D | E | F | a | b | c | d | e | f

Notes

§

The notation for embedding a quote in a character string literal is two quote characters without space between them.

§

The N prefix is needed to implicitly cast a literal to a national character string, but is optional if the assignment target is a national character string type.

§

Character string literals are comparable to any character string independent of the collation, and they are implicitly converted to the character set and collation of the target site in an assignment.

§

The character representation of a GUID literal shall conform to the format of a GUID value, see the example below. The GUID prefix is optional if the assignment target is a GUID type.

§

The V1 syntax of specifying a binary string literal as hexadecimal values inside square brackets is still supported, but the extension has been deprecated in favor of standard SQL syntax using the X prefix, and may be removed from a future version of NexusDB SQL.

§

Feature F271 "Compound character literals" provides the syntactical ability to split string literals onto multiple lines by separating each part with a line-feed.

Examples

Data Type | Literal Examples
CHAR | 'A character string literal is a sequence of characters enclosed in single quotes.'
VARCHAR | 'The quote character ( '' ) is embedded in a literal by using two quote characters.'
CLOB | 'Character string literals are also used with CLOB types. ' 'String literals may be split onto multiple lines like this example.'
NCHAR | N'In formal SQL:2003 syntax, national character string literals are prefixed with a N.'
NCHAR | 'NexusDB SQL supports national character string literals without the N prefix.'
GUID | GUID '{E5C37F97-0F9F-47FB-8B55-30FDEC0026E1}'
BLOB | X'4E6578757344422056322069732061206772656174205244424D532E'

Conformance

SQL:2003 standard

NexusDB extensions

-

-

-

-

-

-

-

Core SQL

Feature F421 "National character"

Feature T041 "Basic LOB data type support"

Feature F271 "Compound character literals"

The N prefix in a national character string literal is optional

GUID literal

Binary literal notation with hexadecimal values inside square brackets


DateTime Literals

DateTime Literals

Syntax

<datetime literal> ::=

|

|

<date literal>

<time literal>

<timestamp literal>

<date literal> ::= DATE <date string>

<time literal> ::= TIME <time string>

<timestamp literal> ::= TIMESTAMP <timestamp string>

<date string> ::= <quote> year-month-day <quote>

<time string> ::= <quote> hour:minute:second [ . [ millisecond ] ] <quote>

<timestamp string> ::= <quote> year-month-day hour:minute:second [ . [ millisecond ] ] <quote>

Examples

Data Type | Literal Examples
DATE | DATE '2004-09-25'
TIME | TIME '14:28:36'
TIMESTAMP | TIMESTAMP '2004-09-25 14:28:36'

Conformance

SQL:2003 standard

-

Core SQL


Interval Literals

Interval Literals

Syntax

<interval literal> ::=

INTERVAL [ + | - ] <interval string> <interval qualifier>

<interval string> ::=

<quote> [ + | - ] { <year-month literal> | <day-time literal> } <quote>

<year-month literal> ::=

|

years [ -months ]

months

<day-time literal> ::=

|

<day-time interval>

<time interval>

<day-time interval> ::=

day [ space hours [ :minutes [ :seconds [ . [ milliseconds ] ] ] ] ]

<time interval> ::=

|

|

hours [ :minutes [ :seconds [ . [ milliseconds ] ] ] ]

minutes [ :seconds [ . [ milliseconds ] ] ]

seconds [ . [ milliseconds ] ]

<interval qualifier> ::=

|

<non-second primary datetime field> TO <primary datetime field>

<primary datetime field>

<primary datetime field> ::=

|

<non-second primary datetime field>

SECOND

<non-second primary datetime field> ::=

|

|

|

|

|

|

|

|

|

|

|

|

YEAR

MONTH

DAY

HOUR

MINUTE

DAYOFYEAR

ISODAYOFWEEK

ISOWEEK

ISOMONTH

ISOYEAR

ISOWEEKOFMONTH

ISODAYOFMONTH

ISODAYOFYEAR

Notes

§

Interval literals can only be used in datetime arithmetic.

§

If the interval string contains a year-month literal, then the interval qualifier shall not specify DAY, HOUR, MINUTE or SECOND. If the interval string contains a day-time literal, then the interval qualifier shall not specify YEAR or MONTH.

§

If TO is specified, then the start field shall be more significant than the end field, and the start field shall not specify MONTH. If the start field specifies YEAR, then the end field shall specify MONTH.

§

DAYOFYEAR - day of the calendar year (1 to 365/366 starting on year-01-01)

ISODAYOFWEEK - day of week according to ISO 8601 (1 = monday to 7 = sunday)

ISOWEEK - week of year according to ISO 8601

ISOMONTH - month of year according to ISO 8601x (can be different from calendar month)

ISOYEAR - year of week according to ISO 8601 (can be different from calendar year)

ISOWEEKOFMONTH - week of month according to ISO 8601x

ISODAYOFMONTH - day of month according to ISO 8601x

ISODAYOFYEAR - day of year according to ISO 8601

Examples

Literal Examples

CURRENT_TIMESTAMP + INTERVAL '2' YEAR

CURRENT_TIMESTAMP + INTERVAL '2-6' YEAR TO MONTH

CURRENT_TIMESTAMP + INTERVAL '6' MONTH

CURRENT_TIMESTAMP + INTERVAL '15' DAY

CURRENT_TIMESTAMP + INTERVAL '15 12' DAY TO HOUR

CURRENT_TIMESTAMP + INTERVAL '12' HOUR

CURRENT_TIMESTAMP + INTERVAL '12:25' HOUR TO MINUTE

CURRENT_TIMESTAMP + INTERVAL '12:25:30' HOUR TO SECOND

CURRENT_TIMESTAMP + INTERVAL '25' MINUTE

CURRENT_TIMESTAMP + INTERVAL '25:30' MINUTE TO SECOND

The following SQL can be used to check out samples of the returned values:

CREATE LOCAL TEMPORARY TABLE #dates (

dt DATE

);

DECLARE StartDT, EndDT, CurrentDT DATE;

SET StartDT = CURRENT_TIMESTAMP;

SET EndDT = StartDT + INTERVAL '5' YEAR;

SET CurrentDT = StartDT;

WHILE CurrentDT < EndDT DO

INSERT INTO #dates (dt) VALUES (CurrentDT);

SET CurrentDT = CurrentDT + INTERVAL '1' DAY;

END WHILE;

SELECT

*,

EXTRACT(DAYOFYEAR FROM dt) AS "DAYOFYEAR",

EXTRACT(ISODAYOFWEEK FROM dt) AS "ISODAYOFWEEK",

EXTRACT(ISOWEEK FROM dt) AS "ISOWEEK",

EXTRACT(ISOMONTH FROM dt) AS "ISOMONTH",

EXTRACT(ISOYEAR FROM dt) AS "ISOYEAR",

EXTRACT(ISOWEEKOFMONTH FROM dt) AS "ISOWEEKOFMONTH",

EXTRACT(ISODAYOFMONTH FROM dt) AS "ISODAYOFMONTH",

EXTRACT(ISODAYOFYEAR FROM dt) AS "ISODAYOFYEAR"

FROM

#dates;

Conformance

SQL:2003 standard

-

Feature F052 "Intervals and datetime arithmetic"


Boolean Literals

Boolean Literals

Syntax:

<boolean literal> ::=

|

|

TRUE

FALSE

UNKNOWN

Notes

§

The boolean literal UNKNOWN is equivalent to the null value.

Conformance

SQL:2003 standard

-

Feature T031 "BOOLEAN data type"


Value Expressions

Value Expressions

Specify a scalar value.

Syntax

<value expression> ::=

|

|

<common value expression>

<boolean value expression>

<row value expression>

<common value expression> ::=

|

|

<numeric value expression>

<string value expression>

<datetime value expression>

<value expression primary> ::=

|

( <value expression> )

<nonparenthesized value expression primary>

<nonparenthesized value expression primary> ::=

|

|

|

|

|

|

|

<unsigned value specification>

<general value specification>

column-reference

<aggregate function>

<scalar subquery>

<case expression>

<cast specification>

<routine invocation>

<value specification> ::=

|

<literal>

<general value specification>

<unsigned value specification> ::=

|

<unsigned literal>

<general value specification>

<general value specification> ::=

|

|

|

|

|

|

|

|

SQL-parameter-reference

SQL-variable-reference

<dynamic parameter specification>

USER

CURRENT_USER

SESSION_USER

SYSTEM_ROW#

NEWGUID

ROWSREAD

ROWSAFFECTED

<target specification> ::=

|

|

|

SQL-parameter-reference

SQL-variable-reference

column-reference

<dynamic parameter specification>

Usage

Value expressions return values with a data type declared by the expression, and can be used in SQL everywhere a value is expected and the particular data type is permitted. Value expressions can be as simple as a literal or a column reference, or complex expressions involving arithmetic operations, combined boolean logic, scalar subqueries, case expressions and SQL functions.

Conformance

SQL:2003 standard

-

-

-

-

Core SQL

Feature F052 "Intervals and datetime arithmetic"

Feature T031 "BOOLEAN data type"

Feature F471 "Scalar subquery values"

Numeric Value Expressions

String Value Expressions

DateTime Value Expressions

Boolean Value Expressions

Row Value Expressions

Subqueries

Case Expressions

Cast Specification


Numeric Value Expressions

Numeric Value Expressions

Specify a numeric value.

Syntax

<numeric value expression> ::=

|

|

<term>

<numeric value expression> + <term>

<numeric value expression> - <term>

<term> ::=

|

|

<factor>

<term> * <factor>

<term> / <factor>

<factor> ::= [ { + | - } ] <numeric primary>

<numeric primary> ::=

|

<value expression primary>

<numeric value function>

Notes

§

The declared type of a numeric primary shall be a numeric type.

§

If both operands are exact numeric types, then the data type of the result is an exact numeric type with precision not less than the maximum precision of the two operands and the scale determined as follows:

§

If addition or subtraction is specified, then the scale is the maximum of the two operands.

§

If multiplication is specified, then the scale is the sum of the two operand scales, limited by the maximum scale of 4.

§

If division is specified, then the scale is the maximum scale 4.

§

If either of the operands are an approximate numeric type, then the result is an approximate numeric type with precision not less than the maximum precision of the two operands.

§

Normal operator precedence is applied.

See also: Numeric Value Functions

Examples

1)

The following example raises all teacher salaries by a given percent:

UPDATE teachers

SET salary = salary * ( 1 + ( :percent / 100 ) )

2)

Some examples of valid numeric value expressions:

§

-5

§ 3 + 1

§ 7/3-16

§ 15 * ( 5-4 )

Conformance

SQL:2003 standard

-

Core SQL


String Value Expressions

String Value Expressions

Specify a character string value or a binary string value.

Syntax

<string value expression> ::=

|

<character value expression>

<blob value expression>

<character value expression> ::=

|

<character value expression> { || | + } <character primary>

<character primary>

<character primary> ::=

|

<value expression primary>

<string value function>

<blob value expression> ::=

<blob value expression> || <blob primary>

<blob primary> ::=

|

<value expression primary>

<string value function>

Notes

§

The declared type of a character primary shall be a character string type.

§

The declared type of a blob primary shall be a binary string type.

§

Two character strings can only be concatenated if they share the same character set and are comparable.

§

The concatenation operator and the arithmetic plus operator can be used interchangeably to concatenate string values. If character string concatenation is specified, then the length of the result is the combined length of the two values, up to the maximum length for the data type.

§

If either of the operands are a character large object type, then the result type of the concatenation is CHARACTER LARGE OBJECT or NATIONAL CHARACTER LARGE OBJECT.

§

If either of the operands are a variable-length character string, then the result type of the concatenation is CHARACTER VARYING or NATIONAL CHARACTER VARYING.

§

If both operands are fixed-length character strings, then the result type of the concatenation is CHARACTER or NATIONAL CHARACTER.

§

If either of the operands are a national character string type, then the result of the concatenation is a national character string type.

§

If either of the operands are null, then the result of the concatenation is null.

See also: String Value Functions

Examples

1)

String concatenation using the concatenation operator:

UPDATE students

SET studentName = firstName || ' ' || lastName

2) String concatenation using the plus operator:

UPDATE students

SET studentName = firstName + ' ' + lastName

Conformance

SQL:2003 standard

NexusDB extensions

-

-

Core SQL

Plus sign as string concatenation operator


DateTime Value Expressions

DateTime Value Expressions

Specify a datetime value.

Syntax

<datetime value expression> ::=

|

|

<datetime primary>

<datetime value expression> + <interval literal>

<datetime value expression> - <interval literal>

<datetime primary> ::=

|

<value expression primary>

<datetime value function>

Notes

§

The declared type of a datetime primary shall be a datetime type.

See also: DateTime Value Functions

Examples

Interval Literals Examples

Conformance

SQL:2003 standard

-

Feature F052 "Intervals and datetime arithmetic"


Boolean Value Expressions

Boolean Value Expressions

Specify a boolean value.

Syntax

<boolean value expression> ::=

|

<boolean term>

<boolean value expression> OR <boolean term>

<boolean term> ::=

|

<boolean factor>

<boolean term> AND <boolean factor>

<boolean factor> ::= [ NOT ] <boolean test>

<boolean test> ::= <boolean primary> [ IS [ NOT ] <truth value> ]

<truth value> ::=

|

|

TRUE

FALSE

UNKNOWN

<boolean primary> ::=

|

<predicate>

<boolean predicand>

<boolean predicand> ::=

|

( <boolean value expression> )

<nonparenthesized value expression primary>

Notes

§

The declared type of a boolean primary shall be boolean.

§

The AND and OR boolean operators are used to combine two or more predicates.

§

The NOT operator inverts the meaning of a boolean expression.

§

The boolean test:

<boolean primary> IS NOT <truth value>

is equivalent to:

NOT <boolean primary> IS <truth value>

Truth tables

Since SQL supports null values and predicates whose value can be UNKNOWN, a three-valued logic is used to evaluate boolean value expressions. The rules of this logic are summarized in the following tables:

Truth Table for the AND Boolean Operator

AND

True

False

Unknown

¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯

True

False

Unknown

True

False

Unknown

False

False

False

Unknown

False

Unknown

_____________________________________________________________________________

Truth Table for the OR Boolean Operator

OR

True

False

Unknown

¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯

True

False

Unknown

True

True

True

True

False

Unknown

True

Unknown

Unknown

_____________________________________________________________________________

Truth Table for the NOT Boolean Operator

NOT

¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯

True

False

Unknown

False

True

Unknown

_____________________________________________________________________________

Truth Table for the IS Boolean Operator

IS

True

False

Unknown

¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯

True

False

Unknown

True

False

False

False

True

False

False

False

True

_____________________________________________________________________________

Examples

1)

The following example selects students from Newport, CA:

SELECT studentID, studentName, city, state

FROM students

WHERE city = 'Newport' AND state = 'CA'

2)

The following example selects students from Newport, CA and RI:

SELECT studentID, studentName, city, state

FROM students

WHERE city = 'Newport' AND ( state = 'CA' OR state = 'RI' )

3)

The following example selects students from Massachusetts and Newport, CA:

SELECT studentID, studentName, city, state

FROM students

WHERE ( city = 'Newport' AND state = 'CA' ) OR state = 'MA'

4)

The following example selects all students except those from California and Boston, MA:

SELECT studentID, studentName, city, state

FROM students

WHERE NOT ( ( city = 'Boston' AND state = 'MA' ) OR state = 'CA' )

Conformance

SQL:2003 standard

-

-

Feature T031 "BOOLEAN data type"

Feature F571 "Truth value tests"


Row Value Expressions

Row Value Expressions

Specify a value or list of values to construct a row value.

Syntax

<row value expression> ::= <row value constructor>

<row value constructor> ::=

|

|

<common value expression>

<boolean value expression>

<explicit row value constructor>

<explicit row value constructor> ::=

( <row value element> , <row value element list> )

<row value element list> ::=

<row value element> [ { , <row value element> }... ]

<row value element> ::=

|

|

<value expression>

NULL

DEFAULT

<row value predicand> ::= <row value constructor predicand>

<row value constructor predicand> ::=

|

|

<common value expression>

<boolean predicand>

<explicit row value constructor>

Notes

§

Row value constructors with more than 1 row value element are only supported in the INSERT statement in NexusDB SQL.

Examples

1) The following example selects students from California and New York:

SELECT studentName, state

FROM students

WHERE state IN ( 'CA', 'NY' )

2)

The following example inserts two new rows into the documents table:

INSERT into documents ( documentID, title )

VALUES ( NEWGUID, 'NexusDB main features' ), ( NEWGUID, 'NexusDB special features' )

Conformance

SQL:2003 standard

-

Core SQL


Subqueries

Subqueries

Specify a scalar value, a row or a table based on a query expression.

Syntax

<scalar subquery> ::= <subquery>

<row subquery> ::= <subquery>

<table subquery> ::= <subquery>

<subquery> ::= ( <query expression> )

Usage

A subquery is a query that is executed as part of another query expression.

Notes

§

Subqueries shall be enclosed in parentheses.

§

Table subqueries are commonly used in predicates, but can also appear in the FROM clause to specify a derived table.

§

Scalar subqueries can be used anywhere a value expression is allowed, including computed columns in the select list. A scalar subquery shall have a degree of one and a cardinality of one.

§

A correlated subquery is a subquery that is referencing data in an outer query. A subquery specified in the select list is typically correlated with the main query to produce meaningful data. Since correlated subqueries depend on data from the outer query, they must normally be evaluated for each row produced by the outer query.

Examples

1)

The following examples uses a subquery with the IN predicate to select students who are enrolled in courseID 730:

SELECT studentID, studentName

FROM students

WHERE studentID IN ( SELECT studentID FROM enrolls WHERE courseID = 730 )

2) The following example uses a subquery in the FROM clause instead of a base table:

SELECT *

FROM ( SELECT studentID, studentName, gender FROM students ) AS student_list

3)

The following example uses a correlated subquery in the select list to count the number of courses related to the selected student:

SELECT

courseID,

studentID,

( SELECT COUNT( * ) FROM enrolls WHERE studentID = e.studentID ) AS numCourses

FROM enrolls e

ORDER BY courseID

Conformance

SQL:2003 standard

-

Core SQL


Case Expressions

Case Expressions

Specify a conditional value.

Syntax

<case expression> ::=

|

<case abbreviation>

<case specification>

<case abbreviation> ::=

|

NULLIF ( <value expression> , <value expression> )

COALESCE ( <value expression> { , <value expression> }... )

<case specification> ::=

|

CASE <case operand> <simple when clause>... [ <else clause> ] END

CASE <searched when clause>... [ <else clause> ] END

<simple when clause> ::= WHEN <when operand> THEN <result>

<searched when clause> ::= WHEN <search condition> THEN <result>

<else clause> ::= ELSE <result>

<case operand> ::= <row value predicand>

<when operand> ::= <row value predicand>

<result> ::=

|

<value expression>

NULL

Usage

The CASE expression is similar in concept to the CASE statement found in some programming languages. It is used in SQL to provide a conditional value, and can be specified anywhere a value expression is allowed.

Notes

§

The searched CASE expression is the main syntactical version, and the most flexible one, since a search condition is specified for each individual WHEN clause.

§

The simple CASE expression is a handy shorthand that eliminates the need for repeating the same value in each WHEN clause when comparing the same value against different conditions.

§

The NULLIF abbreviation compares two values and returns null if the values are equal, else returning the first value specified. This is shorthand syntax for:

CASE WHEN value1 = value2 THEN NULL ELSE value1 END

§

The COALESCE abbreviation returns the first value in a list of comparable values that is not null, or null if all values in the list are the null value. This is shorthand syntax for:

CASE

WHEN value1 IS NOT NULL THEN value1

WHEN value2 IS NOT NULL THEN value2

WHEN valuen IS NOT NULL THEN valuen

ELSE NULL

END

§

If the optional ELSE clause is omitted, then ELSE NULL is implicit.

§

The resulting data type of a CASE expression is determined according to the rules for Result data types of aggregations.

Tip: Use COALESCE in arithmetic expressions to avoid calculations on a null value.

Examples

1)

The following example uses a searched CASE expression to determine a conditional value for the gender_description column:

SELECT

studentID,

studentName,

CASE

WHEN gender = 'F' THEN 'Female'

WHEN gender = 'M' THEN 'Male'

ELSE 'Not available'

END AS gender_description

FROM students

2)

The following example uses a simple CASE expression to determine a conditional value for the gender_description column:

SELECT

studentID,

studentName,

CASE gender

WHEN 'F' THEN 'Female'

WHEN 'M' THEN 'Male'

ELSE 'Not available'

END AS gender_description

FROM students

3)

The following example uses the NULLIF function to display a null in the rows where the orderTotal column has a value of 0:

SELECT

orderID,

NULLIF( orderTotal, 0 ) AS orderTotal

FROM orders

ORDER BY orderID

4)

The following example uses the COALESCE function to avoid calculations on potential null values:

SELECT

orderID,

COALESCE( orderTotal, 0 ) - COALESCE( amountPaid, 0 ) AS "Amount Due"

FROM orders

ORDER BY orderID

Conformance

SQL:2003 standard

-

Core SQL


Cast Specification

Cast Specification

Specify a data conversion.

Syntax

<cast specification> ::= CAST ( <cast operand> AS <data type> )

<cast operand> ::=

|

<value expression>

NULL

Usage

The CAST function converts data of one type to a different type, subject to the conversion rules below.

Notes

§

Numeric values can be converted to any other numeric data type. If the target type has a smaller scale than the source value, then the fractional component is truncated. An exception is raised if the conversion would lead to a loss of any leading significant digits.

§

Numeric values can be converted to any character string type.

§

Character strings can be converted to other character string types. If the string being converted has more characters than the maximum length of the target type, then the string is truncated to fit the length of the target type.

§

Character strings can be converted to any other data type, with the restriction that the contents of the character string must make sense for the target type. For example, if the target type is DATE, then the character string must mimic the pattern of a DATE literal – four digits, a hyphen, two digits, another hyphen, and two digits.

§

A DATE value can be converted to a character string or a TIMESTAMP with the time part filled in with 00:00:00.

§

A TIME value can be converted to a character string or a TIMESTAMP with the date part filled in with the current date.

§

A TIMESTAMP value can be converted to a character string, a DATE or a TIME.

§

Values of any datetime type can be converted to a numeric type. This functionality is a NexusDB extension.

§

Boolean values can be converted to a character string. The boolean value TRUE is converted to 'True' and FALSE is converted to 'False'.

§

The expression CAST ( NULL AS data-type ) is used to associate a data type with NULL.

Examples

1)

The following example casts a character string value to DOUBLE PRECISION:

CAST( '2.75' AS DOUBLE PRECISION )

2)

The following example casts an integer value to FLOAT:

CAST( 50 AS FLOAT )

3)

The following example casts a character string value to DATE:

CAST( '2004-10-15' AS DATE )

4)

The following example casts the current datetime value to VARCHAR:

CAST( CURRENT_TIMESTAMP AS VARCHAR(19) )

5)

The following example casts the null value to DECIMAL:

CAST ( NULL AS DECIMAL )

Conformance

SQL:2003 standard

NexusDB extensions

-

-

Core SQL

Casting datetime values to a numeric type


Parameters

Parameters

Specify a dynamic value.

Syntax

<dynamic parameter specification> ::= :parameter-name | ?

Usage

Parameters are used to specify dynamic values that are passed to the statement by the client application.

Notes

§

The parameter-name can be any name you choose, but a common (and good) practice is to use descriptive names.

§

The notation of using a question mark to represent parameters is only used with ODBC and ADO.NET data access.

Tip: Preparing statements with parameters will improve the efficiency when the same statement is executed more than once.

Examples

SELECT statement with parameters in the search condition

INSERT statement with parameters in the VALUES clause

UPDATE statement with parameters in the SET clause

DELETE statement with parameters in the WHERE clause

Conformance

SQL:2003 standard

NexusDB extensions

-

-

Core SQL

Named dynamic parameters


SQL Data Types

SQL Data Types

Specify a data type.

Syntax

<data type> ::=

|

|

|

|

|

|

|

|

<character string type> [ <character set clause> ] [ <collate clause> ]

<national character string type> [ <collate clause> ]

<binary large object string type>

<numeric type>

<boolean type>

<datetime type>

GUID

BYTEARRAY ( length )

RECREV

<character set clause> ::=

|

CHARACTER SET <character string literal>

CODEPAGE <unsigned integer>

<collate clause> ::=

|

COLLATE <character string literal> [ <compare flag>... ]

LOCALE <unsigned integer> [ <compare flag>... ]

<compare flag> ::=

|

|

|

|

IGNORE KANA TYPE

IGNORE NONSPACE

IGNORE SYMBOLS

IGNORE WIDTH

USE STRING SORT

Character sets and collations

NexusDB uses the available Windows OS codepages and locales to define the encoding and collation for the character string types.

§

CHARACTER SET and CODEPAGE are used interchangeably to specify the encoding used by the character string type.

§

The national character string type implicitly defines codepage UTF16.

§

If a character set is not specified, then the default codepage associated with the specified collation is implicit.

§

If neither an explicit nor implicit character set is defined, then no encoding takes place.

Tip: You can query the #CODEPAGES system table to get a list of supported character sets.

§

COLLATE and LOCALE are used interchangeably to specify an explicit collation for the character string and national character string types.

§

If a collation is not specified for the character string type, then the collation is byte order.

§

If a collation is not specified for the national character string type, then the collation is locale-neutral.

Tip: You can query the #COLLATIONS system table to get a list of supported collations.

Note: The compare flags are arguments used in standard Windows API calls. Please check your Windows OS documentation for the meaning of these flags.

Result data types of aggregations

The result data type of an aggregation over values of compatible data types, such as CASE expressions and columns in the result of a query expression with UNION, EXCEPT and INTERSECT, is determined as follows:

§

All data types in the set of data types shall be comparable.

§

If any of the data types in the set are character string, then all data types shall be a character string type. The result data type is the character string type with highest precedence:

§

If at least one of the data types in the set is a character large object type, then the result data type is a character large object type. NATIONAL CHARACTER LARGE OBJECT takes precedence over CHARACTER LARGE OBJECT.

§

If at least one of the data types in the set is a variable-length character string, then the result data type is a variable-length character string with maximum length equal to the maximum length of the data types in the set. NATIONAL CHARACTER VARYING takes precedence over CHARACTER VARYING.

§

Otherwise the result is a fixed-length character string with length equal to the maximum length of the data types in the set. NATIONAL CHARACTER takes precedence over CHARACTER.

§

If any of the data types in the set are binary string, then all data types shall be binary string. The result data type is BINARY LARGE OBJECT.

§

If all data types in the set are exact numeric, then the result data type is an exact numeric type with precision not less than the highest precision of the data types in the set and scale equal to the maximum scale of the data types in the set.

§

If any of the data types in the set are approximate numeric, then the result data type is an approximate numeric type with precision not less than the highest precision of the data types in the set.

§

If any of the data types in the set are a datetime type, then all data types in the set shall be a datetime type having the same datetime fields, which is also the data type of the result.

§

If any of the data types in the set are boolean, then all data types shall be boolean. The result data type is BOOLEAN.

Special data types

§

GUID is a NexusDB-specific 16 byte binary string type used to define a Globally Unique Identifier.

The NEWGUID function can be used to generate GUID values.

§

BYTEARRAY is a NexusDB-specific binary string type with a maximum length of 64KB.

§

RECREV is a special NexusDB-specific 32-bit unsigned integer type which is incremented automatically each time the row is updated.

Conformance

SQL:2003 standard

NexusDB extensions

-

-

-

-

-

-

-

-

-

-

Core SQL

Feature F421 "National character"

Feature T041 "Basic LOB data type support"

Feature T031 "BOOLEAN data type"

GUID

BYTEARRAY

RECREV

CODEPAGE

LOCALE

Compare flags in the collate clause

Overview of Predefined Data Types

String Types

Numeric Types

Boolean Types

DateTime Types


Overview of Predefined Data Types

Overview of Predefined Data Types

Predefined data types in NexusDB SQL

SQL type

NexusDB type

Capacity

Conformance

CHARACTER [ ( length ) ] | nxtNullString | 8192 characters | Core SQL
CHAR [ ( length ) ] | nxtNullString | 8192 characters | Core SQL
NULLSTRING [ ( length ) ] | nxtNullString | 8192 characters | NexusDB ext
SHORTSTRING [ ( length ) ] | nxtShortString | 255 characters | NexusDB ext
SINGLECHAR | nxtChar | 8-bit character | NexusDB ext
CHARACTER VARYING [ ( length ) ] | nxtNullString | 8192 characters | Core SQL
CHAR VARYING [ (length) ] | nxtNullString | 8192 characters | Core SQL
VARCHAR [ ( length ) ] | nxtNullString | 8192 characters | Core SQL
CHARACTER LARGE OBJECT [ ( length ) ] | nxtBlobMemo | 4GB | Feature T041
CHAR LARGE OBJECT [ ( length ) ] | nxtBlobMemo | 4GB | Feature T041
CLOB [ ( length ) ] | nxtBlobMemo | 4GB | Feature T041
TEXT [ ( length ) ] | nxtBlobMemo | 4GB | NexusDB ext
NATIONAL CHARACTER [ ( length ) ] | nxtWideString | 32767 characters | Feature F421
NATIONAL CHAR [ ( length ) ] | nxtWideString | 32767 characters | Feature F421
NCHAR [ ( length ) ] | nxtWideString | 32767 characters | Feature F421
NSINGLECHAR | nxtWideChar | 16-bit character | NexusDB ext
NATIONAL CHARACTER VARYING [ ( length ) ] | nxtWideString | 32767 characters | Feature F421
NATIONAL CHAR VARYING [ ( length ) ] | nxtWideString | 32767 characters | Feature F421
NCHAR VARYING [ ( length ) ] | nxtWideString | 32767 characters | Feature F421
NVARCHAR [ ( length ) ] | nxtWideString | 32767 characters | NexusDB ext
NATIONAL CHARACTER LARGE OBJECT [ ( length ) ] | nxtBlobWideMemo | 4GB | Feature T041
NCHAR LARGE OBJECT [ ( length ) ] | nxtBlobWideMemo | 4GB | Feature T041
NCLOB [ ( length ) ] | nxtBlobWideMemo | 4GB | Feature T041
BINARY LARGE OBJECT [ ( length ) ] | nxtBlob | 4GB | Feature T041
BLOB [ ( length ) ] | nxtBlob | 4GB | Feature T041
IMAGE [ ( length ) ] | nxtBlobGraphic | 4GB | NexusDB ext
NUMERIC [ ( precision [ , scale ] ) ] | nxtBCD | Precision 20, scale 4 | Core SQL
DECIMAL [ ( precision [ , scale ] ) ] | nxtBCD | Precision 20, scale 4 | Core SQL
DEC [ ( precision [ , scale ] ) ] | nxtBCD | Precision 20, scale 4 | Core SQL
BCD [ ( precision [ , scale ] ) ] | nxtBCD | Precision 20, scale 4 |
FMTBCD [ ( precision [ , scale ] ) ] | nxtFmtBCD | Precision 20, scale 4 |
BYTE | nxtByte | 8-bit unsigned integer | NexusDB ext
TINYINT | nxtByte | 8-bit unsigned integer | NexusDB ext
SHORTINT | nxtInt8 | 8-bit signed integer | NexusDB ext
SMALLINT | nxtInt16 | 16-bit signed integer | Core SQL
INTEGER | nxtInt32 | 32-bit signed integer | Core SQL
INT | nxtInt32 | 32-bit signed integer | Core SQL
AUTOINC [ ( start-value [ , increment-value ] ) ] | nxtAutoInc | 32-bit unsigned integer | NexusDB ext
BIGINT | nxtInt64 | 64-bit signed integer | Feature T071
LARGEINT | nxtInt64 | 64-bit signed integer | NexusDB ext
WORD | nxtWord16 | 16-bit unsigned integer | NexusDB ext
DWORD | nxtWord32 | 32-bit unsigned integer | NexusDB ext
FLOAT [ ( precision ) ] | nxtSingle | 1.5E-45 .. 3.4E38 | Core SQL
REAL | nxtDouble | 5.0E-324 .. 1.7E308 | Core SQL
DOUBLE PRECISION | nxtDouble | 5.0E-324 .. 1.7E308 | Core SQL
EXTENDED | nxtExtended | 3.6E-4951 .. 1.1E4932 | NexusDB ext
MONEY | nxtCurrency | Precision 20, scale 4 | NexusDB ext
BOOLEAN | nxtBoolean | 8-bit boolean flag | Feature T031
BOOL | nxtBoolean | 8-bit boolean flag | NexusDB ext
DATE | nxtDate | 4 bytes | Core SQL
TIME | nxtTime | 4 bytes | Core SQL
TIMESTAMP | nxtDateTime | 8 bytes | Core SQL
DATETIME | nxtDateTime | 8 bytes | NexusDB ext
GUID | nxtGuid | 16 bytes | NexusDB ext
BYTEARRAY ( length ) | nxtByteArray | 64KB | NexusDB ext
RECREV | nxtRecRev | 32-bit unsigned integer | NexusDB ext


String Types

String Types

Syntax

<character string type> ::=

|

|

|

|

|

|

|

|

CHARACTER [ ( length ) ]

CHAR [ ( length ) ]

NULLSTRING [ ( length ) ]

SHORTSTRING [ ( length ) ]

SINGLECHAR

CHARACTER VARYING [ ( length ) ]

CHAR VARYING [ ( length ) ]

VARCHAR [ ( length ) ]

<character large object type>

<character large object type> ::=

|

|

|

CHARACTER LARGE OBJECT [ ( length ) ]

CHAR LARGE OBJECT [ ( length ) ]

CLOB [ ( length ) ]

TEXT [ ( length ) ]

<national character string type> ::=

|

|

|

|

|

|

|

|

NATIONAL CHARACTER [ ( length ) ]

NATIONAL CHAR [ ( length ) ]

NCHAR [ ( length ) ]

NSINGLECHAR

NATIONAL CHARACTER VARYING [ ( length ) ]

NATIONAL CHAR VARYING [ ( length ) ]

NCHAR VARYING [ ( length ) ]

NVARCHAR [ ( length ) ]

<national character large object type>

<national character large object type> ::=

|

|

NATIONAL CHARACTER LARGE OBJECT [ ( length ) ]

NCHAR LARGE OBJECT [ ( length ) ]

NCLOB [ ( length ) ]

<binary large object string type> ::=

|

|

BINARY LARGE OBJECT [ ( length ) ]

BLOB [ ( length ) ]

IMAGE [ ( length ) ]

Notes

§

NexusDB stores string data either in fixed or variable length, depending on the STORAGE attribute in the table definition.

§

If the length argument is omitted, then a length of 1 is implicit.

§

The large object types have a dynamic length in NexusDB, up to the maximum capacity. Specifying the length argument has no effect, and is only included for syntax compatibility with SQL:2003.

§

The character string type defines a single-byte ANSI string.

§

CHARACTER is a fixed-length character string type with a maximum length of 8192 characters. Values are padded with trailing spaces on data retrieval to fill the fixed length.

§

CHAR is equivalent to CHARACTER.

§

CHARACTER VARYING is a variable-length character string type with the same maximum length as CHARACTER.

§

CHAR VARYING and VARCHAR are equivalent to CHARACTER VARYING.

§

NULLSTRING is a NexusDB synonym for CHARACTER VARYING.

§

SHORTSTRING is a NexusDB-specific variable-length character string type with a maximum length of 255 characters.

§

SINGLECHAR is a NexusDB-specific type that defines a single 8-bit character.

§

CHARACTER LARGE OBJECT is a large object character string type with a maximum capacity of 4GB.

§

CHAR LARGE OBJECT and CLOB are equivalent to CHARACTER LARGE OBJECT.

§

TEXT is a NexusDB synonym for CHARACTER LARGE OBJECT.

§

The national character string type defines a double-byte Unicode string with implicit character set UTF16.

§

NATIONAL CHARACTER is a fixed-length character string type with a maximum capacity of 32767 characters. Values are padded with trailing spaces on data retrieval to fill the fixed length.

§

NATIONAL CHAR and NCHAR are equivalent to NATIONAL CHARACTER.

§

NSINGLECHAR is a NexusDB-specific type that defines a single 16-bit character.

§

NATIONAL CHARACTER VARYING is a variable-length character string type with the same maximum length as NATIONAL CHARACTER.

§

NATIONAL CHAR VARYING and NCHAR VARYING are equivalent to NATIONAL CHARACTER VARYING.

§

NVARCHAR is a NexusDB synonym for NATIONAL CHARACTER VARYING.

§

NATIONAL CHARACTER LARGE OBJECT is a large object character string type with a maximum capacity of 4GB.

§

NCHAR LARGE OBJECT and NCLOB are equivalent to NATIONAL CHARACTER LARGE OBJECT.

§

BINARY LARGE OBJECT is a large object binary string type with a maximum capacity of 4GB.

§

BLOB is equivalent to BINARY LARGE OBJECT.

§

IMAGE is a NexusDB-specific large object binary string type with a maximum capacity of 4GB.

§

Two character strings are comparable only if they use the same collation and compare flags.

§

A character string is assignable to any character string site, including sites having a different character set, provided that data loss does not occur during the translation. An exception is raised if an assignment leads to truncation of the character string data.

§

A binary string is only assignable to a binary string type.

Conformance

SQL:2003 standard

NexusDB extensions

-

-

-

-

-

-

-

-

-

-

Core SQL

Feature F421 "National character"

Feature T041 "Basic LOB data type support"

NULLSTRING

SHORTSTRING

SINGLECHAR

TEXT

NSINGLECHAR

NVARCHAR

IMAGE


Numeric Types

Numeric Types

Syntax

<numeric type> ::=

|

<exact numeric type>

<approximate numeric type>

<exact numeric type> ::=

|

|

|

|

|

|

|

|

|

|

|

|

|

NUMERIC [ ( precision [ , scale ] ) ]

DECIMAL [ ( precision [ , scale ] ) ]

DEC [ ( precision [ , scale ] ) ]

BYTE

TINYINT

SHORTINT

SMALLINT

INTEGER

INT

AUTOINC [ ( start-value [ , increment-value ] ) ]

BIGINT

LARGEINT

WORD

DWORD

<approximate numeric type> ::=

|

|

|

|

|

|

FLOAT [ ( precision ) ]

REAL

DOUBLE PRECISION

EXTENDED

MONEY

BCD

FMTBCD

Notes

§

The exact numeric types can represent a numeric value exactly. The unsigned and signed integer types have an implicit scale of 0, while the NUMERIC and DECIMAL types have both a precision (the number of significant digits) and a scale (the fractional component).

§

DECIMAL is a BCD type with a maximum precision of 16-20, depending on the scale, and a maximum scale of 4. A scale of 0 is implicit if the scale argument is omitted, and a precision of 16 is implicit if also the precision argument is omitted.

§

NUMERIC is equivalent to DECIMAL.

§

BYTE and TINYINT are equivalent NexusDB-specific 8-bit unsigned integer types with range 0 .. 255.

§

SHORTINT is a NexusDB-specific 8-bit signed integer type with range -128..127.

§

SMALLINT is a 16-bit signed integer type with range -32768..32767.

§

INTEGER is a 32-bit signed integer type with range -2147483648..2147483647.

§

INT is equivalent to INTEGER.

§

AUTOINC is a NexusDB-specific 32-bit unsigned integer type with range 0..4294967295, suitable for identity columns. A start value of 1 is implicit if start-value is not specified. An increment value of 1 is implicit if increment-value is not specified.

§

BIGINT is a 64-bit integer type with range -2E63..2E63-1.

§

LARGEINT is a NexusDB synonym for BIGINT.

§

WORD is a NexusDB-specific unsigned 16-bit integer type with range 0..65535.

§

DWORD is a NexusDB-specific unsigned 32-bit integer type with range 0..4294967295

§

The approximate numeric types are floating point values that can only represent numbers approximately.

§

FLOAT is a 4 byte floating point type with range -1.5E-45..3.4E38. The option of specifying the precision is included for syntax compatibility with SQL:2003, but does not have any affect on the actual precision.

§

DOUBLE PRECISION is an 8 byte floating point type with range 5.0E-324..1.7E308.

§

REAL is equivalent to DOUBLE PRECISION.

§

EXTENDED is a NexusDB-specific 10 byte floating point type with range 3.6E-4951..1.1E4932.

§

MONEY is a NexusDB-specific type that is stored as a 64-bit scaled integer with the last 4 digits representing the fractional component.

§

Any two numbers are comparable.

§

A number is assignable only to numeric sites. An exception is raised if an assignment of a numeric value would lead to a loss of its most significant digit. When a numeric value is assigned to a site of exact numeric type, the value is converted to have the precision and scale of the target, with truncation of the fractional component if the scale is reduced.

Conformance

SQL:2003 standard

NexusDB extensions

-

-

-

-

-

-

-

-

-

-

-

-

-

Core SQL

Feature T071 "BIGINT data type"

BYTE

TINYINT

SHORTINT

AUTOINC

LARGEINT

WORD

DWORD

EXTENDED

MONEY

BCD

FMTBCD


Boolean Types

Boolean Types

Syntax

<boolean type> :=

|

BOOLEAN

BOOL

Notes

§

The BOOLEAN data type can have one of three different boolean literal values:

TRUE

FALSE

UNKNOWN

§

BOOL is a NexusDB synonym for BOOLEAN.

§

Two boolean values are comparable.

§

A boolean value is assignable only to a boolean site.

Conformance

SQL:2003 standard

NexusDB extensions

-

-

Feature T031 "BOOLEAN data type"

BOOL


DateTime Types

DateTime Types

Syntax

<datetime type> ::=

|

|

|

DATE

TIME

TIMESTAMP

DATETIME

Notes

§

DATE is a 4 byte type that contains the datetime fields YEAR, MONTH and DAY.

§

TIME is a 4 byte type that contains the datetime fields HOUR, MINUTE and SECOND.

§

TIMESTAMP is a 8 byte type that contains the datetime fields YEAR, MONTH, DAY, HOUR, MINUTE and SECOND.

§

DATETIME is a NexusDB synonym for TIMESTAMP.

§

The time precision of the datetime types is milliseconds, 3 digits following the decimal point in the SECOND datetime field.

§

Two date time values are comparable only if both have the same datetime fields.

§

A datetime value is assignable only to a datetime site having the same datetime fields.

Conformance

SQL:2003 standard

NexusDB extensions

-

-

Core SQL

DATETIME


Functions

Functions

This section describes the aggregate functions and built-in SQL functions available in NexusDB SQL.

Aggregate Functions

Numeric Value Functions

String Value Functions

DateTime Value Functions

System Functions


Aggregate Functions

Aggregate Functions

Specify a value computed from a group of rows.

Syntax

<aggregate function> ::=

|

COUNT ( * )

<general set function>

<general set function> ::=

<set function type> ( [ DISTINCT | ALL ] <value expression> )

<set function type> ::=

|

|

|

|

|

|

|

AVG

MAX

MIN

SUM

COUNT

MED

STD

LIST

Usage

The aggregate functions, also referred to as set functions, perform operations that aggregate data from groups of rows.

Notes

§

ALL is implicit if no set quantifier is not specified.

§

If DISTINCT is specified, then all duplicate values are removed from the set.

§

If the query specifies a GROUP BY clause, then the aggregate functions are applied to each group, else the entire table is treated as a single group.

§

If a general set function is specified, other than the COUNT function, and the set is empty, then the result is null.

COUNT function

§

If COUNT( * ) is specified, then the result is the cardinality of the set, else the COUNT function returns the number of non-null values in the set.

§

The data type of the result is BIGINT.

MAX function

§

The MAX function returns the highest non-null value in the set.

§

The data type of the result is the same type as the argument.

MIN function

§

The MIN function returns the lowest non-null value in the set.

§

The data type of the result is the same type as the argument.

AVG function

§

The AVG function calculates the average of non-null values in the set.

§

The argument shall be a numeric type.

§

The data type of the result is the same type as the argument.

SUM function

§

The SUM function calculates the sum of non-null values in the set.

§

The argument shall be a numeric type.

§

If the argument is an integer type, then the data type of the result is BIGINT.

§

If the argument is a BCD type, then the data type of the result is DECIMAL with maximum possible precision and the same scale as the argument.

§

If the argument is an approximate numeric type, then the data type of the result is DOUBLE PRECISION or EXTENDED.

MED function

§

The MED function calculates the median value of the non-null values in the set.

§

The argument shall be a numeric type.

§

The data type of the result is the same type as the argument.

STD function

§

The STD function calculates the standard deviation of the non-null values in the set.

§

The argument shall be a numeric type.

§

The data type of the result is the same type as the argument.

LIST function

§

LIST is a special NexusDB set function that compiles a comma-separated list of non-null values in the set.

§

The argument shall be a character string type.

§

The data type of the result is CHAR(4096).

Examples

1)

The following example calculates the number of rows in the students table:

SELECT COUNT( * ) FROM students

2)

The following example returns the number of female students:

SELECT COUNT( studentName )

FROM students

WHERE gender = 'F'

3)

The following example calculates the average grade for each student:

SELECT studentID, AVG( grade )

FROM enrolls

GROUP BY studentID

4)

The following example selects the highest and lowest salaries of teachers:

SELECT MAX( salary ), MIN( salary )

FROM teachers

5)

The following example calculates the total cost of each order made:

SELECT orderID, SUM( extPrice )

FROM order_details

GROUP BY orderID

6)

The following example retrieves a list of student names grouped by gender:

SELECT gender, LIST( studentName )

FROM students

GROUP BY gender

Conformance

SQL:2003 standard

NexusDB extensions

-

-

-

-

Core SQL

MED function

STD function

LIST function


Numeric Value Functions

Numeric Value Functions

Specify a function that returns a numeric value.

Syntax

<numeric value function> ::=

|

|

|

|

|

|

|

|

|

|

|

|

|

|

|

|

|

|

<position expression>

<extract expression>

<length expression>

<absolute value expression>

<modulus expression>

<natural logarithm>

<exponential function>

<power function>

<square root>

<floor function>

<ceiling function>

<arctangent function>

<cosine function>

<sine function>

<ordinal function>

<pi function>

<round function>

<random function>

<lastautoinc function>

Usage

Numeric value functions can be used in SQL everywhere a numeric value is permitted.

Conformance

SQL:2003 standard

NexusDB extensions

-

-

-

-

-

-

-

-

-

-

-

-

-

-

Core SQL

Feature F052 "Intervals and datetime arithmetic"

Feature T441 "ABS and MOD functions"

Feature T621 "Enhanced numeric functions"

ATAN function

ATAN2 function

COS function

SIN function

ORD function

PI function

ROUND function

BROUND function

RAND function

LASTAUTOINC function


Position Expression

Position Expression

Syntax

<position expression> ::=

|

<string position expression>

<blob position expression>

<string position expression> ::=

POSITION ( <string value expression> IN <string value expression>

[ USING <char length units> ] )

<blob position expression> ::=

POSITION ( <blob value expression> IN <blob value expression> )

<char length units> ::=

|

CHARACTERS

OCTETS

Notes

§

The POSITION function returns the ordinal position of the first occurrence of the first string expression within the second string expression.

§

The USING clause shall only be specified if the IN expression is a UNICODE character string. CHARACTERS, which is implicit if the clause is omitted, returns the ordinal character position, while OCTETS returns the ordinal byte position.

§

The POSITION function returns the ordinal byte position when the IN expression is a binary string.

§

If the substring is not found within the second string, then 0 is returned.

§

The data type of the result is INTEGER.

§

If either of the arguments are null, then the result is null.

Examples

1) The following example lists all courses starting with 'C':

SELECT *

FROM courses

WHERE POSITION( 'C' IN courseName ) = 1

2)

In the following example, the POSITION function returns 3 with the CHARACTERS char length unit implicitly specified:

SELECT POSITION( 'a' IN N'Grace' )

FROM #dummy

3)

In the following example, the POSITION function returns 6 with the OCTETS char length unit explicitly specified:

SELECT POSITION( 'a' IN N'Grace' USING OCTETS )

FROM #dummy

Conformance

SQL:2003 standard

-

Core SQL


Extract Expression

Extract Expression

Syntax

<extract expression> ::=

EXTRACT ( <extract field> FROM <extract source> )

<extract field> ::= <primary datetime field>

<extract source> ::= <datetime value expression>

Notes

§

The EXTRACT function returns the datetime field specified by <extract field> from the <extract source>.

§

If SECOND is specified as extract field, then the data type of the result is DOUBLE PRECISION, else the data type of the result is INTEGER.

§

If either of the arguments are null, then the result is null.

Examples

1) The following example extracts the YEAR, MONTH and DAY fields from the orderDate column:

SELECT

orderID,

EXTRACT( YEAR FROM orderDate ) AS "Year",

EXTRACT( MONTH from orderDate ) AS "Month",

EXTRACT( DAY FROM orderDate ) AS "Day",

orderTotal

FROM orders

2) The following example groups order totals by year:

SELECT

"Year",

SUM( orderTotal ) AS "Total Amount"

FROM

(

SELECT

EXTRACT( YEAR FROM orderDate ) AS "Year",

orderTotal

FROM orders

) AS orders

GROUP BY "Year"

Conformance

SQL:2003 standard

-

Feature F052 "Intervals and datetime arithmetic"


Length Expression

Length Expression

Syntax

<length expression> ::=

|

<char length expression>

<octet length expression>

<char length expression> ::=

{ CHAR_LENGTH | CHARACTER_LENGTH } ( <string value expression>

[ USING <char length units> ] )

<octet length expression> ::=

OCTET_LENGTH ( <string value expression> )

Notes

§

CHAR_LENGTH and CHARACTER_LENGTH are equivalent functions that return the number of characters or bytes in a character string according to the implicit or explicit char length unit, or the number of bytes in a binary string.

§

The USING clause shall only be specified if the argument is a UNICODE character string. CHARACTERS is implicit if a char length unit is not specified.

§

The OCTET_LENGTH function returns the number of octets (bytes) in a character or binary string.

§

The data type of the result is INTEGER.

§

If the argument is null, then the result is null.

Examples

1)

The following example selects students who have a longer last name than first name:

SELECT firstName, lastName

FROM students

WHERE CHAR_LENGTH( lastName ) > CHARACTER_LENGTH( firstName )

2)

In the following example, the CHAR_LENGTH function returns 5 with the CHARACTERS char length unit implicitly specified:

SELECT CHAR_LENGTH( N'Grace' )

FROM #dummy

3)

In the following example, the CHAR_LENGTH function returns 10 with the OCTETS char length unit explicitly specified:

SELECT CHAR_LENGTH( N'Grace' USING OCTETS )

FROM #dummy

Conformance

SQL:2003 standard

-

Core SQL


Absolute Value Expression

Absolute Value Expression

Syntax

<absolute value expression> ::=

ABS ( <numeric value expression> )

Notes

§

The ABS function returns the unsigned value of the argument.

§

The data type of the result is the same type as the argument.

§

If the argument is null, then the result is null.

Examples

Expression | Result
ABS( 5 ) | 5
ABS( -5.25 ) | 5.25

Conformance

SQL:2003 standard

-

Feature T441 "ABS and MOD functions"


Modulus Expression

Modulus Expression

Syntax

<modulus expression> ::=

MOD ( <numeric value expression dividend> , <numeric value expression divisor> )

<numeric value expression dividend> ::= <numeric value expression>

<numeric value expression divisor> ::= <numeric value expression>

Notes

§

The MOD function returns the remainder of the first argument divided by the second argument.

§

Both dividend and divisor shall be whole numbers (scale 0).

§

The divisor shall not be 0.

§

The data type of the result is the same type as the second argument.

§

If either of the arguments are null, then the result is null.

Examples

Expression | Result
MOD( 7, 2 ) | 1
MOD( 11, 4.0 ) | 3.0

Conformance

SQL:2003 standard

-

Feature T441 "ABS and MOD functions"


Natural Logarithm

Natural Logarithm

Syntax

<natural logarithm> ::=

LN ( <numeric value expression> )

Notes

§

The LN function returns the natural logarithm of the argument.

§

The argument shall be greater than 0.

§

The data type of the result is DOUBLE PRECISION.

§

If the argument is null, then the result is null.

Examples

Expression | Result
LN( 2 ) | 0.693147..
LN( 7.5 ) | 2.014903..

Conformance

SQL:2003 standard

-

Feature T621 "Enhanced numeric functions"


Exponential Function

Exponential Function

Syntax

<exponential function> ::=

EXP ( <numeric value expression> )

Notes

§

The result of the EXP function is e (the base of natural logarithms) raised to the power of the argument.

§

The data type of the result is DOUBLE PRECISION.

§

If the argument is null, then the result is null.

Examples

Expression | Result
EXP( 2 ) | 7.389056..
EXP( -2 ) | 0.135335..

Conformance

SQL:2003 standard

-

Feature T621 "Enhanced numeric functions"


Power Function

Power Function

Syntax

<power function> ::=

POWER ( <numeric value expression> , <numeric value expression> )

Notes

§

The POWER function returns the value of the first argument raised to the power of the second argument.

§

If both arguments are 0, then the result is 1.

§

If the first argument is 0 and the second argument is positive, then the result is 0.

§

If the first argument is negative, then the second argument shall be an exact numeric value with scale 0.

§

The data type of the result is DOUBLE PRECISION.

§

If either of the arguments are null, then the result is null.

Examples

Expression | Result
POWER( 0, 0 ) | 1
POWER( 0, 3 ) | 0
POWER( -2, 3 ) | -8
POWER( 2, 3 ) | 8

Conformance

SQL:2003 standard

-

Feature T621 "Enhanced numeric functions"


Square Root Function

Square Root Function

Syntax

<square root> ::=

SQRT ( <numeric value expression> )

Notes

§

The SQRT function returns the square root of the argument.

§

The data type of the result is DOUBLE PRECISION.

§

If the argument is null, then the result is null.

Examples

Expression | Result
SQRT( 4 ) | 2
SQRT( 5 ) | 2.236068..

Conformance

SQL:2003 standard

-

Feature T621 "Enhanced numeric functions"


Floor Function

Floor Function

Syntax

<floor function> ::=

FLOOR ( <numeric value expression> )

Notes

§

The FLOOR function returns the greatest whole number (scale 0) that is less than or equal to the argument.

§

The data type of the result is the same type as the argument.

§

If the argument is null, then the result is null.

Examples

Expression | Result
FLOOR( 5 ) | 5
FLOOR( 5.25 ) | 5
FLOOR( -5.25 ) | -6

Conformance

SQL:2003 standard

-

Feature T621 "Enhanced numeric functions"


Ceiling Function

Ceiling Function

Syntax

<ceiling function> ::=

{ CEIL | CEILING } ( <numeric value expression> )

Notes

§

The CEILING function returns the smallest whole number (scale 0) that is greater than or equal to the argument.

§

The data type of the result is the same type as the argument.

§

If the argument is null, then the result is null.

Examples

Expression | Result
CEILING( 5 ) | 5
CEILING( 5.25 ) | 6
CEILING( -5.25 ) | -5

Conformance

SQL:2003 standard

-

Feature T621 "Enhanced numeric functions"


Arctangent Function

Arctangent Function

Syntax

<arctangent function> ::=

|

ATAN ( <numeric value expression> )

{ ATN2 | ATAN2 } ( <numeric value expression> , <numeric value expression> )

Notes

§

The ATAN function returns the angle in radians, whose tangent is the value of the argument.

§

The ATAN2 function returns the angle in radians, whose tangent is between the values of the two arguments.

§

The data type of the result is DOUBLE PRECISION.

§

If either of the arguments are null, then the result is null.

Examples

Expression | Result
ATAN( 2.75 ) | 1.222025..
ATAN2( 2.75, 7.3525 ) | 0.357913..

Conformance

NexusDB extensions

-

-

ATAN function

ATAN2 function


Cosine Function

Cosine Function

Syntax

<cosine function> ::=

COS ( <numeric value expression> )

Notes

§

The COS function returns the trigonometric cosine of the argument (in radians).

§

The data type of the result is DOUBLE PRECISION.

§

If the argument is null, then the result is null.

Examples

Expression | Result
COS( 5.25 ) | 0.512085..
COS( -2 ) | -0.416147..

Conformance

NexusDB extensions

-

COS function


Sine Function

Sine Function

Syntax

<sine function> ::=

SIN ( <numeric value expression> )

Notes

§

The SIN function returns the trigonometric sine of the argument (in radians).

§

The data type of the result is DOUBLE PRECISION.

§

If the argument is null, then the result is null.

Examples

Expression | Result
SIN( 5.25 ) | -0.858935..
SIN( -2 ) | -0.909297..

Conformance

NexusDB extensions

-

SIN function


Ordinal Function

Ordinal Function

Syntax

<ordinal function> ::=

ORD ( <character value expression> )

Notes

§

The ORD function returns the ordinal value of the character passed by the argument.

§

The data type of the result is INTEGER.

§

If the argument is null, then the result is null.

Examples

Expression | Result
ORD('A') | 65
ORD('a') | 97
ORD('5') | 53

Conformance

NexusDB extensions

-

ORD function


PI Function

PI Function

Syntax

<pi function> ::= PI

Notes

§

The PI function returns the numeric value pi (3.141592..).

§

The data type of the result is DOUBLE PRECISION.

Conformance

NexusDB extensions

-

PI function


Round Function

Round Function

Syntax

<round function> ::=

|

ROUND ( <numeric value expression> )

BROUND ( <numeric value expression> )

Notes

§

The ROUND function returns the value of the argument rounded to the closest whole number (scale 0).

§

The BROUND function uses the "Banker's Rounding" algorithm to calculate the result.

§

The data type of the result is the same type as the argument.

§

If the argument is null, then the result is null.

Examples

Expression | Result
ROUND( 6.5 ) | 7.0
BROUND( 6.5 ) | 6.0
ROUND( 7.5 ) | 8.0
BROUND( 7.5 ) | 8.0

Conformance

NexusDB extensions

-

-

ROUND function

BROUND function


Random Function

Random Function

Syntax

<random function> ::= RAND

Notes

§

The RAND function returns a random floating point value between 0 and 1.

§

The data type of the result is DOUBLE PRECISION.

Conformance

NexusDB extensions

-

RAND function


LastAutoinc Function

LastAutoinc Function

Syntax

<lastautoinc function> ::=

|

LASTAUTOINC

IDENTITY

Notes

§

The LASTAUTOINC function returns the most recent AutoInc value generated by the current SQL session.

§

The data type of the result is DWORD.

§

If no AutoInc value has been generated during the current SQL session, then the result is null.

§

The LASTAUTOINC function should always be executed inside a transaction when the result is used for assignments.

§

The IDENTITY keyword, which is a synonym for the LASTAUTOINC function, is deprecated in V2 and may be deleted in a future version of NexusDB SQL.

Examples

1)

Assuming master.masterID is an AUTOINC column, the following example first inserts a new row into master, which causes the AUTOINC column to be incremented, and then inserts a new row into the detail table using the LASTAUTOINC function to assign the last generated AutoInc value to the detail.masterID column:

START TRANSACTION;

INSERT INTO master ( masterID ) VALUES ( 100 );

INSERT INTO detail ( masterID, detailID ) VALUES ( LASTAUTOINC, 1 );

COMMIT;

Conformance

NexusDB extensions

-

LASTAUTOINC function


String Value Functions

String Value Functions

Specify a function that returns a character string value.

Syntax

<string value function> ::=

<character value function>

<character value function> ::=

|

|

|

|

|

|

<character substring function>

<fold>

<trim function>

<char function>

<tostring function>

<tostringlen function>

OVERLAY Function

Usage

String value functions can be used in SQL everywhere a character string value is permitted.

Conformance

SQL:2003 standard

NexusDB extensions

-

-

-

-

Core SQL

CHR function

TOSTRING function

TOSTRINGLEN function


Substring Function

Substring Function

Syntax

<character substring function> ::=

SUBSTRING ( <character value expression>

FROM [ LEFT | RIGHT ] <integer expression>

[ { FOR | TO } [ LEFT | RIGHT ] <integer expression> ]

[ USING { CHARACTERS | OCTETS } ]

)

Alternative syntax supporting regular expression matching:

SUBSTRING ( <character value expression>

SIMILAR [ TO ] <character value expression> [ "IGNORE" "CASE" ]

[ "GROUP" <character value or integer expression> ]

)

Notes

§

The SUBSTRING function returns a subset of characters from the source string.

§

The start-position argument specifies the ordinal position within the source string where the substring starts.

§

The string-length argument specifies how many characters or octets shall be copied from the source string.

§

If no direction is specified for FROM, the LEFT is the default.

If no direction is specified for FOR | TO then the direction from FROM is the default.

FROM and TO are absolute positions, counting from the start (LEFT) or end (RIGHT) of the string. With 1 being the first or last character in the string. (Keeping in mind that for CHAR instead of VARCHAR the string always has the full length and is padded with spaces at the end).

FOR counts from the FROM position either forward (LEFT) or backward (RIGHT) to determine the absolute position. If backward, this effectively calculates the start position and the value from FROM becomes the end position.

If the calculated absolute start or end position is beyond the start or end of the string, it is clamped to fall within the string.

The string is always copied left-to-right from the absolute start to the absolute end position. (So if the end position is before the start position, an empty string is returned).

§

If CHARACTERS is specified, or the USING clause is omitted, then start-position is the ordinal character position within the source string. If OCTETS is specified, then start-position is the byte position.

§

If the declared type of the character value expression is a fixed-length or variable-length character string type, then the data type of the result is CHARACTER VARYING or NATIONAL CHARACTER VARYING with a length equal to the fixed length or maximum variable length of the character value expression, otherwise the data type of the result is CHARACTER LARGE OBJECT or NATIONAL CHARACTER LARGE OBJECT.

§

When the alternative SIMILAR [TO] regular expression syntax is used, the regular expressions are evaluated using the PCRE library.

NOTE! The required library to support regular expressions does not exist in Rad Studio/Delphi versions before the XE version. An exception will be raised when using the SIMILAR [TO] syntax If the SQL engine is compiled with a pre-XE version. The pre-compiled NexusDB executables fully support regular expressions.

A quick overview of the supported syntax can be found at

https://www.pcre.org/original/doc/html/pcresyntax.html

The full specification of valid patterns can be found at

https://www.pcre.org/original/doc/html/pcrepattern.html

The optional GROUP parameter can be either an integer or a string to

extract only the value of a numbered subpattern or named subpattern as

described in the "SUBPATTERNS" and "NAMED SUBPATTERNS" section of the full

pattern specification.

See below for an example of what is now possible.

§

If either of the arguments are null, then the result is null.

Examples

Expression | Result
SUBSTRING( 'Hello World' FROM 1 ) | 'Hello World'
SUBSTRING( 'Hello World' FROM 1 FOR 5 ) | 'Hello'
SUBSTRING( 'Hello World' FROM 7 ) | 'World'
SUBSTRING( 'Hello World' FROM 7 FOR 5 ) | 'World'
SUBSTRING('this is a test' FROM /*LEFT*/ 5) | is a test
SUBSTRING('this is a test' FROM /*LEFT*/ 1 FOR /*LEFT*/ 4) | this
SUBSTRING('this is a test' FROM LEFT 1 FOR /*LEFT*/ 4) | this
SUBSTRING('this is a test' FROM LEFT 4 FOR RIGHT 2) | is
SUBSTRING('this is a test' FROM RIGHT 1 FOR /*RIGHT*/ 4) | test
SUBSTRING('this is a test' FROM RIGHT 4 FOR LEFT 4) | test
SUBSTRING('this is a test' FROM RIGHT 5) | this is a
SUBSTRING('this is a test' FROM /*LEFT*/ 2 TO /*LEFT*/ 3) | hi
SUBSTRING('this is a test' FROM LEFT 2 TO LEFT 3) | hi
SUBSTRING('this is a test' FROM RIGHT 3 TO /*RIGHT*/ 2) | es
SUBSTRING('this is a test' FROM RIGHT 10 TO LEFT 10) | " is a "

Regular expression style:

DECLARE s NVARCHAR(53);

SET S = 'the blue king and the White king and the white queen';

SELECT

SUBSTRING(s SIMILAR TO 'the ((red|white) (king|queen))') AS "FULL",

SUBSTRING(s SIMILAR TO 'the ((red|white) (king|queen))' GROUP 0) AS

"GROUP_0", -- same as not specifying GROUP

SUBSTRING(s SIMILAR TO 'the ((red|white) (king|queen))' GROUP 1) AS

"GROUP_1",

SUBSTRING(s SIMILAR TO 'the ((red|white) (king|queen))' GROUP 2) AS

"GROUP_2",

SUBSTRING(s SIMILAR TO 'the ((red|white) (king|queen))' GROUP 3) AS

"GROUP_3",

SUBSTRING(s SIMILAR TO 'the ((?<color>red|white) (?<gender>king|queen))'

GROUP 'color') AS "GROUP_COLOR",

SUBSTRING(s SIMILAR TO 'the ((?<color>red|white) (?<gender>king|queen))'

GROUP 'gender') AS "GROUP_GENDER",

SUBSTRING(s SIMILAR TO 'the (red|white) (?<gender>king|queen)' IGNORE

CASE GROUP 'gender') AS "GROUP_GENDER_IGNORECASE"

FROM

#dummy;

which will return the following values:

FULL: the white queen

GROUP_0: the white queen

GROUP_1: white queen

GROUP_2: white

GROUP_3: queen

GROUP_COLOR: white

GROUP_GENDER: queen

GROUP_GENDER_IGNORECASE: king

Conformance

SQL:2003 standard

-

Core SQL


OVERLAY Function

OVERLAY function

The syntax has been extended from what the standard allows to allow the same target specification as the extended SUBSTRING function (see that function for details).

Syntax

OVERLAY (

<character value expression> PLACING <character value expression>

{ FROM [ LEFT | RIGHT ] <integer expression>

[ { FOR | TO } [ LEFT | RIGHT ] <integer expression> ]

[ USING { CHARACTERS | OCTETS } ]

| SIMILAR [ TO ] <character value expression> [ "IGNORE" "CASE" ]

[ "GROUP" <character value or integer expression> ]

}

)

Furthermore, OVERLAY has been extended to allow operating on DATE, TIME, and TIMESTAMP values.

In this case the syntax is:

OVERLAY (

<date, time, or timestamp value expression> PLACING <integer or float value expression>

IN {YEAR | MONTH | DAY | HOUR | MINUTE | SECOND }

Usage

The function allows to replace parts of a string with another string directly in place.

Here is an example of what is now possible:

DECLARE s NVARCHAR(70);

SET S = 'the blue king and the White king and the white queen';

SELECT

OVERLAY(s PLACING 'the joker' SIMILAR TO 'the ((red|white) (king|queen))' IGNORE CASE),

OVERLAY(s PLACING ' - gone - ' FROM LEFT 5 TO RIGHT 5),

OVERLAY(s PLACING ' - gone - ' FROM 5)

FROM

#dummy;

Resulting in the following 3 string:

the blue king and the joker and the white queen

the - gone - ueen

the - gone - and the White king and the white queen


Fold Function

Fold Function

Syntax

<fold> ::=

{ UPPER | LOWER } ( <character value expression> )

Notes

§

The UPPER function converts the source string to upper case.

§

The LOWER function converts the source string to lower case.

§

The data type of the result is the declared type of the argument.

§

If the argument is null, then the result is null.

Examples

Expression | Result
UPPER( 'Hello World' ) | 'HELLO WORLD'
LOWER( 'Hello World' ) | 'hello world'

Conformance

SQL:2003 standard

-

Core SQL


Trim Function

Trim Function

Syntax

<trim function> ::=

TRIM ( [ [ <trim specification> ] [ <trim character> ] FROM ] <trim source>

<trim specification> ::=

|

|

LEADING

TRAILING

BOTH

<trim character> ::= <character value expression>

<trim source> ::= <character value expression>

Notes

§

The TRIM function removes leading and/or trailing characters from the source string.

§

If no trim specification is specified, then BOTH is implicit.

§

If no trim character is specified, then space is implicit.

§

If the declared type of the trim source is a fixed-length or variable-length character string type, then the data type of the result is CHARACTER VARYING or NATIONAL CHARACTER VARYING with a length equal to the fixed length or maximum variable length of the trim source, otherwise the data type of the result is CHARACTER LARGE OBJECT or NATIONAL CHARACTER LARGE OBJECT.

§

If either of the arguments are null, then the result is null.

Examples

Expression | Result
TRIM( ' Meet the Dream Team ' ) | 'Meet the Dream Team'
TRIM( BOTH FROM ' Meet the Dream Team ' ) | 'Meet the Dream Team'
TRIM( LEADING 'M' FROM 'Meet the Dream TeaM' ) | 'eet the Dream TeaM'
TRIM( TRAILING 'M' FROM 'Meet the Dream TeaM' ) | 'Meet the Dream Tea'
TRIM( BOTH 'M' FROM 'Meet the Dream TeaM' ) | 'eet the Dream Tea'

Conformance

SQL:2003 standard

-

Core SQL


Char Function

Char Function

Syntax

<char function> ::=

CHR ( <numeric value expression> )

Notes

§

The CHR function returns a character corresponding to the value of the argument.

§

The argument shall be within the range 1..255.

§

If the argument is null, then the result is null.

Examples

1)

The following example selects all customer rows with a CR/LF pair in the Memo column:

SELECT *

FROM customers

WHERE memo LIKE '%' || CHR( 13 ) || CHR( 10 ) || '%'

Conformance

NexusDB extensions

-

CHR function


Tostring Function

Tostring Function

Syntax

<tostring function> ::=

TOSTRING ( <value expression> )

Notes

§

The TOSTRING function returns a text representation of the argument.

§

The data type of the result is CHARACTER.

§

If the argument is null, then the result is null.

Examples

1)

The following example converts the studentID column to a character string:

SELECT studentName, TOSTRING( studentID ) AS student_code

FROM students

ORDER BY student_code

Conformance

NexusDB extensions

-

TOSTRING function


Tostringlen Function

Tostringlen Function

Syntax

<tostringlen function> ::=

TOSTRINGLEN ( <value expression> , max-length )

Notes

§

The TOSTRINGLEN function is equal to the TOSTRING function, except that the result is limited to the length specified by the max-length argument.

§

The data type of the result is CHARACTER.

§

If the value expression is null, then the result is null.

Examples

1)

The following example shows a computed column with 100 characters extracted from a BLOB column:

SELECT documentID, title, TOSTRINGLEN( content, 100 ) AS hex_content

FROM documents

ORDER BY documentID

Conformance

NexusDB extensions

-

TOSTRINGLEN function


DateTime Value Functions

DateTime Value Functions

Specify a function that returns a datetime value.

Syntax

<datetime value function> ::=

|

|

|

<current date value function>

<current time value function>

<current timestamp value function>

OVERLAY Function

Usage

DateTime value functions can be used in SQL everywhere a datetime value is permitted.

Conformance

SQL:2003 standard

-

Core SQL


Current Date Function

Current Date Function

Syntax

<current date value function> ::= CURRENT_DATE

Notes

§

The CURRENT_DATE function returns the system date on the server computer.

Examples

1)

The following example selects all future appointments:

SELECT *

FROM appointments

WHERE bookingDate >= CURRENT_DATE

Conformance

SQL:2003 standard

-

Core SQL


Current Time Function

Current Time Function

Syntax

<current time value function> ::=

|

CURRENT_TIME

LOCALTIME

Notes

§

The CURRENT_TIME function has an implicit time zone specification equal to the time offset on the server computer, and is therefore equivalent to LOCALTIME in this version of NexusDB SQL.

§

The LOCALTIME function returns the local time on the server computer.

Examples

1)

The following example selects all remaining appointments for today:

SELECT *

FROM appointments

WHERE bookingDate = CURRENT_DATE AND apptTime >= CURRENT_TIME

Conformance

SQL:2003 standard

-

Core SQL


Current Timestamp Function

Current Timestamp Function

Syntax

<current timestamp value function> ::=

|

CURRENT_TIMESTAMP

LOCALTIMESTAMP

Notes

§

The CURRENT_TIMESTAMP function has an implicit time zone specification equal to the time offset on the server computer, and is therefore equivalent to LOCALTIMESTAMP in this version of NexusDB SQL.

§

The LOCALTIMESTAMP function returns the system date and local time on the server computer.

Examples

1)

The following example selects all new appointments made today:

SELECT *

FROM appointments

WHERE bookingDate BETWEEN CURRENT_DATE AND CURRENT_TIMESTAMP

Conformance

SQL:2003 standard

-

Core SQL


OVERLAY Function

OVERLAY function

OVERLAY, which is commonly used for strings, has also been extended to allow operating on DATE, TIME, and TIMESTAMP values.

Syntax

OVERLAY (

<date, time, or timestamp value expression> PLACING <integer or float value expression>

IN {YEAR | MONTH | DAY | HOUR | MINUTE | SECOND }

Usage

The function allows to replace parts of a datetime value with another value directly in place.

For YEAR..MINUTE the PLACING expression is interpreted as an integer expression, for SECOND as a float expression, containing both seconds and milliseconds as a fraction.

The value being placed must result in a valid date/time (so HOUR must be 0..23, MINUTE or SECOND 0..59.9999, DAY can't be beyond the end of the specific month, and so on).

When a YEAR or MONTH is set that would result in the existing DAY component to be beyond the valid range for that month (e.g. 29th Feb when not a leap year, or 31st Apr) then the DAY value is clamped to the last day of that month.

Here is an example of what is now possible:

SELECT

OVERLAY(CURRENT_DATE PLACING 2020 IN YEAR),

OVERLAY(CURRENT_DATE PLACING 1 IN MONTH),

OVERLAY(CURRENT_DATE PLACING 2 IN DAY),

OVERLAY(CURRENT_TIME PLACING 3 IN HOUR),

OVERLAY(CURRENT_TIME PLACING 4 IN MINUTE),

OVERLAY(CURRENT_TIME PLACING 5.1234 IN SECOND),

OVERLAY(DATE'2004-02-29' PLACING 2005 IN YEAR),

OVERLAY(DATE'2004-01-31' PLACING 4 IN MONTH)

FROM

#dummy;


System Functions

System Functions

The following table shows the special system functions supported in NexusDB SQL.

Syntax | Data type | Notes
USER | VARCHAR | Returns the user name associated with the current session.
CURRENT_USER | VARCHAR | Equivalent to USER.
SESSION_USER | VARCHAR | Equivalent to USER.
SYSTEM_ROW# | DWORD | The function can only be defined as the first select column in query specifications, and will assign a sequential (zero-based) row-id to the rows in the result set. Cannot be used inside other functions.
NEWGUID | GUID | Returns a generated GUID value.
ROWSREAD |  | Returns the number of rows in the result set of a SELECT statement executed prior to calling ROWSREAD.
ROWSAFFECTED |  | Returns the number of rows affected by a data-change statement executed prior to calling ROWSAFFECTED.
ERROR_MESSAGE | VARCHAR | Returns the last error message from the system. The function can only be used in the CATCH clause of the TRY statement.

Examples

1)

The following example selects all appointments made by the current user:

SELECT *

FROM orders

WHERE takenBy = CURRENT_USER

2)

The following example retrieves a unique row-id and the customer name:

SELECT SYSTEM_ROW# AS row_id, customer_name

FROM customers

3)

The following example uses the NEWGUID function to assign a value to the documentID column:

INSERT INTO documents ( documentID, title )

VALUES ( NEWGUID, 'NexusDB features' )

4)

The following example shows how ERROR_MESSAGE is used in the TRY statement:

START TRANSACTION;

TRY

DROP TABLE garbage;

COMMIT;

CATCH POSITION( 'Unable to open table' IN ERROR_MESSAGE ) <> 0

ROLLBACK;

SIGNAL ERROR_MESSAGE;

END;

Conformance

SQL:2003 standard

NexusDB extensions

-

-

-

-

-

-

Core SQL

SYSTEM_ROW#

NEWGUID

ROWSREAD

ROWSAFFECTED

ERROR_MESSAGE


Predicates

Predicates

Specify a condition that evaluates to a boolean value.

Syntax

<predicate> ::=

|

|

|

|

|

|

|

|

|

|

|

|

|

|

|

|

<comparison predicate>

<between predicate>

<in predicate>

<like predicate>

<null predicate>

<quantified comparison predicate>

<exists predicate>

<unique predicate>

<match predicate>

<equivalent predicate>

<odd predicate>

<SIMILAR Predicate>

<inserting predicate>

<updating predicate>

<deleting predicate>

SIMILAR Predicate

CONTAINS Predicate

<search condition> ::= <boolean value expression>

Usage

Predicates are boolean value expressions, and can be used in SQL everywhere a boolean value is permitted.

Search conditions

The main function of the predicates is to specify a search condition that is used in data statements to join tables and filter out unwanted rows from a set, and elsewhere in SQL a conditional computation is specified. The search condition can be a single predicate or multiple predicates combined with the AND and OR boolean operators.

Conformance

SQL:2003 standard

NexusDB extensions

-

-

-

-

-

-

-

-

-

-

-

-

Core SQL

Feature F561 "Full value expressions"

Feature F281 "LIKE enhancements"

Feature F481 "Expanded NULL predicate"

Feature T501 "Enhanced EXISTS predicate"

Feature F291 "UNIQUE predicate"

Feature F741 "Referential MATCH types"

EQUIVALENT predicate

ODD predicate

INSERTING predicate

UPDATING predicate

DELETING predicate


Comparison Predicate

Comparison Predicate

Specify a comparison of two row values.

Syntax

<comparison predicate> ::=

<row value predicand> <comparison predicate part 2>

<comparison predicate part 2> ::= <comp op> <row value predicand>

<comp op> ::=

|

|

|

|

|

<equals operator>

<not equals operator>

<less than operator>

<greater than operator>

<less than or equals operator>

<greater than or equals operator>

Notes

§

The degree of the row value predicands shall be 1.

§

The data types shall be comparable.

§

The predicate evaluates to TRUE if the comparison criteria is met, else to FALSE.

§

If either of the row values are null, then the predicate evaluates to UNKNOWN.

Examples

1)

The following example selects students who are not from California:

SELECT studentName, state

FROM students

WHERE state <> 'CA'

2)

The following example selects teachers who earn $35,000 or more:

SELECT teacherName, salary

FROM teachers

WHERE salary >= 35000

Conformance

SQL:2003 standard

-

Core SQL


Between Predicate

Between Predicate

Specify a range comparison.

Syntax

<between predicate> ::=

<row value predicand> <between predicate part 2>

<between predicate part 2> ::=

[ NOT ] BETWEEN <row value predicand> AND <row value predicand>

Notes

§

The BETWEEN predicate is equal to:

value1 >= value2 AND value1 <= value3

§

The degree of the row value predicands shall be 1.

§

The data types shall be comparable.

§

The result of evaluating the predicate is shown in the Truth table for the AND boolean operator.

§

The NOT operator inverts the meaning of the comparison and is equal to:

NOT ( <row value predicand> BETWEEN <row value predicand> AND <row value predicand> )

Examples

1)

The following example selects teachers who earn between $30,000 and $35,000:

SELECT teacherName, salary

FROM teachers

WHERE salary BETWEEN 30000 AND 35000

Conformance

SQL:2003 standard

-

Core SQL


In Predicate

In Predicate

Specify a quantified comparison.

Syntax

<in predicate> ::=

<row value predicand> <in predicate part 2>

<in predicate part 2> ::= [ NOT ] IN <in predicate value>

<in predicate value> ::=

|

<table subquery>

( <row value expression> [ { , <row value expression> }... ] )

Notes

§

The degree of the row value predicand, table subquery and row value expressions shall be 1.

§

The data types shall be comparable.

§

The predicate evaluates to TRUE if the row value predicand has a matching value in the list of values specified by the in predicate value, else to FALSE.

§

If the row value predicand is null, then the predicate evaluates to UNKNOWN.

§

The NOT operator inverts the meaning of the comparison and is equal to:

NOT ( <row value predicand> IN <in predicate value> )

Examples

1)

The following example selects students from California and New York:

SELECT studentName, state

FROM students

WHERE state IN ( 'CA', 'NY' )

2)

The following examples uses a subquery with the IN predicate to select students who are enrolled in courseID 730:

SELECT studentID, studentName

FROM students

WHERE studentID IN ( SELECT studentID FROM enrolls WHERE courseID = 730 )

Conformance

SQL:2003 standard

-

-

Core SQL

Feature F561 "Full value expressions"


Like Predicate

Like Predicate

Specify a pattern-match comparison.

Syntax

<like predicate> ::=

<row value predicand> <character like predicate part 2> [ IGNORE CASE ]

<character like predicate part 2> ::=

[ NOT ] LIKE <character pattern> [ ESCAPE escape-character ]

<character pattern> ::= <character value expression>

Notes

§

The data types of the row value predicand, character pattern and escape-character shall be comparable character string types.

§

The predicate evaluates to TRUE if the row value predicand string matches the character pattern string, else to FALSE.

§

Underscore ( _ ) is used as a wildcard character representing an arbitrary single character specifier at the location in character pattern.

§

Percent ( % ) is used as a wildcard character representing an arbitrary string specifier at the location in character pattern.

§

The ESCAPE clause causes the character following the escape-character to be treated as a normal character, which allows the special wildcard characters to be used as single character specifiers in character pattern.

§

If any of the row value predicand, character pattern or escape-character values are null, then the predicate evaluates to UNKNOWN.

§

The NOT operator inverts the meaning of the comparison and is equal to:

NOT ( <row value predicand> LIKE <character pattern> [ ESCAPE escape-character ] )

§

IGNORE CASE makes the comparison operation case-insensitive.

Examples

1)

The following example selects students whose first name starts with 'Jo':

SELECT studentName, gender

FROM students

WHERE studentName LIKE 'Jo%'

2)

The following example selects students whose first name has an 'o' in the second position:

SELECT studentName, gender

FROM students

WHERE studentName LIKE '_o%'

3)

The following example selects orders with '10%' as a portion of the notes column value:

SELECT orderID, notes

FROM orders

WHERE notes LIKE '%10\%%' ESCAPE '\'

Conformance

SQL:2003 standard

NexusDB extensions

-

-

-

Core SQL

Feature F281 "LIKE enhancements"

IGNORE CASE


Null Predicate

Null Predicate

Specify a test for a null value.

Syntax

<null predicate> ::=

<row value predicand> <null predicate part 2>

<null predicate part 2> ::= IS [ NOT ] NULL

Notes

§

The degree of the row value predicand shall be 1.

§

IS NULL evaluates to TRUE if the row value is null, else to FALSE.

§

IS NOT NULL evaluates to TRUE if the row value is different from null, else to FALSE.

Examples

1)

The following example selects students who don't take any courses:

SELECT studentID, studentName

FROM students s

left join enrolls e ON e.studentID = s.studentID

WHERE e.studentID IS NULL

Conformance

SQL:2003 standard

-

-

Core SQL

Feature F481 "Expanded NULL predicate"


Quantified Comparison Predicate

Quantified Comparison Predicate

Specify a quantified comparison.

Syntax

<quantified comparison predicate> ::=

<row value predicand> <quantified comparison predicate part 2>

<quantified comparison predicate part 2> ::=

<comp op> <quantifier> <table subquery>

<quantifier> ::=

|

ALL

{ SOME | ANY }

Notes

§

The degree of the row value predicand and table subquery shall be 1.

§

The data types shall be comparable.

§

If ALL is specified, then the predicate is evaluated as follows:

a) If the result of the subquery is empty or the comparison is true for every row in the subquery, then the result is TRUE.

b) If the comparison is false for at least one row in the subquery, then the result is FALSE.

c) If any of the values returned by the subquery is null and none of the comparisons are false, then the result is UNKNOWN.

§

If SOME or ANY is specified, then the predicate is evaluated as follows:

a) If the comparison is true for at least one row in the subquery, then the result is TRUE.

b) If the result of the subquery is empty or the comparison is false for every row in the subquery, then the result is FALSE.

c) If any values returned by the subquery is null and none of the comparisons are true, then the result is UNKNOWN.

§

If the row value predicand is null, then the predicate evaluates to UNKNOWN.

Examples

1)

The following example selects students enrolled in all available courses:

SELECT studentID, studentName

FROM students s

WHERE studentID = ALL (

SELECT e.studentID

FROM courses c

LEFT JOIN enrolls e on

e.courseID = c.courseID AND e.studentID = s.studentID

)

2)

The following example selects students enrolled in at least one course:

SELECT studentID, studentName

FROM students s

WHERE studentID = ANY (

SELECT e.studentID

FROM courses c

LEFT JOIN enrolls e on

e.courseID = c.courseID AND e.studentID = s.studentID

)

Conformance

SQL:2003 standard

-

Core SQL


Exists Predicate

Exists Predicate

Specify a test for a non-empty set.

Syntax

<exists predicate> ::= EXISTS <table subquery>

Notes

§

The predicate evaluates to TRUE if the cardinality of the subquery result is greater than 0, else to FALSE.

Examples

1)

The following example selects students enrolled in more than 2 courses:

SELECT studentID, studentName

FROM students s

WHERE EXISTS (

SELECT COUNT( * )

FROM enrolls

WHERE studentID = s.studentID

HAVING COUNT( * ) > 2

)

2)

The following example selects students not enrolled in any courses:

SELECT studentID, studentName

FROM students s

WHERE EXISTS (

SELECT COUNT( * )

FROM enrolls

WHERE studentID = s.studentID

HAVING COUNT( * ) = 0

)

Conformance

SQL:2003 standard

-

-

Core SQL

Feature T501 "Enhanced EXISTS predicate"


Unique Predicate

Unique Predicate

Specify a test for the absence of duplicate rows.

Syntax

<unique predicate> ::= UNIQUE <table subquery>

Notes

§

The predicate evaluates to TRUE if the subquery result is empty or it contains no duplicate rows, else to FALSE.

Examples

1)

The following example selects students enrolled in none or only 1 course:

SELECT studentID, studentName

FROM students s

WHERE UNIQUE (

SELECT studentID

FROM enrolls

WHERE studentID = s.studentID

)

Conformance

SQL:2003 standard

-

Feature F291 "UNIQUE predicate"


Match Predicate

Match Predicate

Specify a test for matching rows.

Syntax

<match predicate> ::=

<row value predicand> <match predicate part 2>

<match predicate part 2> ::=

MATCH [ UNIQUE ] [ SIMPLE | PARTIAL | FULL ] <table subquery>

Notes

§

The degree of the row value predicand and table subquery shall be 1.

§

The data types shall be comparable.

§

If neither SIMPLE, PARTIAL, nor FULL is specified, then SIMPLE is implicit. Since this version of NexusDB only allows row value constructors with a degree of 1 (one column), specifying SIMPLE, PARTIAL or FULL will evaluate to exactly the same result.

§

The predicate evaluates to TRUE if the row value predicand is null, or the row value predicand is different from null and there is at least one row in the subquery result matching the row value predicand, else to FALSE.

§

If UNIQUE is specified, then the predicate evaluates to TRUE only if there is exactly one row in the subquery result matching the row value predicand.

Examples

1)

The following example selects students enrolled in any number of courses:

SELECT studentID, studentName

FROM students

WHERE studentID MATCH (

SELECT studentID

FROM enrolls

)

2)

The following example selects students enrolled in exactly 1 course:

SELECT studentID, studentName

FROM students

WHERE studentID MATCH UNIQUE (

SELECT studentID

FROM enrolls

)

Conformance

SQL:2003 standard

-

Feature F741 "Referential MATCH types"


Equivalent Predicate

Equivalent Predicate

Compares two values for equivalence.

Syntax

<equivalent predicate> ::=

EQUIVALENT ( <value expression>, <value expression> )

Notes

§

The data types shall be comparable.

§

The predicate evaluates to TRUE if the two values are equal, else to FALSE.

§

Null values are considered equal in this predicate.

Examples

1)

The following example selects students from Newport:

SELECT studentID, studentName, city

FROM students

WHERE EQUIVALENT ( city, 'Newport' )

Conformance

NexusDB extensions

-

EQUIVALENT predicate


Odd Predicate

Odd Predicate

Specify a test for odd numbers.

Syntax

<odd predicate> ::= ODD ( <numeric value expression> )

Notes

§

The data type shall be numeric.

§

The predicate evaluates to TRUE if the value is an odd number, else to FALSE.

§

If the given value is null, then the predicate evaluates to UNKNOWN.

Examples

1)

The following example selects students with odd studentID:

SELECT studentID, studentName

FROM students

WHERE ODD( studentID )

Conformance

NexusDB extensions

-

ODD predicate


Inserting Predicate

Inserting Predicate

Testing if a trigger event is INSERT.

Syntax

<inserting predicate> ::= INSERTING

Notes

§

The INSERTING predicate can only be specified in the body of a trigger definition to check the trigger event.

§

The predicate evaluates to TRUE if the trigger was activated by an INSERT operation on the table, else to FALSE.

Examples

1)

The following example shows how the INSERT predicate is used in the WHEN clause of a triggered action:

WHEN ( INSERTING )

triggered SQL statements

2)

The following example shows how the INSERT predicate is used in a triggered SQL statement:

IF INSERTING THEN ... END IF;

Conformance

NexusDB extensions

-

INSERTING predicate


Updating Predicate

Updating Predicate

Testing if a trigger event is UPDATE.

Syntax

<updating predicate> ::= UPDATING

Notes

§

The UPDATING predicate can only be specified in the body of a trigger definition to check the trigger event.

§

The predicate evaluates to TRUE if the trigger was activated by an UPDATE operation on the table, else to FALSE.

Examples

1)

The following example shows how the UPDATE predicate is used in the WHEN clause of a triggered action:

WHEN ( UPDATING )

triggered SQL statements

2)

The following example shows how the UPDATE predicate is used in a triggered SQL statement:

IF UPDATING THEN ... END IF;

Conformance

NexusDB extensions

-

UPDATING predicate


Deleting Predicate

Deleting Predicate

Testing if a trigger event is DELETE.

Syntax

<deleting predicate> ::= DELETING

Notes

§

The DELETING predicate can only be specified in the body of a trigger definition to check the trigger event.

§

The predicate evaluates to TRUE if the trigger was activated by an DELETE operation on the table, else to FALSE.

Examples

1)

The following example shows how the DELETING predicate is used in the WHEN clause of a triggered action:

WHEN ( DELETING )

triggered SQL statements

2)

The following example shows how the DELETING predicate is used in a triggered SQL statement:

IF DELETING THEN ... END IF;

Conformance

NexusDB extensions

-

DELETING predicate


SIMILAR Predicate

SIMILAR predicate

This predicate allows for regular expression matching. The exact syntax that NexusDB implements diverges from the SQL standard.

Syntax

<row value predicand> [ NOT ] SIMILAR [ TO ] <similar pattern> [ IGNORE CASE ]

Usage

The regular expressions are evaluated using the PCRE library.

A quick overview of the supported syntax can be found at

https://www.pcre.org/original/doc/html/pcresyntax.html

The full specification of valid patterns can be found at

https://www.pcre.org/original/doc/html/pcrepattern.html

SIMILAR evaluates to TRUE if the pattern evaluated against the predicand produces one or more matches, it evaluates to FALSE of there are 0 matches, if predicand or pattern is NULL, it returns NULL.

Here is an example of what is now possible:

CREATE LOCAL TEMPORARY TABLE #strings (

string NVARCHAR(30)

);

INSERT INTO #strings VALUES ('the blue king');

INSERT INTO #strings VALUES ('the Red king');

INSERT INTO #strings VALUES ('the white prince');

INSERT INTO #strings VALUES ('the white queen');

INSERT INTO #strings VALUES ('red queen');

SELECT

*,

string SIMILAR TO 'the (red|white) (king|queen)',

string SIMILAR TO 'the (red|white) (king|queen)' IGNORE CASE,

string SIMILAR TO '(red|white) (king|queen)'

FROM

#strings;

The produced output is:

the blue king - FALSE FALSE FALSE

the Red king - FALSE TRUE FALSE

the white prince - FALSE FALSE FALSE

the white queen - TRUE TRUE TRUE

red queen - FALSE FALSE TRUE


CONTAINS Predicate

CONTAINS predicate

This predicate allows for searching on the contents of a fulltext index. The named fulltext index must exist in the table in order to use this predicate.

Syntax

<contains predicate> ::= CONTAINS(<SqlName>, <FullTextSearchExpr>)

<SqlName> ::= name of existing fulltext index

<FullTextSearchExpr> ::= { <FullTextSearchTerm> "OR" }

<FullTextSearchFactor> :== [ "NOT" ] [ "STARTS" "WITH" ] <SimpleExpression>

<FullTextSearchTerm> :== { <FullTextSearchFactor> "AND" }

Usage

SELECT * FROM Customers

WHERE CONTAINS(idx_ft_name, 'jack' OR 'jill')


Stored Procedures and Functions

Stored Procedures and Functions

The support for user-defined procedures and functions, commonly known as stored procedures, makes NexusDB SQL a programmable database language, allowing users to automate tasks and extend the built-in functionality with custom routines.

NexusDB SQL supports user-defined procedures and functions written in SQL, called SQL routines, or hosted in a .NET assembly, called CLR routines (Enterprise Edition only). Since user-defined procedures and functions share many common characteristics and syntactic elements, they are both called SQL-invoked routines in standard SQL.

The procedure language in NexusDB adds power and flexibility to the functionality of user-defined procedures and functions written in SQL.

User-defined procedures

A user-defined procedure (UDP) can have both IN and OUT parameters. The OUT parameter is used to return a value from the procedure, and is equivalent to the result of a scalar-valued function. Procedures can also return a cursor by specifying a SELECT statement as the last statement in the routine body.

User-defined procedures are defined with the CREATE PROCEDURE statement and are invoked in SQL by the CALL statement.

The following example shows a procedure definition and how it is called in SQL:

CREATE PROCEDURE addCourse (

// Parameter declarations with implicit IN mode

p_courseID SMALLINT,

p_courseName CHAR(20),

p_department CHAR(20),

p_numCredits TINYINT

)

MODIFIES SQL DATA -- We need write-access to update the courses table

// We prefer to use a compound statement in the routine body, even with a single statement

BEGIN

INSERT INTO courses

VALUES ( p_courseID, p_courseName, p_department, p_numCredits );

END;

CALL addCourse( :courseID, :courseName, :department, :numCredits );

User-defined functions

Like functions in traditional programming languages, a user-defined function (UDF) always returns a value. The function result can be either a scalar value (scalar-valued function) or a table derived from a cursor specification (table-valued function), by specifying TABLE in the RETURNS clause of the function definition. A scalar-valued function can be referenced everywhere in SQL a scalar value is expected, while a table-valued function can be referenced in the FROM clause of a SELECT statement or invoked by calling the function as a separate SQL statement.

User-defined functions are defined with the CREATE FUNCTION statement and are invoked in SQL by the function name, see routine invocation.

The following example shows a function definition and how it is invoked in SQL:

CREATE FUNCTION getFullName ( firstName VARCHAR(30), lastName VARCHAR(30) )

RETURNS VARCHAR(61)

/*

LANGUAGE SQL is implicit

NOT DETERMINISTIC is implicit

CONTAINS SQL is implicit

*/

RETURNS NULL ON NULL INPUT -- We don't invoke the function if any of the arguments are null

// Multiple statements in the routine body must appear inside a compound statement

BEGIN

DECLARE name VARCHAR(61);

SET name = firstName || ' ' || lastName;

RETURN name;

END;

UPDATE students

SET studentName = getFullName( firstName, lastName )

WHERE studentID = 211;

Procedure Language

SQL-Invoked Routines

SQL Procedure Statements


Procedure Language

Procedure Language

The procedure language in NexusDB SQL consists of a set of control statements that extends the SQL language with compound statement blocks (BEGIN .. END), SQL variable declarations, conditional statements, iterating statements and error handling. This functionality adds computational completeness to NexusDB SQL and makes it a fully programmable database language, which is particularly useful in the definition of triggers, user-defined procedures and user-defined functions.

CALL statement

RETURN statement

Compound statement

DECLARE variable statement

SET statement

IF statement

ITERATE statement

LEAVE statement

REPEAT statement

WHILE statement

SIGNAL statement

TRY statement


SQL-Invoked Routines

SQL-Invoked Routines

Define an SQL-invoked routine.

Syntax

<SQL-invoked routine> ::=

|

<user-defined procedure>

<user-defined function>

The following syntax elements are common to both procedures and functions:

<SQL parameter declaration list> ::=

( [ <SQL parameter declaration> [ { , <SQL parameter declaration> }... ] ] )

<SQL parameter declaration> ::=

[ <parameter mode> ] parameter-name <data type>

<parameter mode> ::=

|

|

IN

OUT

INOUT

<routine characteristics> ::= [ <routine characteristic>... ]

<routine characteristic> ::=

|

|

|

<language clause>

<deterministic characteristic>

<SQL-data access indication>

<null-call clause>

<language clause> ::= LANGUAGE { SQL | CLR }

<deterministic characteristic> ::=

|

DETERMINISTIC

NOT DETERMINISTIC

<SQL-data access indication> ::=

|

|

|

NO SQL

CONTAINS SQL

READS SQL DATA

MODIFIES SQL DATA

<null-call clause> ::=

|

RETURNS NULL ON NULL INPUT

CALLED ON NULL INPUT

<routine body> ::=

|

<SQL routine body>

<external body reference>

<SQL routine body> ::= <SQL procedure statement>

<external body reference> ::= EXTERNAL [ NAME <external routine name> ]

<external routine name> ::= assembly-name [ .class-name [ .method-name ] ]

<routine invocation> ::= routine-name <SQL argument list>

<SQL argument list> ::= ( [ <SQL argument> [ { , <SQL argument> }... ] ] )

<SQL argument> ::=

|

<value expression>

<target specification>

SQL parameter declaration

§

A parameter mode of IN is implicit if parameter mode is not specified.

§

The OUT parameter has an initial value of null, and is used to output values from a procedure.

§

The INOUT parameter is a combined IN and OUT parameter.

§

Function definitions shall not specify a parameter mode.

§

SQL parameter names shall conform to the rules for identifiers in NexusDB SQL.

Routine characteristics

§

Language clause

NexusDB SQL supports user-defined procedures and functions written in SQL or hosted in a .NET assembly.

§

LANGUAGE SQL defines an SQL routine, and is implicit if the clause is omitted. SQL routines shall specify an SQL routine body.

§

LANGUAGE CLR defines a .NET CLR routine. CLR routines shall specify an external body reference, which is a reference to a class method in a .NET assembly.

Note: CLR routines are supported in the Enterprise Edition only.

§

Deterministic characteristic

If neither DETERMINISTIC nor NOT DETERMINISTIC is specified, then NOT DETERMINISTIC is implicit. A DETERMINISTIC routine is guaranteed to produce the same result every time it is invoked with the same argument values, and may therefore be optimized by caching the result. NOT DETERMINISTIC routines are re-evaluated each time.

§

SQL-data access indication

Defines whether or not the routine contains SQL statements, and if the routine is allowed to read or change SQL data.

§

NO SQL can only be used with CLR routines, and means that the external routine contains no SQL statements. NO SQL is implicit for CLR routines if no SQL-data access indication is specified.

§

CONTAINS SQL is implicit for SQL routines if no SQL-data access indication is specified, and means that the routine may contain SQL statements, except any of the data statements.

§

READS SQL DATA means that the routine may contain SQL statements, including the SELECT statement, but is not allowed to specify any of the data change statements.

§

MODIFIES SQL DATA means that the routine may contain any of the SQL procedure statements, including the data-change statements.

§

A routine is only allowed to call other routines that specify the same or a higher SQL-data access level.

§

Null-call clause

Is used to specify whether or not a function should be invoked if any of the arguments are null.

§

CALLED ON NULL INPUT, which is implicit if the clause is not specified, means that the function is invoked regardless of null values in the arguments.

§

RETURNS NULL ON NULL INPUT instructs the engine to return null as the function result if any of the arguments are null, without invoking the function itself.

§

Procedure definitions shall not specify a null-call clause.

Routine body

§

SQL Routines

The body of an SQL routine is defined using an SQL routine body, which is a single SQL procedure statement. The Compound statement can be used to specify multiple SQL statements inside a BEGIN..END block.

§

CLR Routines

The body of a CLR routine is defined by providing an EXTERNAL body reference, which is a reference to a .NET assembly that has been registered on the NexusDB Server. Assemblies can be registered directly in the NexusDB Server GUI, or in SQL by using the CREATE ASSEMBLY statement.

§

The NAME clause may be omitted in the rare case of the assembly name, the class name and the method name being the same as the routine name.

§

assembly-name is the name used to register the assembly. The assembly can only be referenced by its owner and users with REFERENCES privilege on the assembly.

§

class-name is the name of an existing class in the assembly. If the class has a multi-part name separated with periods, then the name must be delimited with double quotation marks, for example: AssemblyName."Namespace.ClassName". The class name may be omitted if the name is the same as the assembly name.

§

method-name is the name of a method defined in the specified class. The method name may be omitted if the name is the same as the class name.

Note: CLR routines are supported in the Enterprise Edition only.

Routine invocation

§

User-defined procedures are invoked by the CALL statement followed by the routine name and the argument list.

§

User-defined functions are invoked by the routine name followed by the argument list only.

§

The number and types of arguments shall match the parameter declarations in the routine definition.

§

The parentheses in the argument list shall be specified even if the routine has no parameters.

§

Any arguments that represent an OUT parameter must be a target specification.

Examples

1)

The following example calls a user-defined procedure passing two dynamic parameter values and one local SQL variable as arguments:

CALL setStudentName( :lastName, :firstName, studentName )

2)

The following example calls a user-defined function without arguments:

getSystemTime()

3)

The following example calls a user-defined function that takes two arguments:

getFullName( 'Tiger', 'Woods' )

Conformance

SQL:2003 standard

NexusDB extensions

-

-

-

-

Core SQL

Feature B128 "Routine language SQL"

Feature T653 "SQL-schema statements in external routines"

LANGUAGE CLR


SQL Procedure Statements

SQL Procedure Statements

Define all of the SQL statements that are allowed in SQL routines.

Syntax

<SQL procedure statement> ::=

|

|

|

|

<SQL schema statement>

<SQL data statement>

<SQL control statement>

<SQL transaction statement>

<SQL session statement>

<SQL schema statement> ::=

|

<SQL schema definition statement>

<SQL schema manipulation statement>

<SQL schema definition statement> ::=

|

|

|

|

<table definition>

<index definition>

<view definition>

<SQL-invoked routine>

<trigger definition>

<SQL schema manipulation statement> ::=

|

|

|

|

|

<alter table statement>

<drop table statement>

<drop index statement>

<drop view statement>

<drop routine statement>

<drop trigger statement>

<SQL data statement> ::=

|

<select statement>

<SQL data change statement>

<SQL data change statement> ::=

|

|

<insert statement>

<update statement: searched>

<delete statement: searched>

<SQL control statement> ::=

|

|

|

|

|

|

|

|

|

|

|

<call statement>

<return statement>

<compound statement>

<SQL variable declaration>

<assignment statement>

<if statement>

<iterate statement>

<leave statement>

<repeat statement>

<while statement>

<signal statement>

<try statement>

<SQL transaction statement> ::=

|

|

<start transaction statement>

<commit statement>

<rollback statement>

<SQL session statement> ::=

<set passwords statement>

Conformance

SQL:2003 standard

NexusDB extensions

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

Core SQL

Feature T651 "SQL-schema statements in SQL routines"

Feature P002-01 "Compound statement"

Feature P002-04 "SQL variable declaration"

Feature P002-05 "Assignment statement"

Feature P002-07 "IF statement"

Feature P002-08 "ITERATE statement"

Feature P002-09 "LEAVE statement"

Feature P002-11 "REPEAT statement"

Feature P002-12 "WHILE statement"

Feature P002-14 "SIGNAL statement"

CREATE INDEX statement

DROP INDEX statement

TRY statement

SET PASSWORDS statement


SQL Statements

SQL Statements

An SQL statement is a sequence of characters that conforms to the syntax rules for the statement and the general rules for keywords and identifiers.

The execution of an SQL statement triggers database actions as implied by the content of the statement. Statements can be executed one by one or as a sequence of statements, either in the main execution block or in a Compound statement block. When SQL statements are executed as a sequence, each individual statement must be terminated with a semicolon.

SQL statements are categorized according to type of function:

§

Schema Statements

§

Data Statements

§

Transaction Statements

§

Session Statements

§

Control Statements

§

Special Statements

Prepared Statements

Statement switches


Prepared Statements

Prepared Statements

NexusDB preserves the execution context of an SQL statement if a prepared statement is requested by the client application, and stays prepared until explicitly unprepared. A prepared statement can therefore be reused without the overhead of creating a new execution context every time the statement is executed.

Usage

Prepared statements are typically used in combination with parameters, which is an efficient method for reusing data manipulation statements that are frequently used. For example, instead of executing a batch of UPDATE statements with explicit values, it is more efficient to prepare a parameterized UPDATE statement and provide the parameter values before each execution.

Examples

SELECT statement with parameters in the search condition

INSERT statement with parameters in the VALUES clause

UPDATE statement with parameters in the SET clause

DELETE statement with parameters in the WHERE clause


Statement Switches

Statement Switches

The following statement switches are NexusDB extensions that can be specified in a statement block to set execution characteristics:

Syntax

<blob switch> ::= #B { + | - }

<index switch> ::= #I { + | - }

<simplify switch> ::= #S { + | - }

<log switch> ::= #L { + | - }

<timeout switch> ::= #T integer-literal

<feature switch> ::= #F feature-name { + | - }

Blob Switch

This switch controls the behavior when copying columns that contain BLOBs. In the default #B- state, the query engine only copies a link to the original BLOB in the result set which can save on both memory and execution time. The exception to this is if the source table is a temporary table, in which case the query engine will copy the full BLOB from the temporary table to the result set.

If switched on (#B+) the query engine will always copy the full BLOB to the result set. Since NexusDB allows for query results drawn on tables that are dropped within the same statement block, it is possible to write a SQL block so that BLOB links would refer to a table which had since been dropped, so understanding the implications of this setting is important.

SELECT * FROM BlobTable;

DROP BlobTable;

In this case, the SELECT statement must be prefixed with #B+ to force BLOBs to be copied from BlobTable into the query result.

Index Switch

Indexing optimization turns the use of indexes on and off for the following statement. This switch defaults to #I+ (on), and is usually only turned off when trying to troubleshoot the query optimizer.

Simplify Switch

#S- turns query simplification off for the statement that follows the switch (default is on). Query simplification is a process where the structure of a parsed query is simplified. E.g. x BETWEEN y AND z would be simplified to x >= y AND x <= z .

As simplification occurs before the SQL statement is bound to the tables it works on, occasionally, simplification can convert a query into a less efficient alternative. For such cases, the #S- switch allows you turn simplification off. When logging is enabled (see #L+), the individual stages in the simplification process are logged.

Log Switch

#L+ turns on logging for the statement block as a whole and should be placed before any other noncomment line. When logging is enabled, (default is off) the query engine collects information about simplifications, index use, and query metrics, and builds a text report which is returned to the client with the query result and status. The report can be accessed through the Log property of the nxQuery component and can be used to analyze the query for opportunities to rephrase statements for better performance.

Timeout Switch

#T sets the query timeout period to the value specified by integer-literal in milliseconds.

Feature Switch

#F sets the named feature switch to the value specified by the switch value.


Schema Statements

Schema Statements

The schema statements, the part of the SQL language commonly known as DDL, Data Definition Language, are used to create, alter and drop schema (database) objects.

CREATE TABLE statement

CREATE INDEX statement

CREATE VIEW statement

CREATE TRIGGER

CREATE PROCEDURE statement

CREATE FUNCTION statement

CREATE ASSEMBLY statement

ALTER TABLE statement

DROP TABLE statement

DROP INDEX statement

DROP VIEW statement

DROP TRIGGER statement

DROP ROUTINE statement

DROP ASSEMBLY statement


CREATE TABLE statement

CREATE TABLE statement

Define a persistent base table, a global temporary table, or a local temporary table.

Syntax

<table definition> ::=

CREATE [ { GLOBAL | LOCAL } TEMPORARY ] TABLE [ schema-name. ] table-name

[ DESCRIPTION <character string literal> ]

[ { BLOCKSIZE | BLOCK } <unsigned integer> ]

[ { INITIALSIZE | INITIAL } <unsigned integer> ]

[ { GROWSIZE | GROW } <unsigned integer> ]

[ STORAGE [ ENGINE ] <character string literal> ]

[ ENCRYPTION [ ENGINE ] <character string literal> ]

[ ENCRYPT WITH <character string literal> ]

[ DEFAULT <character set clause> ]

[ DEFAULT <collation clause> ]

<table contents source>

<collation clause> ::=

|

COLLATION <character string literal> [ <compare flag>... ]

LOCALE <unsigned integer> [ <compare flag>... ]

<table contents source> ::=

|

( <table element> [ { , <table element> }... ] )

<as subquery clause>

<table element> ::=

|

|

<column definition>

<table constraint definition>

<like clause>

<like clause> ::= LIKE [ schema-name. ] table-name

<as subquery clause> ::=

[ ( column-name [ { , column-name }... ] ) ] AS <subquery>

WITH { NO DATA | DATA }

<column definition> ::=

column-name <data type>

[ <default clause> ]

[ <column constraint>... ]

[ DESCRIPTION <character string literal> ]

<default clause> ::= DEFAULT <default option>

<default option> ::=

|

|

|

|

|

|

|

<literal>

NULL

EMPTY

NEWGUID

CURRENT_DATE

{ CURRENT_TIME | LOCALTIME }

{ CURRENT_TIMESTAMP | LOCALTIMESTAMP }

{ USER | CURRENT_USER | SESSION_USER }

<column constraint definition> ::= [ <constraint name definition> ] <column constraint>

<constraint name definition> ::= CONSTRAINT constraint-name

<column constraint> ::=

|

|

|

NOT NULL

<unique constraint definition>

<references specification>

<check constraint definition>

<table constraint definition> ::= [ <constraint name definition> ] <table constraint>

<table constraint> ::=

|

|

<unique constraint definition>

<referential constraint definition>

<check constraint definition>

<unique constraint definition> ::= { UNIQUE | PRIMARY KEY } ( column-name [ { , column-name }... ] )

<referential constraint definition> ::=

FOREIGN KEY ( column-name [ { , column-name }... ] )

<references specification>

<references specification> ::=

REFERENCES [ schema-name. ] table-name [ ( column-name [ { , column-name }... ] ) ]

[ <referential triggered action> ]

<referential triggered action> ::=

|

<update rule> [ <delete rule> ]

<delete rule> [ <update rule> ]

<update rule> ::= ON UPDATE <referential action>

<delete rule> ::= ON DELETE <referential action>

<referential action> ::=

|

|

|

CASCADE

SET NULL

SET DEFAULT

RESTRICT

<check constraint definition> ::= CHECK ( <search condition> )

Usage

The CREATE TABLE statement creates a new table in the database with metadata according to the table definition.

§

The schema and table names shall conform to the format rules for identifiers and schema-qualified names in NexusDB SQL. The table is created in the database specified by schema-name. The current database is implicit if a schema name is not specified.

Table attributes

The following table descriptor attributes are NexusDB extensions that can be used to tailor the table definition:

§

DESCRIPTION is a free text attribute used to store a comment in the table descriptor.

§

BLOCKSIZE sets the physical page size to one of the valid values 4, 8, 16, 32 or 64 kilobytes. The default value is 4. The page size is automatically adjusted to fit the total row size.

§

INITIALSIZE sets the initial number of allocated pages. The default value is 4.

§

GROWSIZE sets the number of new pages allocated when more space is needed. The default value is 1.

§

STORAGE ENGINE specifies the name of the storage engine used for storing data in the table. The names of the built-in storage engines are 'Static', which stores data in fixed length, and 'Variable', which stores data in variable length. The default storage engine is 'Static'.

§

ENCRYPTION ENGINE specifies the name of the encryption engine used to encrypt the table. The name of the built-in encryption engine is 'nx1xDefault'. NexusDB tables are not encrypted if an encryption engine is not explicitly specified.

§

If ENCRYPT WITH is specified, then the encryption engine will use the password as part of the encryption algorithm. A password-encrypted table cannot be accessed unless the password has been added to the list of passwords available in the current session, see the SET PASSWORDS statement.

§

DEFAULT CHARACTER SET or CODEPAGE can be used interchangeably to override the implicit codepage associated with the default collation for the table.

§

DEFAULT COLLATION or LOCALE can be used interchangeably to set a default collation for the table. If the clause is omitted, then the default collation is byte order for character string columns and a locale-neutral collation for national character string columns.

Note: Table attributes must be specified in the order they appear in the syntax specification.

Table contents

The following elements can be used to define the table contents:

§

Column definition

§

Table constraint definition

§

LIKE clause

§

AS subquery clause

Column definition

A column definition consists of the following column descriptor attributes:

§

Column name

§

Data type

§

Default clause (optional)

§

Column constraints (optional)

§

DESCRIPTION (optional)

§

The column name shall conform to the format rules for identifiers in NexusDB SQL.

§

The data type can be any of the predefined data types in NexusDB SQL.

§

A character set can be specified for character string columns to override the default codepage associated with the implicit or explicit collation for the column.

§

A collation to be used in sort and comparison operations on the column, can be specified for character string and national character string columns. The default collation for the table is implicit if a collation is not explicitly defined.

§

The DEFAULT clause is used to assign a default value to the column when inserting new rows, with an implicit null value if the clause is omitted.

§

DESCRIPTION is a free text attribute that can be used to store comments in the column descriptor.

Column and table constraints

§

The NOT NULL constraint can only be defined as a column constraint, and prohibits the null value to be assigned to the column in any row of the table.

§

The UNIQUE constraint can be defined either as a column constraint, in case of an one-column key, or as a table constraint with one ore more columns specified in the column list. Rows that have the null value in any of the columns contained in the UNIQUE constraint are not included in the uniqueness check implied by the constraint, hence UNIQUE alone, without also specifying NOT NULL, permits multiple null values in a column.

§

The PRIMARY KEY constraint is equal to the UNIQUE constraint, except that null values are not allowed in any column of the primary key. NOT NULL is implicit for primary key columns if not explicitly specified. Only one PRIMARY KEY constraint can be defined on a table.

§

A FOREIGN KEY is a single column or a set of columns within a table that is referencing equivalent columns in another table through its values, a relationship called Referential Integrity. The purpose of the FOREIGN KEY constraint is to ensure that rows in the referencing table always have corresponding rows in the referenced table. The FOREIGN KEY constraint can be defined either as a column constraint using the REFERENCES clause only, in case of an one-column key, or as a table constraint with one ore more referencing columns specified in the column list.

§

If the REFERENCES clause specifies a column list, then there shall be a one-to-one correspondence between the set of column names specified in the REFERENCES clause and the set of column names contained in a PRIMARY KEY or UNIQUE constraint of the referenced table. If the column list is not specified, then the referenced table shall have a PRIMARY KEY constraint containing a set of columns that matches the set of referencing columns.

§

The data types of referencing and referenced columns shall be comparable.

§

The REFERENCES clause may optionally specify an update rule, a delete rule, or both rules, each defining one of the following referential actions:

§

RESTRICT, the default referential action, aborts the current transaction with a foreign key violation error if the referential constraint is violated during the execution of an UPDATE or DELETE statement on the referenced table.

§

If CASCADE is specified, then data changes in the referenced table are replicated to the corresponding rows in the referencing table.

§

If SET NULL is specified, then referencing columns are set to null when a corresponding row in the referenced table is deleted or the referenced column is changed.

§

If SET DEFAULT is specified, then referencing columns are set to their default value when a corresponding row in the referenced table is deleted or the referenced column is changed.

§

The CHECK constraint is a general purpose constraint that can be defined either as a column constraint or a table constraint. The search condition can be any valid predicate. Columns of the table defining the constraint are referenced by the column names, while columns of other tables can be accessed by using a subquery. The CHECK constraint is satisfied if the search condition is not False for any row in the table, as opposed to having to be True for every row in the table.

§

A user-defined constraint name can be specified for both column and table constraints, except the NOT NULL constraint. The constraint name shall conform to the format rules for identifiers in NexusDB SQL. If the constraint name is not specified, then the constraint being defined is given a system-generated name. The main advantage of using named constraints, is that this feature makes constraint management a lot easier.

§

The implicit constraint mode is INITIALLY IMMEDIATE NOT DEFERRABLE.

Note: The ALTER TABLE statement is used for constraint management.

LIKE clause

§

The LIKE clause works like a shorthand for manually defining new columns based on the columns of an existing table, and can be mixed with other column definitions and table constraints.

§

The LIKE clause defines a new column for each column in the source table and copies the following column descriptor attributes to the new table:

§

Column name

§

Data type (including explicit character set and collation for character string columns)

§

DEFAULT value

§

NOT NULL constraint

§

Column constraints other than the NOT NULL constraint are not copied to the new table.

AS subquery clause

§

The AS subquery clause creates a new table based on one or more existing tables using a subquery.

§

The optional column-name list is used to rename the columns specified in the select list of the subquery. If the column-name list is specified, then the number of columns shall be the same as the degree of the subquery.

§

For each column in the subquery result set, the following column descriptor attributes are copied to the new table:

§

Column name

§

Data type (including explicit character set and collation for character string columns)

§

No table constraints or indexes are copied to the new table.

§

If WITH DATA is specified, then the new table is populated with the rows from the subquery result set.

Temporary tables

By supporting temporary tables, NexusDB provides an efficient concept for storing sets of data temporarily, without worrying about conflicts with persistent data in the database. Temporary tables are useful in many situations, for example to create fast lookup tables locally on the client, to combine data from multiple tables, and to store intermediate result sets that can be accessed and further processed by other statements.

NexusDB creates temporary tables in memory. Once created, temporary tables can be manipulated and queried just like ordinary tables, except that the visibility is limited to the scope of the temporary table type. NexusDB SQL supports three types of temporary tables with different scopes and duration:

§

A context-local temporary table is defined by prefixing the table name with a single pound symbol (#table-name). Context-local temporary tables are visible only in the execution block where they are created, and are automatically dropped when the execution block terminates. Context-local temporary tables cannot be restructured if they contain data.

§

A session-local temporary table is defined by prefixing the table name with two pound symbols (##table-name). Session-local temporary tables are visible to all processes that are executed in the context of the session and database object that created them, and are automatically dropped when the session terminates.

§

A global temporary table is defined by prefixing the table name with three pound symbols (###table-name). Global temporary tables are visible to all sessions accessing the server, and stay in memory on the server until explicitly dropped or the server shuts down.

§

The V1 syntax of defining a global temporary table by enclosing the table name in angle brackets (<table-name>) is still supported, but has been deprecated and may be removed in a future version of NexusDB SQL.

§

NexusDB SQL requires that the names of temporary tables are prefixed with pound symbols. The optional GLOBAL | LOCAL TEMPORARY TABLE syntax can be used when defining temporary tables, but is only supported for clarity and syntax compatibility with SQL:2003.

Tip: The AS subquery clause and the SELECT INTO statement are both convenient ways of creating temporary tables based on existing tables.

Examples

1)

The following example shows a table definition with column constraints:

CREATE TABLE customers

/*

We're defining a default collation for this table.

The default collation is used in sort and comparison operations on character string columns that don't explicitly specify a collation on the column level.

*/

DEFAULT LOCALE 1033

(

customerID AUTOINC NOT NULL PRIMARY KEY,

companyName VARCHAR(40),

address1 VARCHAR(30),

address2 VARCHAR(30),

city VARCHAR(20),

postalCode VARCHAR(10),

country VARCHAR(20)

)

2)

The following example shows a table definition with default column values and column constraints:

CREATE TABLE orders

DESCRIPTION 'Master sales orders table'

(

orderID AUTOINC NOT NULL PRIMARY KEY,

customerID INTEGER NOT NULL REFERENCES customers ( customerID ),

orderDate DATE DEFAULT CURRENT_DATE,

shippingDate DATE,

freight DECIMAL(0,2) DEFAULT 0,

orderTotal DECIMAL(0,2) DEFAULT 0,

amountPaid DECIMAL(0,2) DEFAULT 0

)

3)

The following example shows a table definition with named column and table constraints:

CREATE TABLE order_details

(

orderID INTEGER NOT NULL CONSTRAINT uc_orderID UNIQUE,

productID AUTOINC NOT NULL,

unitPrice DECIMAL(0,4) DEFAULT 0,

quantity SMALLINT DEFAULT 0,

discount FLOAT DEFAULT 0,

CONSTRAINT pk_orderID_productID PRIMARY KEY ( orderID, productID ),

CONSTRAINT fk_orders_orderID FOREIGN KEY ( orderID ) REFERENCES orders ( orderID )

ON UPDATE CASCADE ON DELETE RESTRICT,

CONSTRAINT ck_discount CHECK ( discount >= 0 )

)

4)

The following example illustrates how the LIKE clause is used in a table definition:

/*

The LOCAL TEMPORARY keywords have been included for syntax clarity.

What's actually defining the table as a session-local temporary table in NexusDB are the two pound signs prefixing the table name.

*/

CREATE LOCAL TEMPORARY TABLE ##students

(

LIKE students,

dob DATE,

notes CLOB LOCALE 1033, -- A collation is explicitly specified for this column

primary key ( studentID )

)

5)

The following example illustrates how the AS subquery clause is used in a table definition:

/*

The three pound signs prefixing the table name defines the table as a global temporary table.

The GLOBAL TEMPORARY keywords have been omitted, but could have been specified for syntax clarity.

*/

CREATE TABLE ###students_list

( studentID, name, city, gender ) AS

( SELECT studentID, studentName, city, gender FROM students )

WITH DATA

Conformance

SQL:2003 standard

NexusDB extensions

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

Core SQL

Feature F692 "Enhanced collation support"

Feature T171 "LIKE clause in table definition"

Feature T172 "AS subquery clause in table definition"

Feature F531 "Temporary tables"

Rows of temporary tables are implicitly preserved

Feature F491 "Constraint management"

Feature T591 "UNIQUE constraints of possibly null columns"

Feature F191 "Referential delete actions"

Feature F701 "Referential update actions"

Feature T191 "Referential action RESTRICT"

Feature T201 "Comparable data types for referential constraints"

Feature F671, "Subqueries in CHECK constraints"

Feature F672 "Retrospective check constraints"

DESCRIPTION

BLOCKSIZE | BLOCK

INITIALSIZE | INITIAL

GROWSIZE | GROW

STORAGE [ ENGINE ]

ENCRYPTION [ ENGINE ]

ENCRYPT WITH

DEFAULT CHARACTER SET

DEFAULT COLLATION

NEWGUID

#table-name defines a context-local temporary table

##table-name defines a session-local temporary table

###table-name defines a global temporary table


CREATE INDEX statement

CREATE INDEX statement

Define a primary or secondary index.

Syntax

<index definition> ::=

CREATE [ UNIQUE ] INDEX index-name ON [ schema-name. ] table-name

( <index element> [ { , <index element> }... ] )

<index element> ::= column-name <sort options>

<sort options> ::=

[ <collate clause> ]

[ IGNORE CASE ]

[ <sort direction> ]

[ <null ordering> ]

<sort direction> ::= { ASC | DESC }

<null ordering> ::= NULLS { FIRST | LAST }

Usage

The CREATE INDEX statement creates a compiled index based on the columns and sort options of the specified index elements. Indexes are used to optimize search and filter conditions, and it is generally recommended to define indexes on all columns that are used as keys in JOIN and WHERE clause criteria. The speed of grouping and ordering operations will also improve greatly if the columns in the GROUP BY and ORDER BY clauses are supported by matching indexes.

Notes

§

The index, schema and table names shall conform to the format rules for identifiers and schema-qualified names in NexusDB SQL. The current database is implicit if a schema name is not specified.

§

If UNIQUE is specified, then an UNIQUE constraint is implicitly defined for the table.

§

The collate clause is used to specify a Windows OS collation for character string and national character string index columns. If the clause is omitted, then the explicit or implicit collation in the column definition is used for the sort order of the index key.

§

IGNORE CASE makes the sort order case-insensitive.

§

ASC is implicit if a sort direction is not specified. Specify DESC to sort the index key in descending order.

§

NULLS FIRST sorts null values before all non-null values in the set, and is implicit if the null ordering clause is not specified. Specify NULLS LAST to have null values sorted at the bottom of the set.

Examples

1)

The following example creates a compound unique index on the sections table:

CREATE UNIQUE INDEX ix_courseID_teacherID ON sections ( courseID, teacherID )

2)

The following example creates a case-insensitive index on the students table with an explicit collation for the studentName column:

CREATE INDEX ix_studentName ON students ( studentName locale 1033 IGNORE CASE )

Conformance

NexusDB extensions

-

CREATE INDEX statement


CREATE VIEW statement

CREATE VIEW statement

Define a persistent viewed table.

Syntax

<view definition> ::=

CREATE VIEW [ schema-name. ] view-name <view specification> AS <query expression>

<view specification> ::= [ ( column-name [ { , column-name }... ] ) ]

Usage

The CREATE VIEW statements creates a persistent view in the database based on the query expression. Views are a useful mechanism for defining complex queries once and to use them repeatedly in SQL statements. Views can also be used to enhance database security, for example by exposing only parts of a base table to other users.

Notes

§

The schema and view names shall conform to the format rules for identifiers and schema-qualified names in NexusDB SQL. The view is created in the database specified by schema-name. The current database is implicit if a schema name is not specified.

§

The optional view specification can be used to rename the columns processed by the query expression. If view specification is specified, then the degree shall be equal to the degree of the query expression.

§

All table references in the subquery shall identify persistent base tables or views.

§

Only the metadata about the view is stored in the database. The data itself is processed from the base tables when the view is referenced in SQL statements.

§

Once created, views can be queried just like ordinary base tables.

§

Views are read-only.

Examples

1)

The following example creates a view that exposes only some of the columns in the Teachers base table:

CREATE VIEW teachers_view AS (

SELECT teacherID, teacherName, phone

FROM teachers

)

2)

The following example references teachers_view in a SELECT statement:

SELECT courseID, teacherID, teacherName

FROM sections s

JOIN teachers_view t on t.teacherID = s.teacherID

ORDER BY courseID

Conformance

SQL:2003 standard

-

-

Core SQL

Views are read-only


CREATE TRIGGER statement

CREATE TRIGGER statement

Define a trigger.

Syntax

<trigger definition> ::=

CREATE TRIGGER [ schema-name. ] trigger-name

{ BEFORE | AFTER } <trigger event> [ { , <trigger event> }... ]

ON [ schema-name. ] table-name [ REFERENCING <transition variable>... ]

[ DESCRIPTION <character string literal> ]

[ AS ] <triggered action>

<trigger event> ::=

|

|

INSERT

DELETE

UPDATE [ OF <trigger column list> ]

<trigger column list> ::= column-name [ { , column-name }... ]

<transition variable> ::=

|

OLD [ ROW ] [ AS ] correlation-name

NEW [ ROW ] [ AS ] correlation-name

<triggered action> ::=

[ FOR EACH { ROW } ]

[ WHEN ( <search condition> ) ]

<triggered SQL statement>

<triggered SQL statement> ::= <SQL procedure statement>

Usage

The CREATE TRIGGER statement creates a trigger that is stored in the database and associated with the subject table identified by table-name.

Notes

§

The schema and trigger names shall conform to the format rules for identifiers and schema-qualified names in NexusDB SQL. The trigger is created in the database specified by schema-name, which must be the same database as where the associated table is stored. The current database is implicit if a schema name is not specified.

§

The subject table of the trigger definition shall be a persistent base table.

§

The BEFORE and AFTER keywords define whether the trigger is fired immediately before or immediately after the effects of the triggering SQL statement are applied to the table.

§

The trigger event specifies which data-change statements that will cause the trigger to fire. The trigger event can specify INSERT, DELETE or UPDATE, or any combination of the event types separated by a comma. If the optional OF clause is specified for an UPDATE event, then the event is only triggered when the specified columns are updated.

§

The REFERENCING clause can be specified to give correlation names to the OLD and NEW transition variables. OLD is a reference to the original row values that existed before an UPDATE or DELETE operation. NEW is a reference to the row values that will exist after an INSERT or UPDATE operation. The NEW variable can be used in a BEFORE trigger to assign column values to the new or modified row, else both variables are read-only.

§

DESCRIPTION is a free text attribute used to store a comment in the trigger descriptor.

§

FOR EACH ROW, which is implicit if not specified, means that the trigger is fired for each row being processed by a data-change statement.

§

The optional WHEN clause is used to specify a condition that must evaluate to true before the triggered SQL statement is executed.

§

The triggered SQL statement can be any valid SQL procedure statement, including the Compound statement that allows multiple SQL statements to be specified.

§

The special INSERTING, UPDATING and DELETING predicates can be referenced in the WHEN clause or the triggered SQL statement to check the current trigger event type.

Examples

1)

The following example creates a trigger that deletes related rows in the order_details table after a row in the orders table has been deleted:

CREATE TRIGGER orders_after_delete

AFTER DELETE ON orders

WHEN ( DELETING )

DELETE FROM order_details WHERE orderID = OLD.orderID;

2)

The following example creates a multi-event trigger that stores information about changes to the enrolls table in the enrolls_log table:

CREATE TRIGGER enrolls_changes

AFTER INSERT, DELETE, UPDATE ON enrolls

REFERENCING OLD AS o NEW AS n

BEGIN

IF INSERTING THEN

INSERT INTO enrolls_log ( action, n1, n2, n3, n4, stamp )

VALUES ( 'Insert', n.courseID, n.sectionID, n.studentID, n.grade, CURRENT_TIMESTAMP );

ELSEIF UPDATING THEN

INSERT INTO enrolls_log ( action, o1, n1, o2, n2, o3, n3, o4, n4, stamp )

VALUES ('Update', o.courseID, n.courseID, o.sectionID, n.sectionID,

o.studentID, n.studentID, o.grade, n.grade, CURRENT_TIMESTAMP );

ELSEIF DELETING THEN

INSERT INTO enrolls_log ( action, o1, o2, o3, o4, stamp )

VALUES ( 'Delete', o.courseID, o.sectionID, o.studentID, o.grade, CURRENT_TIMESTAMP );

END IF;

END

Conformance

SQL:2003 standard

NexusDB extensions

-

-

-

-

Feature T211 "Basic trigger capability"

DESCRIPTION

More than one trigger event can be specified

AS clause before the triggered action specification.


CREATE PROCEDURE statement

CREATE PROCEDURE statement

Define an SQL-invoked procedure.

Syntax

<user-defined procedure> ::=

CREATE PROCEDURE [ schema-name. ] procedure-name

<SQL parameter declaration list>

<routine characteristics>

[ DESCRIPTION <character string literal> ]

[ AS ] <routine body>

Usage

The CREATE PROCEDURE statement creates a user-defined procedure that is stored in the database.

Notes

§

The schema and procedure names shall conform to the format rules for identifiers and schema-qualified names in NexusDB SQL. The procedure is created in the database specified by schema-name. The current database is implicit if a schema name is not specified.

§

SQL parameter declaration

§

Procedure parameters can be specified with a parameter mode of IN, OUT or INOUT. In is implicit if a parameter mode is not specified.

§

SQL parameter names shall conform to the rules for identifiers in NexusDB SQL.

§

Routine characteristics

§

LANGUAGE SQL is implicit if not specified. LANGUAGE CLR defines a CLR routine.

§

NOT DETERMINISTIC is implicit if neither DETERMINISTIC nor NOT DETERMINISTIC is specified.

§

If an SQL-data access indication is not specified, then CONTAINS SQL is implicit for SQL routines and NO SQL is implicit for CLR routines. The procedure cannot be called by another routine with a lower SQL-data access level.

§

The null-call clause shall not be specified in a procedure definition.

§

DESCRIPTION is a free text attribute used to store a comment in the procedure descriptor.

§

Routine body

SQL procedures shall specify a SQL routine body, and CLR procedures shall specify an external body reference.

An SQL procedure can return a cursor by specifying a SELECT statement as the last statement in the routine body.

Examples

1)

The following example creates a procedure that inserts a new row in the courses table with values passed through four parameters:

CREATE PROCEDURE addCourse

(

// Parameter declarations with implicit IN mode

p_courseID SMALLINT,

p_courseName CHAR(20),

p_department CHAR(20),

p_numCredits TINYINT

)

MODIFIES SQL DATA -- We need write-access to update the courses table

// We prefer to use a compound statement in the routine body, even with a single statement

BEGIN

INSERT INTO courses

VALUES ( p_courseID, p_courseName, p_department, p_numCredits );

END

2)

The following example creates a procedure that raises all teacher salaries by a given percent and uses an OUT parameter to signal whether or not the transaction succeeded:

CREATE PROCEDURE raiseSalaries

(

IN percent FLOAT,

OUT done BOOLEAN

)

/*

LANGUAGE SQL is implicit

NOT DETERMINISTIC is implicit

*/

MODIFIES SQL DATA -- We need write-access to update the teachers table

// Multiple statements in the routine body must appear inside a compound statement

BEGIN

DECLARE rate FLOAT;

SET rate = 1 + ( percent / 100 );

START TRANSACTION;

TRY

UPDATE teachers SET salary = ROUND( salary * rate );

COMMIT;

SET done = TRUE;

CATCH ( TRUE )

ROLLBACK;

SET done = FALSE;

END;

END

3) The following example creates a procedure that returns a cursor derived from the students table:

CREATE PROCEDURE studentNames

( IN isFullName BOOLEAN )

READS SQL DATA -- We need read-access to execute the SELECT statement

BEGIN

IF isFullName THEN

SELECT lastName, firstName FROM students;

ELSE

SELECT firstName FROM students;

END IF;

END

Conformance

SQL:2003 standard

NexusDB extensions

-

-

-

-

Core SQL

Feature B128 "Routine language SQL"

DESCRIPTION

AS clause before the routine body


CREATE FUNCTION statement

CREATE FUNCTION statement

Define an SQL-invoked function.

Syntax

<user-defined function> ::=

CREATE FUNCTION [ schema-name. ] function-name

<SQL parameter declaration list>

RETURNS { <data type> | TABLE }

<routine characteristics>

[ DESCRIPTION <character string literal> ]

[ AS ] <routine body>

Usage

The CREATE FUNCTION statement creates a user-defined function that is stored in the database.

Notes

§

The schema and procedure names shall conform to the format rules for identifiers and schema-qualified names in NexusDB SQL. The procedure is created in the database specified by schema-name. The current database is implicit if a schema name is not specified.

§

SQL parameter declaration

§

A parameter mode shall not be specified. All function parameters are implicitly IN parameters.

§

SQL parameter names shall conform to the rules for identifiers in NexusDB SQL.

§

The RETURNS clause can either specify the data type of a scalar-valued function result, or TABLE to define a table-valued function that returns a cursor.

§

Routine characteristics

§

LANGUAGE SQL is implicit if not specified. LANGUAGE CLR defines a CLR routine.

§

NOT DETERMINISTIC is implicit if neither DETERMINISTIC nor NOT DETERMINISTIC is specified.

§

If an SQL-data access indication is not specified, then CONTAINS SQL is implicit for SQL routines and NO SQL is implicit for CLR routines. The function cannot be called by another routine with a lower SQL-data access level.

§

CALLED ON NULL INPUT is implicit if the null-call clause is not specified.

§

DESCRIPTION is a free text attribute used to a store comment in the function descriptor.

§

Routine body

SQL functions shall specify a SQL routine body, and CLR functions shall specify an external body reference.

The RETURN statement is required in an SQL routine body to specify the result of the function.

The RETURN clause of a table-valued SQL function shall specify a SELECT statement.

Examples

1)

The following example creates a function that concatenates two character string values, separated by a space, to form a full name:

CREATE FUNCTION getFullName

(

firstName VARCHAR(30),

lastName VARCHAR(30)

)

RETURNS VARCHAR(61)

/*

LANGUAGE SQL is implicit

NOT DETERMINISTIC is implicit

CONTAINS SQL is implicit

*/

RETURNS NULL ON NULL INPUT -- We don't invoke the function if any of the arguments are null

// Multiple statements in the routine body must appear inside a compound statement

BEGIN

DECLARE name VARCHAR(61);

SET name = firstName || ' ' || lastName;

RETURN name;

END

2)

The following example creates a function that returns the system date and time:

CREATE FUNCTION getSystemTime ()

RETURNS TIMESTAMP

// A single statement in the routine body doesn't need to appear inside a compound statement

RETURN CURRENT_TIMESTAMP;

3)

The following example creates a function that returns a cursor derived from the students table:

CREATE FUNCTION getStudents ()

RETURNS TABLE

READS SQL DATA -- We need read-access to execute the SELECT statement

// We prefer to use a compound statement in the routine body, even with a single statement

BEGIN

RETURN SELECT * FROM students;

END

Conformance

SQL:2003 standard

NexusDB extensions

-

-

-

-

-

Core SQL

Feature B128 "Routine language SQL"

Feature T326 "Table functions"

DESCRIPTION

AS clause before the routine body


CREATE ASSEMBLY statement

CREATE ASSEMBLY statement

Define an assembly registration.

Syntax

<assembly definition> ::=

CREATE [ SCRIPT | NET ] ASSEMBLY assembly-name

[ AUTHORIZATION owner-name ]

FROM <assembly specifier>

<assembly specifier> ::=

|

<quote> [ \\computer-name\ ] share-name\ [ path\ ] manifest-file-name <quote>

<quote> [ local_path\ ] manifest-file-name <quote>

Usage

The CREATE ASSEMBLY statement registers a .NET assembly on the NexusDB Server. After registration, the methods contained in the assembly can be referenced by user-defined CLR procedures and functions.

Notes

§

The assembly name shall conform to the format rules for identifiers in NexusDB SQL. The name must be different from other registered assembly names, and is the name used by the server to identify the assembly when invoking user-defined CLR routines.

§

The AUTHORIZATION clause specifies the name of a valid user or role. If the clause is not specified, then ownership is given to the current user.

§

The FROM clause specifies the local path or network location where the assembly is located, and the manifest file name that corresponds to the assembly.

Note: The CREATE ASSEMBLY and DROP ASSEMBLY statements are supported in the Enterprise Edition only.

Examples

1)

The following example registers an assembly located in a local folder on the NexusDB Server computer:

CREATE ASSEMBLY dotNET_routines

FROM 'C:\Program Files\NexusDB\Assemblies\DBFuncs.dll'

Conformance

NexusDB extensions

-

CREATE ASSEMBLY statement


ALTER TABLE statement

ALTER TABLE statement

Change the definition of a table.

Syntax

<alter table statement> ::=

ALTER TABLE [ schema-name. ] table-name <alter table action>

<alter table action> ::=

|

|

|

|

|

SET DESCRIPTION <character string literal>

<add column definition>

<alter column definition>

<drop column definition>

<add table constraint definition>

<drop table constraint definition>

<add column definition> ::= ADD [ COLUMN ] <column definition>

<alter column definition> ::=

ALTER [ COLUMN ] column-name <alter column action>

<alter column action> ::=

|

|

|

|

|

SET <default clause>

DROP DEFAULT

ADD CONSTRAINT NOT NULL

DROP CONSTRAINT NOT NULL

SET DESCRIPTION <character string literal>

CAST [AS] <data type> [ WITH BESTFIT ]

<drop column definition> ::= DROP [ COLUMN ] column-name [ RESTRICT ]

<add table constraint definition> ::= ADD <table constraint definition>

<drop table constraint definition> ::= DROP CONSTRAINT [ IF EXISTS ] constraint-name [ RESTRICT ]

Usage

The ALTER TABLE statement is used to change the definition of an existing table in the database, and requires exclusive access to that table.

Notes

§

The current database is implicit if a schema name is not specified.

§

Use the SET DESCRIPTION clause to change the content of the table description attribute.

§

Use the ADD COLUMN clause to add a new column to the table.

§

Use the ALTER COLUMN clause to change one of the following column attributes:

§

DEFAULT value

§

NOT NULL constraint

§

DESCRIPTION

§

Use the DROP COLUMN clause to remove a column from the table. RESTRICT, which is implicit if not explicitly specified, prevents the column from being removed if the column is referenced by other database objects.

§

Use the ADD table constraint clause to add a PRIMARY KEY, UNIQUE or FOREIGN KEY constraint to the table.

§

Use the DROP CONSTRAINT clause to remove a table constraint.

§

The IF EXISTS clause can be specified to avoid an exception when attempting to delete a constraint that does not exist.

§

RESTRICT, which is implicit if not explicitly specified, prevents a PRIMARY KEY or UNIQUE constraint from being removed if the constraint is referenced by a FOREIGN KEY constraint in another table.

§

The ALTER TABLE statement requires exclusive access to the table being altered.

Examples

1)

The following example adds a new column to the students table:

ALTER TABLE students

ADD COLUMN picture IMAGE

2)

The following example adds a NOT NULL constraint to the lastName column of the students table:

ALTER TABLE students

ALTER COLUMN lastName

ADD CONSTRAINT NOT NULL

3)

The following example removes the picture column from the students table:

ALTER TABLE students

DROP COLUMN picture

4)

The following example adds a FOREIGN KEY constraint to the enrolls table:

ALTER TABLE enrolls

ADD CONSTRAINT fk_students_studentID FOREIGN KEY ( studentID ) REFERENCES students ( studentID )

5)

The following example removes the constraint named fk_students_studentID from the enrolls table:

ALTER TABLE enrolls

DROP CONSTRAINT fk_students_studentID

Conformance

SQL:2003 standard

NexusDB extensions

-

-

-

-

-

-

Core SQL

Feature F381 "Extended schema manipulation"

ADD CONSTRAINT NOT NULL

DROP CONSTRAINT NOT NULL

SET DESCRIPTION

IF EXISTS clause


DROP TABLE statement

DROP TABLE statement

Delete a table.

Syntax

<drop table statement> ::=

DROP TABLE [ IF EXISTS ] [ schema-name. ] table-name [ RESTRICT ]

Usage

The DROP TABLE statement deletes the table specified by table-name from the database.

Notes

§

The current database is implicit if a schema name is not specified.

§

The IF EXISTS clause can be specified to avoid an exception when attempting to delete a table that does not exist in the database.

§

Dropping a table automatically deletes all indexes and triggers owned by the table.

§

RESTRICT, which is implicit and the only supported drop behavior, prevents the table from being deleted if the table is referenced by other tables, views, triggers, procedures or functions.

§

The DROP TABLE statement requires exclusive access to the table being deleted.

Examples

1)

The following example deletes the ##students table:

DROP TABLE IF EXISTS ##students

Conformance

SQL:2003 standard

NexusDB extensions

-

-

Core SQL

IF EXISTS clause


DROP INDEX statement

DROP INDEX statement

Delete an index.

Syntax

<drop index statement> ::=

DROP INDEX [ IF EXISTS ] [ schema-name. ] table-name.index-name

Usage

The DROP INDEX statement deletes the index specified by index-name from the table.

Notes

§

The current database is implicit if a schema name is not specified.

§

The IF EXISTS clause can be specified to avoid an exception when attempting to delete an index that does not exist.

§

Deleting a UNIQUE index will automatically remove the associated table constraint.

§

The DROP INDEX statement requires exclusive access to the table on which the index being deleted is defined.

Examples

1)

The following example deletes the ix_studentName index from the students table:

DROP INDEX IF EXISTS students.ix_studentName

Conformance

NexusDB extensions

-

-

DROP INDEX statement

IF EXISTS clause


DROP VIEW statement

DROP VIEW statement

Delete a view.

Syntax

<drop view statement> ::=

DROP VIEW [ IF EXISTS ] [ schema-name. ] view-name [ RESTRICT ]

Usage

The DROP VIEW statement deletes the view specified by view-name from the database.

Notes

§

The current database is implicit if a schema name is not specified.

§

The IF EXISTS clause can be specified to avoid an exception when attempting to delete a view that does not exist in the database.

§

RESTRICT, which is implicit if not explicitly specified, prevents the view from being deleted if the view is referenced by other database objects.

§

The DROP VIEW statement requires exclusive access to the view being deleted.

Examples

1)

The following example deletes the teachers_view view:

DROP VIEW IF EXISTS teachers_view

Conformance

SQL:2003 standard

NexusDB extensions

-

-

Core SQL

IF EXISTS clause


DROP TRIGGER statement

DROP TRIGGER statement

Delete a trigger.

Syntax

<drop trigger statement> ::=

DROP TRIGGER [ IF EXISTS ] [ schema-name. ] trigger-name

Usage

The DROP TRIGGER statement deletes the trigger specified by trigger-name from the database.

Notes

§

The current database is implicit if a schema name is not specified.

§

The IF EXISTS clause can be specified to avoid an exception when attempting to delete a trigger that does not exist in the database.

§

The DROP TRIGGER statement requires exclusive access to the subject table of the trigger definition.

Examples

1)

The following example deletes the enrolls_changes trigger:

DROP TRIGGER IF EXISTS enrolls_changes

Conformance

SQL:2003 standard

NexusDB extensions

-

-

-

Core SQL

Feature T211 "Basic trigger capability"

IF EXISTS clause


DROP ROUTINE statement

DROP ROUTINE statement

Delete a user-defined procedure or function.

Syntax

<drop routine statement> ::=

DROP <routine type> [ IF EXISTS ] [ schema-name. ] routine-name [ RESTRICT ]

<routine type> ::=

|

|

ROUTINE

FUNCTION

PROCEDURE

Usage

The DROP ROUTINE statement deletes the procedure or function specified by routine-name from the database.

Notes

§

The current database is implicit if a schema name is not specified.

§

ROUTINE can be specified to indicate that the routine type can be either procedure or function.

§

If PROCEDURE or FUNCTION is specified, then the routine being deleted must be the specified type.

§

The IF EXISTS clause can be specified to avoid an exception when attempting to delete a routine that does not exist in the database.

§

RESTRICT, which is implicit if not explicitly specified, prevents the routine from being deleted if the routine is referenced by other database objects.

§

The DROP ROUTINE statement requires exclusive access to the routine being deleted.

Examples

1)

The following example deletes the getStudents function:

DROP FUNCTION IF EXISTS getStudents

2)

The following example deletes the getStudents function using the ROUTINE keyword:

DROP ROUTINE IF EXISTS getStudents

3)

The following example deletes the studentNames procedure:

DROP PROCEDURE IF EXISTS studentNames

Conformance

SQL:2003 standard

NexusDB extensions

-

-

Core SQL

IF EXISTS clause


DROP ASSEMBLY statement

DROP ASSEMBLY statement

Remove an assembly registration.

Syntax

<drop assembly statement> ::=

DROP ASSEMBLY [ IF EXISTS ] assembly-name

Usage

The DROP ASSEMBLY statement removes the specified assembly registration on the NexusDB Server.

Notes

§

The IF EXISTS clause can be specified to avoid an exception when attempting to delete an assembly that is not registered on the NexusDB Server.

§

The DROP ASSEMBLY statement returns an error if the assembly is referenced by a CLR routine.

Note: The CREATE ASSEMBLY and DROP ASSEMBLY statements are supported in the Enterprise Edition only.

Examples

1)

The following example removes the registration of DBFuncs.dll which has been registered with the name dotNET_routines:

DROP ASSEMBLY IF EXISTS dotNET_routines

Conformance

NexusDB extensions

-

-

DROP ASSEMBLY statement

IF EXISTS clause


Data Statements

Data Statements

The data statements, the part of the SQL language commonly known as DML, Data Manipulation Language, are used to retrieve, insert, update and delete data in the database.

SELECT statement

INSERT statement

UPDATE statement

DELETE statement


SELECT statement

SELECT statement

Specify a cursor that retrieves a selection of rows from one or more tables.

While the full syntax is rather complex, the main syntactical elements can be simplified as:

SELECT select-list

[ INTO table-name ]

FROM table-reference

[ WHERE search-condition ]

[ GROUP BY grouping-element ]

[ HAVING search-condition ]

[ ORDER BY sort-specification

Syntax

<select statement> ::= <cursor specification>

<cursor specification> :=

<query expression>

[ <order by clause> ]

<query expression> ::=

|

|

<query term>

<query expression> UNION [ ALL | DISTINCT ] <query term>

<query expression> EXCEPT [ ALL | DISTINCT ] <query term>

<query term> ::=

|

|

<query specification>

( <query expression> )

<query term> INTERSECT [ ALL | DISTINCT ] <query expression>

<query specification> ::=

SELECT [ DISTINCT | ALL ] [ TOP n [ PERCENT ] [ { , start-position } ] ] <select list>

[ INTO table-name ]

<table expression>

<select list> ::=

|

*

<select sublist> [ { , <select sublist> }... ]

<select sublist> ::=

|

derived-column [ [ AS ] column-alias ]

qualified-asterisk

<table expression> ::=

<from clause>

[ <where clause> ]

[ <group by clause> ]

[ <having clause> ]

<from clause> := FROM <table reference> [ { , <table reference> }... ]

<table reference> ::=

|

<table factor>

<joined table>

<table factor> ::=

|

|

[ schema-name. ] table-name [ [ AS ] correlation-name [ ( <derived column list> ) ] ]

<table subquery> [ AS ] correlation-name [ ( <derived column list> ) ]

<table function derived table> [ AS ] correlation-name [ ( <derived column list> ) ]

<derived column list> ::= column-alias [ { , column-alias }... ]

<table function derived table> ::= TABLE ( <routine invocation> )

<joined table> ::=

|

|

<cross join>

<qualified join>

<natural join>

<cross join> ::= <table reference> CROSS JOIN <table factor>

<qualified join> ::= <table reference> [ <join type> ] JOIN <table reference> <join specification>

<natural join> ::= <table reference> NATURAL [ <join type> ] JOIN <table factor>

<join type> ::=

|

|

|

INNER

LEFT [ OUTER ]

RIGHT [ OUTER ]

FULL [ OUTER ]

<join specification> ::=

|

ON <search condition>

USING ( column-name [ { , column-name }... ] )

<where clause> ::= WHERE <search condition>

<group by clause> ::= GROUP BY <grouping element> [ { , <grouping element> }... ]

<grouping element> ::= column-reference <collate clause>

<having clause> ::= HAVING <search condition>

<order by clause> ::= ORDER BY <sort specification> [ { , <sort specification> }... ]

<sort specification> ::= column-reference <sort options>

Live and static cursors

NexusDB supports both live (updateable) and static (read-only) cursors as the result of executing a SELECT statement. A live cursor is effectively a direct cursor on the underlying base table referenced in the table expression, with a filter set to the source columns specified in the select list and the rows resulting from evaluating the query specification. A SELECT statement will return a static result set (a special in-memory table) if the query expression does not satisfy the requirements of a live cursor, or the client access software executing the query does not support live cursors.

The requirements for a live cursor are as follows:

§

The FROM clause shall reference a single source table.

§

A DISTINCT quantifier shall not be specified.

§

A TOP quantifier shall not be specified.

§

A GROUP BY clause shall not be specified.

§

If an ORDER BY clause is specified, then a supporting index that includes the same columns and sort options must exist.

Table operators

§

The UNION, EXCEPT and INTERSECT set operators are implicitly DISTINCT if no set quantifier is specified. The result of a table operation with DISTINCT implied or specified contains no duplicate rows.

§

The UNION operator is used to combine the tables derived from two query expressions. The two tables shall have the same number of columns (degree), and each corresponding column shall have the same ordinal position and a compatible data type. The result of an UNION operation is a combination of the two tables without duplicate rows. If ALL is specified, then the duplicate rows are preserved.

§

The EXCEPT operator is used to return all rows in the first table except those that also appear in the second table. If ALL is specified, then the number of duplicates of any specific row is equal to the number of such duplicates in the first table less the number of duplicates in the second table.

§

The INTERSECT operator is used to return rows that appear in both tables. If ALL is specified, then the number of duplicates of any specific row is equal to the lesser number of such duplicates in the two tables.

§

INTERSECT has a higher precedence than UNION and EXCEPT which are executed left to right.

§

The data type of columns in the result of a query expression with UNION, EXCEPT and INTERSECT is determined according to the rules for Result data types of aggregations.

Joined tables

NexusDB supports all common types of joins between tables. While the old-style join syntax with a comma-separated list of table names in the FROM clause and the join criteria contained in the WHERE clause, is still supported in SQL, we recommend the use of the new syntax introduced in SQL-92 and carried on by the latest revisions of the standard.

§

The CROSS join is effectively a cross-product of the two tables, commonly referred to as the Cartesian product, containing all possible combinations of rows from the first and second tables. Like its equivalent, the classic comma-separated join, a more meaningful result can be obtained by specifying a join criteria in the WHERE clause.

§

A NATURAL join is based on all columns in the referenced tables with the same name, and retrieves the rows that have equal values in the corresponding columns. If there are no corresponding columns, the result is a cross-join between the two tables.

§

A qualified join connects two tables based on an explicit join specification. The join specification can be expressed as a condition join with the ON keyword followed by a search condition, or as a column name join with the USING keyword followed by a list of corresponding columns.

§

The INNER keyword is implicit if no join type is specified. An INNER JOIN retrieves only the rows that match the join criteria. The OUTER keyword is implicit for outer joins, but can optionally be included for clarity. A LEFT OUTER JOIN will preserve all rows from the left table with null values in the columns of non-matching rows that correspond to the right table. A RIGHT OUTER JOIN is similar to a LEFT JOIN, except that the right table has its rows preserved. Finally, a FULL OUTER JOIN will preserve the rows from both tables.

SELECT clause

§

The select list specifies the source columns to be retrieved from the table expression and additional columns computed as the result of a value expression.

§

An asterisk (*) is a short-hand syntax for specifying all columns that are part of the table expression. An asterisk qualified by a table name specifies all the fields from that particular table.

§

A derived-column can be a column reference, optionally qualified by the table name, or any other valid value expression, including aggregate functions, value functions, user-defined functions, scalar subqueries, and case expressions.

§

The optional AS column-alias clause is used to give computed columns meaningful names or to rename source columns.

§

The ALL keyword is implicit if no set quantifier is specified. If DISTINCT is specified, then all duplicate rows are removed from the result set.

§

The TOP quantifier is used to restrict the result set to the number of rows specified by the n argument, or a percentage of all rows in the result set if the n argument is followed by the PERCENT keyword. The optional start-position argument indicates the position of the first row to be included in the result set.

Tip: A syntactical way of retrieving the last n rows, is to ORDER BY a descending sort specification using the TOP quantifier.

INTO clause

§

The INTO clause is used to create a new table with columns according to the select list and data resulting from executing the query.

§

Each column in the new table has the same name, data type, ordinal position and row values as the corresponding columns in the select list. Computed columns in the select list will be ordinary columns in the new table, with the column values computed by the query execution.

§

If a table with the same name already exists in the database, then the old table will be implicitly dropped before the new table is created.

§

A new table without data can be created by having a FALSE condition in the WHERE clause.

§

Using the INTO clause to create a new table is an alternate syntax for the AS subquery clause in the CREATE TABLE statement.

FROM clause

§

The FROM clause specifies one or more source tables from which to retrieve rows. The name of base tables, views and user-defined functions may optionally be qualified by the schema name to reference such objects in databases other than the current, or just for syntax clarity.

§

A table reference can specify a base table, a view, a subquery, or a table-valued function.

§

A correlation name is required for source tables derived from a subquery or a user-defined function, but is optional for base tables and views.

§

A derived column list may be specified to rename the columns of the table reference. The number of columns in the list shall match the number of source columns contained in the table reference.

§

If two or more tables are referenced in the FROM clause without an explicit join or a WHERE clause joining the tables, the result will be a cross-join between the tables.

WHERE clause

§

The WHERE clause is a filter that is applied to the result of evaluating the FROM clause.

§

Only the rows that satisfy the criteria specified in the search condition are included in the result set.

§

The scope of the WHERE clause is the tables referenced in the FROM clause. Expression columns or renamed columns in the select list cannot be referenced in the WHERE clause by their column aliases.

GROUP BY clause

§

The effect of the GROUP BY clause is to partition the result of evaluating the table expression so far into one or more groups, such that for each grouping column, no two rows of any group have different values for the grouping column. Furthermore, no other group has the same value for the complete set of grouping columns.

§

Each group is represented in the result set by a single row with aggregate functions, if specified, applied to the group.

§

All non-aggregate source columns that are referenced in the select list shall be specified as grouping columns.

§

The GROUP BY clause may also specify source columns that are not referenced in the select list.

§

Each grouping column shall be a reference to a source column in the table expression.

HAVING clause

§

The HAVING clause works like the WHERE clause, except that the filter criteria is applied to the groups of rows in the grouped table resulting from evaluating the GROUP BY clause.

§

If no GROUP BY clause is specified, then the result of evaluating the preceding clause is considered a single group.

§

Columns referenced in the search condition shall be a grouping column, a source column contained in an aggregate function or an outer reference.

ORDER BY clause

§

The ORDER BY clause specifies the order of rows in the result set.

§

The optional sort options can be specified to override the default sort behavior of the individual columns.

§

The sort keys shall be a reference to columns in the select list.

§

If DISTINCT, GROUP BY, UNION, EXCEPT or INTERSECT is not specified in the main query expression, then the ORDER BY clause may also specify source columns that are not included in the select list.

§

Columns that are present in the select list can be referenced by the column name or the ordinal position.

Note: The option of referencing a column by its ordinal position has been deprecated, and may be removed in a future version of NexusDB SQL.

Examples

1)

The following example selects all students ordered by names:

SELECT studentID, studentName, gender

FROM students

ORDER BY lastName, firstName

2)

The following example selects either female or male students depending on the value of the gender parameter:

SELECT studentID, studentName, gender

FROM students

WHERE gender = :gender

ORDER BY lastName, firstName

3)

The following example selects a list of students with a computed column called nickName:

SELECT studentID, city || '-' || firstName AS nickName, lastName, gender

FROM students

ORDER BY lastName, firstName

4)

The following example computes how many students are enrolled in the available courses:

SELECT courseID, count( * ) AS total_enrolled

FROM enrolls

GROUP BY courseID

ORDER BY total_enrolled DESC

5)

The following example select courses with more than 5 students enrolled:

SELECT courseID, count( * ) AS total_enrolled

FROM enrolls

GROUP BY courseID

HAVING COUNT( * ) > 5

ORDER BY total_enrolled DESC

6)

The following example uses joins to select columns from three tables:

SELECT

e.courseID,

c.courseName,

s.studentName,

e.grade

FROM enrolls e

JOIN courses c ON c.courseID = e.courseID

JOIN students s USING ( studentID )

ORDER BY courseID

7)

The following example combines rows from two tables using the UNION ALL table operator:

SELECT orderID, orderDate, orderTotal, amountPaid

FROM orders_2002

UNION ALL

SELECT orderID, orderDate, orderTotal, amountPaid

FROM orders_2003

ORDER BY orderDate, orderID

8)

The following example uses a subquery as table reference:

SELECT studentID, studentName, city, gender

FROM ( SELECT * FROM students ) AS s

ORDER BY lastName, firstName

9)

The following example uses a table function as table reference:

SELECT studentID, studentName, city, gender

FROM TABLE( getStudents() ) AS s

ORDER BY lastName, firstName

Conformance

SQL:2003 standard

NexusDB extensions

-

-

-

-

-

-

-

-

-

-

Core SQL

Feature F302 "INTERSECT table operator"

Feature F304 "EXCEPT ALL table operator"

Feature F401 "Extended joined table"

Feature F591 "Derived tables"

Feature T326 "Table functions"

Feature T551 "Optional key words for default syntax"

TOP quantifier in SELECT clause

INTO clause in query specification

Sort options


INSERT statement

INSERT statement

Create new rows in a table.

Syntax

<insert statement> ::=

INSERT INTO [ schema-name. ] table-name <insert columns and source>

<insert columns and source> ::=

|

|

<from subquery>

<from constructor>

DEFAULT VALUES

<from subquery> ::=

[ ( column-name [ { , column-name }... ] ) ] <query expression>

<from constructor> ::=

[ ( column-name [ { , column-name }... ] ) ] <table value constructor>

<table value constructor> ::=

VALUES ( <row value expression> [ { , <row value expression> }... ] )

Usage

The INSERT statement is used to add new rows to the table identified by table-name.

Notes

§

The current database is implicit if schema-name is not specified.

§

There are two main variations of the INSERT statement, the first is using a query expression to add rows based on data from existing tables in the database, and the second is using a table value constructor to specify a single row or a comma-separated list of row values.

§

The DEFAULT VALUES clause is a third option that can be used to insert a new row with default values in every column.

§

The optional comma-separated list of insert columns is particularly useful when inserting rows into a wide table and only a few columns will have initial data. Columns that are not specified in the insert column list will get the default value if specified in the table definition, otherwise the null value.

§

The number of values in the inserting rows, whether retrieved by a query expression or specified in the VALUES clause, shall match the number of insert columns or, if the insert column list is omitted, the degree of the target table.

§

The values assigned in the VALUES clause can be any valid value expression, including value functions, user-defined functions, scalar subqueries, case expressions, DEFAULT and NULL.

§

Parameters can be used to represent values supplied by the application.

Tip: Using multiple row values in the VALUES clause is more efficient than executing a sequence of single row INSERT statements.

Examples

1)

The following example inserts a new row into the students table, only assigning values to some of the columns:

INSERT INTO students

( studentID, lastName, firstName , address, state, zip, gender)

VALUES ( 211, 'Smith', 'Joanne', NULL, 'QL', 45678, 'F' )

2)

The following example shows a generic INSERT statement for the students table using parameters to assign column values:

INSERT INTO students

VALUES (

:studentID,

:firstName || ' ' || :lastName,

:lastName,

:firstName,

:address,

:city,

:state,

:zip,

:gender

)

3)

The following example uses a subquery to insert all the rows of the students table into the ##students table:

DELETE FROM ##students;

INSERT INTO ##students

SELECT * FROM students

4)

The following example inserts two new rows into the documents table:

INSERT into documents ( documentID, title )

VALUES ( NEWGUID, 'NexusDB main features' ), ( NEWGUID, 'NexusDB special features' )

Conformance

SQL:2003 standard

-

-

Core SQL

Feature F222 "INSERT statement: DEFAULT VALUES clause"


UPDATE statement

UPDATE statement

Update rows of a table.

Syntax

<update statement: searched> ::=

UPDATE [ schema-name. ] table-name [ [ AS ] correlation-name ]

SET <set clause list>

[ WHERE <search condition> ]

<set clause list> ::=

column-name = <value expression> [ { , column-name = <value expression> }... ]

Usage

The UPDATE statement is used to modify the value of one ore more columns in a selection of rows in the table identified by table-name.

Notes

§

The current database is implicit if schema-name is not specified.

§

The SET clause is a comma-separated list of column assignments. The assigned value can be any valid value expression, including value functions, user-defined functions, scalar subqueries, case expressions, DEFAULT and NULL.

§

Parameters can be used to represent values supplied by the application.

§

The WHERE clause restricts the rows that are updated to those satisfying the search condition. If no WHERE clause is specified, all rows in the table are updated.

Tip: Using a prepared statement with parameters is more efficient than executing a sequence of individual UPDATE statements with explicit values.

Examples

1)

The following example uses literals and value expressions to update studentID 211:

UPDATE students

SET

studentName = firstName || ' ' || lastName,

address = '983 Park Avenue',

city = 'Boston',

state = ( SELECT state FROM students WHERE studentID = 473 ),

zip = '02169'

WHERE studentID = 211

2)

The following example shows a generic UPDATE statement for the students table using parameters to update column values and identify the row:

UPDATE students

SET

studentName = :firstName || ' ' || :lastName,

lastName = :lastName,

firstName = :firstName,

address = :address,

city = :city,

state = :state,

zip = :zip,

gender = :gender

WHERE studentID = :studentID

Conformance

SQL:2003 standard

-

Core SQL


DELETE statement

DELETE statement

Delete rows of a table.

Syntax

<delete statement: searched> ::=

DELETE FROM [ schema-name. ] table-name [ [ AS ] correlation-name ]

[ WHERE <search condition> ]

Usage

The DELETE statement is used to delete a selection of rows from the table identified by table-name.

Notes

§

The current database is implicit if schema-name is not specified.

§

The WHERE clause restricts the rows that are deleted to those satisfying the search condition.

§

If no WHERE clause is specified, then all the rows of the table are deleted.

Tip: A more efficient way of deleting all the rows in a table is to drop the table and then recreate it.

Examples

1) The following example deletes students who are not enrolled in any courses:

DELETE FROM students

WHERE studentID IN (

SELECT DISTINCT studentID

FROM students s

LEFT JOIN enrolls e ON e.studentID = s.studentID

WHERE e.studentID IS NULL

)

2)

The following example uses a parameter to identify the row to delete:

DELETE FROM students

WHERE studentID = :studentID

3)

The following example deletes all rows in the ##students table:

DELETE FROM ##students

Conformance

SQL:2003 standard

-

Core SQL


EXECUTE IMMEDIATE Statement

EXECUTE IMMEDIATE statement

Execute dynamic SQL statements from within triggers etc

Syntax

<ExecuteStatement> ::= EXECUTE IMMEDIATE <SimpleExpression>

Usage

Note: the example shows how to work with single quoted parts of the statement

declare s varchar(100);

set s = 'select ' + chr(39) + 'a string' + chr(39) + ' from #dummy';

execute immediate (s);


CURSOR Statements

DECLARE CURSOR Statement

OPEN Statement

FETCH Statement

CLOSE statement


CLOSE statement

CLOSE statement

Syntax

<CloseStatement> :== 'CLOSE' <identifier>

Usage

Closes a previously declared named cursor.


DECLARE CURSOR Statement

DECLARE CURSOR statement

Syntax

<DeclareStatement> ::= DECLARE <identifier> CURSOR FOR <SELECT statement>

Usage

Declares a named cursor. Example:

drop function if exists createquery;

create function createquery( tablename varchar( 100))

returns varchar( 32767)

reads sql data

begin

declare cRet varchar( 32767);

declare cSep varchar( 5);

declare myFieldname varchar( 32);

declare myFieldlength integer;

declare myFieldType varchar( 32);

declare nLengthOfOtherFields integer;

declare nRL integer;

set cRet = 'select ';

set cSep = '';

set nLengthOfOtherFields = 0;

declare aCursor cursor for select f.field_name, f.field_type_sql,

f.field_length

from #tables t, #fields f

where f.table_name = t.table_name

and lower(t.table_name) =

lower(tablename);

open aCursor;

fetch first from aCursor into myFieldName, myFieldType, myFieldLength;

while @@fetch_status = 0 do

if myFieldType = 'CHARACTER VARYING' then

set cRet = cRet || cSep || 'sum( coalesce( char_length( ' ||

myFieldname || '),0)) ';

set cSep = '+';

else

set nLengthOfOtherFields = nLengthOfOtherFields + myFieldLength;

end if;

fetch next from aCursor into myFieldName, myFieldType, myFieldLength;

end while;

close aCursor;

set cRet = cRet || '+' || tostring( nLengthOfOtherFields) ||'

SizeOfAllTheData ';

declare aCursor cursor for select record_length from #tables where

lower( table_name ) = lower( tablename );

open aCursor;

fetch first from aCursor into nRL;

set cRet = cRet || ', sum( ' || tostring( nRL ) || ' )

SizeOfAllocatedData ';

close aCursor;

set cRet = cRet || ' from ' || tablename || ';';

return cRet;

end;

select createquery( '<table name>' ) from #dummy;


FETCH Statement

Fetch values from a declared cursor.

Syntax

<FetchStatement> ::= FETCH [ ( CURRENT | NEXT | PRIOR | FIRST | LAST | ABSOLUTE <SimpleExpression> | RELATIVE <SimpleExpression> ) ] FROM <identifier>

Usage

See example in DECLARE CURSOR Statement topic


OPEN Statement

OPEN statement

Syntax

<OpenStatement> :== 'OPEN' <identifier>

Usage

Opens a previously declared named cursor.


Transaction Statements

Transaction Statements

Transaction statements are used to start and terminate explicit transactions.

A transaction asserts data integrity by demanding that all statements executed in the context of the transaction must complete successfully before the transaction can be committed, otherwise the transaction is aborted and no changes are updated in the database.

Note: If an explicit transaction is not active, then each SQL statement starts an implicit transaction which is automatically committed when the SQL statement terminates.

Tip: Use transaction blocks to speed up the execution of bulk data-change statements.

START TRANSACTION statement

COMMIT statement

ROLLBACK statement


START TRANSACTION statement

START TRANSACTION statement

Start a transaction.

Syntax

<start transaction statement> ::= START TRANSACTION [ <transaction mode> ]

<transaction mode> ::=

|

SNAPSHOT

SERIALIZABLE

Usage

The START TRANSACTION statement starts a new transaction in the current session.

Notes

§

An isolation level of SERIALIZABLE is implicit if a transaction mode is not specified.

§

SNAPSHOT defines a read-only transaction with SERIALIZABLE isolation level. Since SNAPSHOT transactions don't put any locks on the tables involved in the transaction, SERIALIZABLE transactions from other sessions may change data while the SNAPSHOT transaction is active.

§

All SQL statements between a START TRANSACTION statement and a COMMIT or ROLLBACK statement will be part of the transaction.

§

If an error occurs during the execution of any following SQL statements, then the transaction is aborted and no changes are updated in the database.

§

Schema statements (DDL) are executed under control of an internal transaction, and shall not be specified in the context of an explicit transaction.

Examples

1)

The following example starts a new transaction:

START TRANSACTION;

2)

The following example starts a new snapshot transaction:

START TRANSACTION SNAPSHOT;

Conformance

SQL:2003 standard

NexusDB extensions

-

-

-

Feature T241 "START TRANSACTION statement"

Transaction access mode is not supported.

SNAPSHOT


COMMIT statement

COMMIT statement

Commit a transaction.

Syntax

<commit statement> ::= COMMIT [ WORK ]

Usage

The COMMIT statement terminates the active transaction and makes all changes made during the transaction permanent in the database.

Notes

§

The COMMIT statement will return an error if no transaction is active.

Examples

1)

The following example commits the current transaction:

COMMIT;

Conformance

SQL:2003 standard

-

Core SQL


ROLLBACK statement

ROLLBACK statement

Cancel a transaction.

Syntax

<rollback statement> ::= ROLLBACK [ WORK ]

Usage

The ROLLBACK statement terminates the active transaction and cancels all changes made during the transaction.

Notes

§

The COMMIT statement will return an error if no transaction is active.

Examples

1)

The following example cancels the current transaction:

ROLLBACK;

2)

The following example shows the ROLLBACK statement used in the context of a TRY statement:

START TRANSACTION;

TRY

// Execute some data-change statements here.

COMMIT;

CATCH POSITION( 'Unable to open table' IN ERROR_MESSAGE ) <> 0

ROLLBACK;

SIGNAL ERROR_MESSAGE;

END;

Conformance

SQL:2003 standard

-

Core SQL


Session Statements

Session Statements

Session statements are used to set properties and default values of an SQL-session.

SET PASSWORDS statement

ALTER SYSTEMPASSWORD statement

CREATE ALIAS statement

ALTER ALIAS statement

DROP ALIAS Statement

USE Statement


SET PASSWORDS statement

SET PASSWORDS statement

Set one ore more passwords.

Syntax

<set passwords statement> ::=

SET PASSWORDS [ [ ADD | REMOVE ] password-list ]

Usage

The SET PASSWORDS statement is used to manage a list of encryption passwords for the current session. Tables that are encrypted with a password cannot be accessed in SQL statements unless the encryption password defined for the table is present in the list of passwords assigned to the session.

Notes

§

The password-list is a comma separated list of passwords.

§

SET PASSWORDS ADD password-list adds new passwords to the list.

§

SET PASSWORDS REMOVE password-list removes the passwords specified by password-list from the list.

§

SET PASSWORDS password-list sets the password list according to the argument, replacing any existing passwords.

§

SET PASSWORDS sets the password list to an empty string.

Examples

1)

The following example adds passwords to the password list of the current session:

SET PASSWORDS ADD 'pw100, pw101, pw102'

2)

The following example removes one of the existing passwords in the list:

SET PASSWORDS REMOVE 'pw101'

3)

The following example replaces the existing password list:

SET PASSWORDS 'pw103'

4)

The following example removes all passwords:

SET PASSWORDS

Conformance

NexusDB extensions

-

SET PASSWORDS statement


ALTER SYSTEMPASSWORD statement

ALTER SYSTEMPASSWORD statement

Add password to session.

Syntax

<alter systempassword statement> ::=

ALTER SYSTEMPASSWORD TO <character string literal>

Usage

The ALTER SYSTEMPASSWORD statement is used to change the password for the active session.


CREATE ALIAS statement

CREATE ALIAS statement

Create a new alias

Syntax

<CreateAliasStatement> :== CREATE ALIAS <SqlName> FROM <AliasPath>

Usage

The Create Alias statement is used to create a new alias.


ALTER ALIAS statement

ALTER ALIAS statement

Change name or path of an alias

Syntax

<AlterAliasStatement> :== " ALTER ALIAS" <SqlName> "SET" ( "PATH" "TO" <AliasPath> | 'NAME' "TO" <SqlName> )

Usage

The Alter Alias statement is used to change the path (file system folder) or name of an alias.


DROP ALIAS Statement

DROP ALIAS statement

Drops (removes) an alias

Syntax

<DropAliasStatement> ::= DROP ALIAS [ IF EXISTS ] <SqlName>

Usage

The Drop Alias statement is used to drop an alias.


USE Statement

USE statement

Changes the database in use to the new alias

Syntax

<UseStatement> ::= USE [<SqlName> | DEFAULT_ALIAS ]

Usage

The Use statement is used to switch the active database to the database of a different alias. Subsequent SQL operations will operate on the database in the new alias. To return to the original, use the USE DEFAULT_ALIAS statement.

--client started operations in 'LiveData' alias

USE 'backup'; --now operates on the 'backup' database

--...some operations...

USE DEFAULT_ALIAS; --now operates on 'LiveData' alias again


Control Statements

Control Statements

Control statements, the main part of the procedure language in NexusDB SQL, add computational completeness to the SQL language with the Compound statement, SQL variable declarations, conditional statements, iterating statements and error handling.

CALL statement

RETURN statement

Compound statement

DECLARE variable statement

SET statement

IF statement

ITERATE statement

LEAVE statement

REPEAT statement

WHILE statement

SIGNAL statement

TRY statement


CALL statement

CALL statement

Invoke a user-defined procedure.

Syntax

<call statement> ::= CALL <routine invocation>

Usage

The CALL statement is used to invoke a user-defined procedure.

Notes

§

The CALL statement can only invoke a procedure.

§

The number and types of arguments passed to the procedure call must match the SQL parameter declarations in the procedure definition.

Examples

1)

The following example invokes a user-defined procedure using dynamic parameters to pass values:

CALL addCourse( :courseID, :courseName, :department, :numCredits );

Conformance

SQL:2003 standard

-

Core SQL


RETURN statement

RETURN statement

Return a function result.

Syntax

<return statement> ::= RETURN <return value>

<return value> ::=

|

|

<value expression>

NULL

<cursor specification>

Usage

The RETURN statement is used to assign the return value of a user-defined function.

Notes

§

The RETURN statement can only appear in the routine body of a function definition.

§

The return value shall be compatible with the type specified in the RETURNS clause of the function definition.

§

The cursor specification option shall only be used if the RETURNS clause specify TABLE.

Examples

1)

The following example returns a character string value:

RETURN 'Hello World';

2)

The following example returns the system date and time:

RETURN CURRENT_TIMESTAMP;

3)

The following example returns a cursor derived from the students table:

RETURN SELECT * FROM students;

Conformance

SQL:2003 standard

NexusDB extension

-

-

Core SQL

Cursor specification as return value


Compound statement

Compound statement

Specify a statement block.

Syntax

<compound statement> ::=

BEGIN [ [ NOT ] ATOMIC ]

[ <local declaration list> ]

[ <SQL statement list> ]

END

<local declaration list> ::= { <SQL variable declaration> ; }...

<SQL statement list> ::= { <SQL procedure statement> ; }...

Usage

The Compound statement is used to specify a sequence of SQL procedure statements inside a BEGIN..END block.

Notes

§

NOT ATOMIC is implicit if neither ATOMIC nor NOT ATOMIC is specified.

§

If ATOMIC is specified, then an internal transaction is started before the statement block is executed. The transaction is committed only if all the statements in the block executed successfully, otherwise the transaction is rolled back.

§

All SQL variable declarations shall be specified before other SQL statements.

Examples

1)

The following example shows a Compound statement with parameter declarations:

DROP FUNCTION IF EXISTS dummyFunc;

CREATE FUNCTION dummyFunc ()

RETURNS DOUBLE PRECISION

BEGIN

DECLARE d1, d2 DOUBLE PRECISION;

SET d1 = 25;

SET d2 = 4;

RETURN d1 * d2;

END;

SELECT dummyFunc FROM #dummy;

Conformance

SQL:2003 standard

-

-

SQL/PSM Feature P002-01 "Compound statement"

Beginning and ending statement labels are not supported


DECLARE variable statement

DECLARE variable statement

Declare one or more SQL variables.

Syntax

<SQL variable declaration> ::=

DECLARE <SQL variable name list> <data type> [ <default clause> ]

<SQL variable name list> ::= variable-name [ { , variable-name }... ]

Usage

The DECLARE statement is used to declare SQL variables. SQL variables that are declared in the context of triggers, procedures and functions are local to the statement block where they are declared. SQL variables that are declared in the context of the main executing statement block are global session variables.

Notes

§

SQL variable names shall conform to the rules for identifiers in NexusDB SQL.

§

One ore more SQL variables can be declared with the same data type.

§

The DEFAULT clause is used to specify an initial value for the variable, and is similar to setting a default value for a column.

Examples

1)

The following examples declares two SQL variables with the same data type:

DECLARE min, max INTEGER;

2)

The following example declares two SQL variables with different data types:

DECLARE count INTEGER DEFAULT 0;

DECLARE total DOUBLE PRECISION DEFAULT 0;

Conformance

SQL:2003 standard

-

SQL/PSM Feature P002-04 "SQL variable declaration"


SET statement

Assignment statement

Assign a value to an assignment target.

Syntax

<assignment statement> ::=

SET <assignment target> = <assignment source>

<assignment target> ::= <target specification>

<assignment source> ::=

|

|

<value expression>

NULL

DEFAULT

Usage

The SET statement is used to assign values to assignment targets such as SQL variables, SQL parameters and columns of a transition table.

Notes

§

The data types of the assignment target and the assignment source shall be compatible.

Examples

1)

The following example assigns a value to the name SQL variable:

SET name = firstName || ' ' || lastName;

2)

The following example assigns a value to a column of the transition table pointed to by the NEW variable in the context of a trigger definition:

SET NEW.lastUpdated = CURRENT_TIMESTAMP;

Conformance

SQL:2003 standard

-

SQL/PSM Feature P002-05 "Assignment statement"


IF statement

IF statement

Specify a conditional execution.

Syntax

<if statement> ::=

IF <search condition>

<if statement then clause>

[ <if statement elseif clause>... ]

[ <if statement else clause> ]

END IF

<if statement then clause> ::= THEN <SQL statement list>

<if statement elseif clause> ::= ELSEIF <search condition> THEN <SQL statement list>

<if statement else clause> ::= ELSE <SQL statement list>

Usage

The IF statement is used to specify a conditional execution of one or more SQL statements, and is similar in functionality to conditional IF statements found in traditional programming languages.

Notes

§

The optional ELSEIF clause is used to specify additional conditions with separate statement blocks, and is a convenient shorthand for nesting multiple IF..THEN statements.

§

Only the statements contained in the first IF or ELSEIF clause with a condition that evaluates to true are executed.

§

An optional ELSE clause can be specified to execute one or more statements if none of the preceding conditions were true.

Examples

1)

The following example shows the IF statement used in the body of a trigger definition:

DROP TRIGGER IF EXISTS enrolls_changes;

CREATE TRIGGER enrolls_changes

AFTER INSERT, DELETE, UPDATE ON enrolls

REFERENCING OLD AS o NEW AS n

BEGIN

IF INSERTING THEN

INSERT INTO enrolls_log ( action, n1, n2, n3, n4, stamp )

VALUES ( 'Insert', n.courseID, n.sectionID, n.studentID, n.grade, CURRENT_TIMESTAMP );

ELSEIF UPDATING THEN

INSERT INTO enrolls_log ( action, o1, n1, o2, n2, o3, n3, o4, n4, stamp )

VALUES ('Update', o.courseID, n.courseID, o.sectionID, n.sectionID,

o.studentID, n.studentID, o.grade, n.grade, CURRENT_TIMESTAMP );

ELSEIF DELETING THEN

INSERT INTO enrolls_log ( action, o1, o2, o3, o4, stamp )

VALUES ( 'Delete', o.courseID, o.sectionID, o.studentID, o.grade, CURRENT_TIMESTAMP );

ELSE

SIGNAL 'An unknown trigger event was fired.';

END IF;

END

Conformance

SQL:2003 standard

-

SQL/PSM Feature P002-07 "IF statement"


ITERATE statement

ITERATE statement

Start a new iteration.

Syntax

<iterate statement> ::= ITERATE

Usage

The ITERATE statement can only be specified in the body of the REPEAT and WHILE statements to start a new iteration from the first statement in the block.

See also: LEAVE statement

Examples

1)

The following example shows the ITERATE statement used in SQL code:

DROP FUNCTION IF EXISTS dummyFunc;

CREATE FUNCTION dummyFunc( p1 INTEGER )

RETURNS INTEGER

BEGIN

DECLARE v1, v2 INTEGER;

SET v1 = 10;

SET v2 = 10;

WHILE v1 > 0 DO

SET v1 = v1 - 1;

IF v1 <= 5 THEN

ITERATE;

END IF;

SET v2 = v2 - 1;

END WHILE;

RETURN v2 + p1;

END;

SELECT dummyFunc( 15 ) FROM #dummy;

Conformance

SQL:2003 standard

-

-

SQL/PSM Feature P002-08 "ITERATE statement"

Statement label is not supported


LEAVE statement

LEAVE statement

Exit from an iterated statement.

Syntax

<leave statement> ::= LEAVE

Usage

The LEAVE statement can only be specified in the body of the REPEAT and WHILE statements to stop the iteration and exit the statement.

See also: ITERATE statement

Examples

1)

The following example shows the LEAVE statement used in SQL code:

DROP FUNCTION IF EXISTS dummyFunc;

CREATE FUNCTION dummyFunc( p1 INTEGER )

RETURNS INTEGER

BEGIN

DECLARE v1, v2 INTEGER;

SET v1 = 10;

SET v2 = 10;

WHILE v1 > 0 DO

SET v1 = v1 - 1;

IF v1 >= 5 THEN

LEAVE;

END IF;

SET v2 = v2 - 1;

END WHILE;

RETURN v2 + p1;

END;

SELECT dummyFunc( 15 ) FROM #dummy;

Conformance

SQL:2003 standard

-

-

SQL/PSM Feature P002-09 "LEAVE statement"

Statement label is not supported


REPEAT statement

REPEAT statement

Repeat the execution until the condition is true.

Syntax

<repeat statement> ::=

REPEAT

<SQL statement list>

UNTIL <search condition>

END REPEAT

Usage

The REPEAT statement is used to specify a repeated iteration of the statement block until the search condition is true.

See also: WHILE statement

Examples

1)

The following example shows the REPEAT statement used in SQL code:

DROP FUNCTION IF EXISTS dummyFunc;

CREATE FUNCTION dummyFunc( p1 INTEGER )

RETURNS INTEGER

BEGIN

DECLARE v1, v2 INTEGER;

SET v1 = p1;

SET v2 = p1 * 10;

REPEAT

SET v1 = v1 + p1;

UNTIL v1 = v2

END REPEAT;

RETURN v1;

END;

SELECT dummyFunc( 10 ) FROM #dummy;

Conformance

SQL:2003 standard

-

-

SQL/PSM Feature P002-11 "REPEAT statement"

Beginning and ending statement labels are not supported


WHILE statement

WHILE statement

Repeat the execution while the condition is true.

Syntax

<while statement> ::=

WHILE <search condition> DO

<SQL statement list>

END WHILE

Usage

The WHILE statement is used to specify a repeated iteration of the statement block while the search condition is true.

See also: REPEAT statement

Examples

1)

The following example shows the WHILE statement used in SQL code:

DROP FUNCTION IF EXISTS dummyFunc;

CREATE FUNCTION dummyFunc( p1 INTEGER )

RETURNS INTEGER

BEGIN

DECLARE v1, v2 INTEGER;

SET v1 = 10;

SET v2 = 10;

WHILE v1 > 0 DO

SET v1 = v1 - 1;

IF v1 <= 5 THEN

ITERATE;

END IF;

SET v2 = v2 - 1;

END WHILE;

RETURN v2 + p1;

END;

SELECT dummyFunc( 15 ) FROM #dummy;

Conformance

SQL:2003 standard

-

-

SQL/PSM Feature P002-12 "WHILE statement"

Beginning and ending statement labels are not supported


SIGNAL statement

SIGNAL statement

Signal an exception condition.

Syntax

<signal statement> ::= SIGNAL <character string literal>

Usage

The SIGNAL statement is used to throw an exception with the message specified by the character string literal.

Examples

1)

The following example throws an exception:

SIGNAL 'Dummy Exception.';

2)

The following example shows the SIGNAL statement used in a conditional computation:

DECLARE v1, v2 DOUBLE PRECISION;

SET v1 = RAND;

SET v2 = RAND;

IF ( v1 = v2 ) THEN

SIGNAL 'The values are equal.';

ELSE

SIGNAL 'The values are different.';

END IF;

3) The following example shows the SIGNAL statement used in the CATCH clause of a TRY statement:

START TRANSACTION;

TRY

DROP TABLE garbage;

COMMIT;

CATCH POSITION( 'Unable to open table' IN ERROR_MESSAGE ) <> 0

ROLLBACK;

SIGNAL 'The table couldn''t be dropped.' || CHR(13) || 'The transaction was rolled back.';

END;

Conformance

SQL:2003 standard

-

SQL/PSM Feature P002-14 "SIGNAL statement"


TRY statement

TRY statement

Specify a TRY..CATCH block.

Syntax

<try statement> ::=

TRY <protected statement block> <catch clause>... END

<protected statement block> ::= <SQL statement list>

<catch clause> ::= CATCH error-condition [ <catch statement block> ]

<catch statement block> ::= <SQL statement list>

Usage

The TRY statement is used to protect a block of SQL statements, and handle some or all of the possible errors that might occur during the execution, gracefully in SQL code. The concept is similar to TRY statements found in many programming languages.

Notes

§

Several CATCH clauses may be specified, but at least one is required.

§

A specific error-condition can be determined by evaluating the current error message using the ERROR_MESSAGE system function.

§

The statement block of the first CATCH clause that evaluates to true is executed.

§

If none of the CATCH clauses evaluates to true, then the current exception is raised.

Examples

1)

The following example shows the TRY statement used to control an explicit transaction:

START TRANSACTION;

TRY

// Execute some data-change statements here.

COMMIT;

CATCH POSITION( 'Unable to open table' IN ERROR_MESSAGE ) <> 0

ROLLBACK;

SIGNAL ERROR_MESSAGE;

END;

Conformance

NexusDB extensions

-

TRY statement


Special Statements

Special Statements

Special NexusDB SQL statements are classified under this category.

ASSERT TABLE statement


ASSERT TABLE statement

ASSERT TABLE statement

Syntax

<assert table statement> ::= ASSERT TABLE table-name = TABLE table-name

Usage

The assert table statement checks if the two tables have identical column definitions and row data, and throws an exception if any difference is detected.

Examples

1)

ASSERT TABLE students = TABLE students_backup_20040810;

Conformance

NexusDB extensions

-

ASSERT TABLE statement


System Tables

System Tables

SQL system tables

Table name | Description
$SQL$FUNCTIONS.nx1 | Metadata for user-defined functions
$SQL$PROCEDURES.nx1 | Metadata for user-defined procedures
$SQL$TRIGGERS.nx1 | Metadata for triggers
$SQL$VIEWS.nx1 | Metadata for views

Metadata views

Table name | Description
#META | This table
#TABLES | Base tables
#FIELDS | Columns
#INDICES | Indices
#INDEXES | Same as #INDICES
#INDEXFIELDS | Index segments
#FILES | Physical files
#DUMMY | Virtual test table with NULL as only row/column
#FUNCTIONS | User-defined functions
#PROCEDURES | Stored procedures
#PROCEDURE_ARGUMENTS | Stored procedure arguments
#TRIGGERS | Triggers
#VIEWS | Views
#COLLATIONS | Supported collations
#CODEPAGES | Supported code pages
#CHECK_CONSTRAINTS | Check constraints
#REFERENCES | Referential constraints
#FUNCTION_ARGUMENTS | Stored function arguments
#SCHEMAS | Database aliases
#FOREIGNKEY_CONSTRAINTS | Foreign key constraints
#FOREIGNKEY_CONSTRAINTS_REFERENCING_COLUMNS | Foreign key constraints referencing columns
#FOREIGNKEY_CONSTRAINTS_REFERENCED_COLUMNS | Foreign key constraints referenced columns
#ASSEMBLIES | Known assemblies
#ASSEMBLYFUNCTIONS | Functions of an Assembly
#SERVER_SETTINGS | Server Settings


Core SQL:2003 Features

Core SQL:2003 Features

Overview of Core SQL:2003 Features

Feature ID

Feature description

NexusDB

E011 | Numeric data types |
E011-01 | INTEGER and SMALLINT data types (including all spellings) | ü
E011-02 | REAL, DOUBLE PRECISION, and FLOAT data types | ü
E011-03 | DECIMAL and NUMERIC data types | ü
E011-04 | Arithmetic operators | ü
E011-05 | Numeric comparison | ü
E011-06 | Implicit casting among the numeric data types | ü
E021 | Character string types |
E021-01 | CHARACTER data type (including all its spellings) | ü
E021-02 | CHARACTER VARYING data type (including all its spellings) | ü
E021-03 | Character literals | ü
E021-04 | CHARACTER_LENGTH function | ü
E021-05 | OCTET_LENGTH function | ü
E021-06 | SUBSTRING function | ü
E021-07 | Character concatenation | ü
E021-08 | UPPER and LOWER functions | ü
E021-09 | TRIM function | ü
E021-10 | Implicit casting among the fixed and variable length character string types | ü
E021-11 | POSITION function | ü
E021-12 | Character comparison | ü
E031 | Identifiers |
E031-01 | Delimited identifiers | ü
E031-02 | Lower case identifiers | ü
E031-03 | Trailing underscore | ü
E051 | Basic query specification |
E051-01 | SELECT DISTINCT | ü
E051-02 | GROUP BY clause | ü
E051-04 | GROUP BY can contain columns not in <select list> | ü
E051-05 | Select list items can be renamed | ü
E051-06 | HAVING clause | ü
E051-07 | Qualified * in select list | ü
E051-08 | Correlation names in the FROM clause | ü
E051-09 | Rename columns in the FROM clause | ü
E061 | Basic predicates and search conditions |
E061-01 | Comparison predicate | ü
E061-02 | BETWEEN predicate | ü
E061-03 | IN predicate with list of values | ü
E061-04 | LIKE predicate | ü
E061-05 | LIKE predicate: ESCAPE clause | ü
E061-06 | NULL predicate | ü
E061-07 | Quantified comparison predicate | ü
E061-08 | EXISTS predicate | ü
E061-09 | Subqueries in comparison predicate | ü
E061-11 | Subqueries in IN predicate | ü
E061-12 | Subqueries in quantified comparison predicate | ü
E061-13 | Correlated subqueries | ü
E061-14 | Search condition | ü
E071 | Basic query expressions |
E071-01 | UNION DISTINCT table operator | ü
E071-02 | UNION ALL table operator | ü
E071-03 | EXCEPT DISTINCT table operator |
E071-05 | Columns combined via table operators need not have exactly the same data type. | ü
E071-06 | Table operators in subqueries | ü
E081 | Basic Privileges
E081-01 | SELECT privilege at the table level
E081-02 | DELETE privilege
E081-03 | INSERT privilege at the table level
E081-04 | UPDATE privilege at the table level
E081-05 | UPDATE privilege at the column level
E081-06 | REFERENCES privilege at the table level
E081-07 | REFERENCES privilege at the column level
E081-08 | WITH GRANT OPTION
E081-09 | USAGE privilege
E081-10 | EXECUTE privilege
E091 | Set functions |
E091-01 | AVG | ü
E091-02 | COUNT | ü
E091-03 | MAX | ü
E091-04 | MIN | ü
E091-05 | SUM | ü
E091-06 | ALL quantifier | ü
E091-07 | DISTINCT quantifier | ü
E101 | Basic data manipulation |
E101-01 | INSERT statement | ü
E101-03 | Searched UPDATE statement | ü
E101-04 | Searched DELETE statement | ü

E111

Single row SELECT statement

E121 | Basic cursor support |
E121-01 | DECLARE CURSOR |
E121-02 | ORDER BY columns need not be in select list | ü
E121-03 | Value expressions in ORDER BY clause |
E121-04 | OPEN statement |
E121-06 | Positioned UPDATE statement |
E121-07 | Positioned DELETE statement |
E121-08 | CLOSE statement |
E121-10 | FETCH statement: implicit NEXT |
E121-17 | WITH HOLD cursors |

E131

Null value support (nulls in lieu of values)

ü

E141 | Basic integrity constraints |
E141-01 | NOT NULL constraints | ü
E141-02 | UNIQUE constraints of NOT NULL columns | ü
E141-03 | PRIMARY KEY constraints | ü
E141-04 | Basic FOREIGN KEY constraint with the NO ACTION default for both referential delete action and referential update action. | ü
E141-06 | CHECK constraints | ü
E141-07 | Column defaults | ü
E141-08 | NOT NULL inferred on PRIMARY KEY | ü
E141-10 | Names in a foreign key can be specified in any order | ü
E151 | Transaction support |
E151-01 | COMMIT statement | ü
E151-02 | ROLLBACK statement | ü
E152 | Basic SET TRANSACTION statement
E152-01 | SET TRANSACTION statement: ISOLATION LEVEL SERIALIZABLE clause
E152-02 | SET TRANSACTION statement: READ ONLY and READ WRITE clauses

E153

Updatable queries with subqueries

ü

E161

SQL comments using leading double minus

ü

E171

SQLSTATE support

E182

Module language

F021 | Basic information schema
F021-01 | COLUMNS view
F021-02 | TABLES view
F021-03 | VIEWS view
F021-04 | TABLE_CONSTRAINTS view
F021-05 | REFERENTIAL_CONSTRAINTS view
F021-06 | CHECK_CONSTRAINTS view
F031 | Basic schema manipulation |
F031-01 | CREATE TABLE statement to create persistent base tables | ü
F031-02 | CREATE VIEW statement | ü
F031-03 | GRANT statement |
F031-04 | ALTER TABLE statement: ADD COLUMN clause | ü
F031-13 | DROP TABLE statement: RESTRICT clause | ü
F031-16 | DROP VIEW statement: RESTRICT clause | ü
F031-19 | REVOKE statement: RESTRICT clause |
F041 | Basic joined table |
F041-01 | Inner join (but not necessarily the INNER keyword) | ü
F041-02 | INNER keyword | ü
F041-03 | LEFT OUTER JOIN | ü
F041-04 | RIGHT OUTER JOIN | ü
F041-05 | Outer joins can be nested | ü
F041-07 | The inner table in a left or right outer join can also be used in an inner join | ü
F041-08 | All comparison operators are supported (rather than just =) | ü
F051 | Basic date and time |
F051-01 | DATE data type (including support of DATE literal) | ü
F051-02 | TIME data type (including support of TIME literal) | ü
F051-03 | TIMESTAMP data type (including support of TIMESTAMP literal) | ü
F051-04 | Comparison predicate on DATE, TIME, and TIMESTAMP data types | ü
F051-05 | Explicit CAST between datetime types and character string types | ü
F051-06 | CURRENT_DATE | ü
F051-07 | LOCALTIME | ü
F051-08 | LOCALTIMESTAMP | ü

F081

UNION and EXCEPT in views

ü

F131 | Grouped operations |
F131-01 | WHERE, GROUP BY, and HAVING clauses supported in queries with grouped views | ü
F131-02 | Multiple tables supported in queries with grouped views | ü
F131-03 | Set functions supported in queries with grouped views | ü
F131-04 | Subqueries with GROUP BY and HAVING clauses and grouped views | ü
F131-05 | Single row SELECT with GROUP BY and HAVING clauses and grouped views |

F181

Multiple module support

F201

CAST function

ü

F221

Explicit defaults

ü

F261 | CASE expression |
F261-01 | Simple CASE | ü
F261-02 | Searched CASE | ü
F261-03 | NULLIF | ü
F261-04 | COALESCE | ü

F311

Schema definition statement

F471

Scalar subquery values

ü

F481

Expanded NULL predicate

ü

F501 | Features and conformance views
F501-01 | SQL_FEATURES view
F501-02 | SQL_SIZING view
F501-03 | SQL_LANGUAGES view

F812

Basic flagging

S011 | Distinct data types
S011-01 | USER_DEFINED_TYPES view
T321 | Basic SQL-invoked routines |
T321-01 | User-defined functions with no overloading | ü
T321-02 | User-defined stored procedures with no overloading | ü
T321-03 | Function invocation | ü
T321-04 | CALL statement | ü
T321-05 | RETURN statement | ü
T321-06 | ROUTINES view |
T321-07 | PARAMETERS view |

T631

IN predicate with one list element

ü


Additional SQL:2003 Features

 Top Previous

Additional SQL:2003 Features

Additional SQL:2003 features supported in NexusDB SQL

Feature ID

Feature description

B128 | Routine language SQL
F033 | ALTER TABLE statement: DROP COLUMN clause
F052 | Intervals and datetime arithmetic
F171 | Multiple schemas per user
F191 | Referential delete actions
F222 | INSERT statement: DEFAULT VALUES clause
F271 | Compound character literals
F281 | LIKE enhancements
F291 | UNIQUE predicate
F302 | INTERSECT table operator
F302-01 | INTERSECT DISTINCT table operator
F302-02 | INTERSECT ALL table operator
F304 | EXCEPT ALL table operator
F381 | Extended schema manipulation
F381-01 | ALTER TABLE statement: ALTER COLUMN clause
F381-02 | ALTER TABLE statement: ADD CONSTRAINT clause
F381-03 | ALTER TABLE statement: DROP CONSTRAINT clause
F391 | Long identifiers
F401 | Extended joined table
F401-01 | NATURAL JOIN
F401-02 | FULL OUTER JOIN
F401-04 | CROSS JOIN
F421 | National character
F491 | Constraint management
F531 | Temporary tables
F561 | Full value expressions
F571 | Truth value tests
F591 | Derived tables
F671 | Subqueries in CHECK constraints
F672 | Retrospective check constraints
F692 | Enhanced collation support
F701 | Referential update actions
F741 | Referential MATCH types
T031 | BOOLEAN data type
T041 | Basic LOB data type support
T041-01 | BLOB data type
T041-02 | CLOB data type
T041-03 | POSITION, LENGTH, LOWER, TRIM, UPPER, and SUBSTRING functions for LOB data types
T041-04 | Concatenation of LOB data types
T071 | BIGINT data type
T171 | LIKE clause in table definition
T172 | AS subquery clause in table definition
T191 | Referential action RESTRICT
T201 | Comparable data types for referential constraints
T211 | Basic trigger capability
T211-01 | Triggers activated on UPDATE, INSERT, or DELETE of one base table.
T211-02 | BEFORE triggers
T211-03 | AFTER triggers
T211-04 | FOR EACH ROW triggers
T211-05 | Ability to specify a search condition that shall be True before the trigger is invoked.
T241 | START TRANSACTION statement
T326 | Table functions
T351 | Bracketed SQL comments (/*...*/ comments)
T441 | ABS and MOD functions
T501 | Enhanced EXISTS predicate
T551 | Optional key words for default syntax
T591 | UNIQUE constraints of possibly null columns
T621 | Enhanced numeric functions
T651 | SQL-schema statements in SQL routines
T653 | SQL-schema statements in external routines
P002 | Computational completeness
P002-01 | <compound statement>
P002-04 | <SQL variable declaration>
P002-05 | <assignment statement>
P002-07 | <if statement>
P002-08 | <iterate statement>
P002-09 | <leave statement>
P002-11 | <repeat statement>
P002-12 | <while statement>
P002-14 | <signal statement>