SQL Syntax Definitions

One or more syntax elements terminated by a semicolon.

Scalar expressions represent a single data value, either a numeric or a string from a constant specification. Examples include: 1, 'hello there', '31-DEC-60'd), a function (that is, avg(a*b)), a column/variable (that is foo.bar), the case expression, or even a subquery which returns a single run-time value. Boolean predicates are either true or false. They are used in WHERE clauses, having clauses and in the case expression. You cannot select predicates, nor can you assign them to columns (that is, in an update statement). Scalar expressions and Boolean predicates cannot be used interchangeably, although SAS SQL does allow you to mix the expressions.

SPD Server SQL strings are character streams that are delimited by either single or double quotation marks. If you use a single quotation mark to begin a string, you must use a single quotation mark to terminate the string. To embed a single quotation mark in a string, use two single quotation marks together. For example,

SELECT 'it''s a wonderful life' from mytable. 

You can use double quotation marks in the same manner. There is another way to embed a single quotation mark without doubling the character. You can use double-quotation marks as delimiters. For example,

SELECT it's a wonderful life from mytable.

In some of the syntax specifications that follow, a user-defined or database-specific string is noted. Delimit these strings with a bracket or parenthesis. Characters between the delimiters are considered part of the string up to, but not including, the matching delimiter.

   CONNECT to sasspds(
     user='john'
     passwd='foobar'
     options=(a b c)
     );

The dbms_options string is

    user='john'
    passwd='foobar'
    options=(a b c).

In this example, the first right-parenthesis is considered part of the string. It is not the matching termination delimiter.

Identifiers are the names of librefs, tables, indexes and columns, as well as table and column aliases.

Keywords are used to initiate statements and syntax elements. For example, WHERE or GROUP BY clauses. Keywords are also reserved. They cannot be used for identifiers because this use introduces ambiguity. For example, select unique from; is a valid but ambiguous statement. Below is a list of current SPD Server keywords. Some words have been reserved for future enhancements to SPD Server SQL:

add, all, alter, and, any, as, asc, async, begin, between, both, by,
calculated, cascade, case, char, character, column, connect,
connection, contains, contents, copy, corr, corresponding, create,
cross, date, dec, decimal, default, delete, desc, describe,
dictionary, disconnect, distinct, double, drop, else, end, engname,
engopt, eq, except, execute, exists, false, float, for, format, from,
full, ge, grant, group, gt, having, in, index, indexes, informat,
inner, insert, int, integer, intersect, into, is, join, label, le,
leading, left, libref, like, load, lower, lt, match, missing, modify,
natural, ne, no, not, notin, null, num, numeric, on, operation, option,
or, order, outer, overlaps, partial, precision, privileges, public,
real, references, reset, restrict, revoke, right, select, set,
smallint, some, table, then, to, trailing, trim, true, union, unique,
unknown, update, upper, using, validate, values, varchar, verbose,
view, when, where, with, without, yes