SQL Syntax Table

The PxPlus SQL ODBC Driver supports the SQL syntax described in the table below. For an illustration of this syntax, see Constructing a Left Outer Join Using the Syntax Table.

In the following table, SQL keywords are shown in uppercase, the vertical bars (pipes) "|" separate choices where more than one command is represented, and a blank indicates that no qualifier is required.

Syntax	Description
statement	SELECT select orderby | INSERT insert | DELETE delete | UPDATE update
top	blank | TOP integer
select	top selectcols FROM tablelist where groupby having limit hint union
insert	INTO tablename insertvals
delete	FROM tablename where
update	tablename SET setlist where
setlist	set | setlist, set
set	columnname = NULL | columnname = expression
insertvals	(columnlist) VALUES (valuelist) | VALUES (valuelist) | (columnlist) VALUES (SELECT select) | VALUES (SELECT select)
columnlist	columnname, columnlist | columnname
valuelist	NULL, valuelist | expression, valuelist | expression | NULL
selectcols	selectallcols * | selectallcols selectlist
selectallcols	blank | ALL | DISTINCT
selectlist	selectlistitem, selectlist | selectlistitem
selectlistitem	expression | expression aliasname | expression AS aliasname | aliasname.* | colref
where	blank | WHERE boolean
having	blank | HAVING boolean
limit	blank | LIMIT integer limitoffset
limitoffset	blank | OFFSET integer
union	blank | UNION select | UNION ALL select (Support for SQL UNION and UNION ALL syntax was added in PxPlus 2016.)
boolean	and | and OR boolean
and	not | not AND and
not	comparison | NOT comparison
comparison	(boolean) | colref IS NULL | colref IS NOT NULL | expression LIKE pattern | expression NOT LIKE pattern | expression IN (valuelist) | expression NOT IN (valuelist) | expression op expression | EXISTS (SELECT select) | expression op selectop (SELECT select) | expression IN (SELECT select) | expression NOT IN (SELECT select) | expression BETWEEN expression AND expression | expression NOT BETWEEN expression AND expression
selectop	blank | ALL | ANY
op	> | >= | < | <= | = | <>
pattern	string | ? | USER
expression	expression + times | expression - times | times
times	times * neg | times / neg | neg
neg	term | + term | - term
term	(expression) | colref | simpleterm | aggterm | scalar | case
case	CASE when else END
when	expression simplewhen | searchedwhen
simplewhen	WHEN expression THEN expression | simplewhen simplewhen
searchedwhen	WHEN boolean THEN expression | searchedwhen searchedwhen
else	blank | ELSE expression
scalar	scalarshorthand | fn
scalarshorthand	{FN fn}
fn	functionname (valuelist) | functionname( )
aggterm	COUNT ( * ) | AVG (expression) | MAX (expression) | MIN (expression) | SUM (expression) | COUNT (expression)
simpleterm	string | realnumber | ? | USER | date
groupby	blank | GROUP BY groupbyterms
groupbyterms	colref | colref, groupbyterms
orderby	blank | ORDER BY orderbyterms
orderbyterms	orderbyterm | orderbyterm, orderbyterms
orderbyterm	colref asc | integer asc
asc	blank | ASC | DESC
hint	blank | HINT KEY integer
colref	aliasname.columnname | columnname
tablelist	tablelistitem crossjointerm tablelist | tablelistitem
crossjointerm	, | CROSS JOIN
tablelistitem	tableref | outerjoin
outerjoin	ojshorthand | oj
ojshorthand	{OJ oj}
inneroj	tableref innerojterm tableref ON boolean | tableref innerojterm oj ON boolean | oj innerojterm tableref ON boolean | oj innerojterm oj ON boolean
innerojterm	INNER JOIN | JOIN
oj	tableref ojterm tableref ON boolean | tableref ojterm oj ON boolean | oj ojterm tableref ON boolean | oj ojterm oj ON boolean | inneroj | ( oj )
ojterm	LEFT OUTER JOIN | LEFT JOIN | RIGHT OUTER JOIN | RIGHT JOIN | FULL OUTER JOIN | FULL JOIN (Support for FULL OUTER JOIN | FULL JOIN was added in PxPlus 2018.)
tableref	tablename | tablename aliasname
indexname	identifier
functionname	identifier (see Scalar Functions)
tablename	identifier | qualifier.identifier
qualifier	identifier (The qualifier is ignored. The PxPlus SQL ODBC Driver does not support a data dictionary directory other than the one specified in the DSN or connection string.) (Support for qualifier was added in PxPlus 2017.)
datatype	identifier
columnname	identifier
aliasname	identifier
identifier	An identifier (must be enclosed in double quotes if it contains spaces)
string	A string (enclosed in single quotes)
realnumber	A non-negative real number (including E notation)
integer	A non-negative integer
date	dateshorthand | dateval
dateshorthand	{d dateval}
dateval	Date in yyyy-mm-dd format in single quotes (e.g. '1996-02-05')

SQL Comments

The PxPlus SQL ODBC Driver also allows comments within SQL queries. Both block and line comments are supported. (added in PxPlus 2016)

Block Comment:	/* This is a comment */
Line Comment:	-- This is also a comment

Example:

       /* This is an example SQL query with comments */
       SELECT * --Get all rows
       FROM 'Customer' --List all customers