# Using the PxPlus SQL ODBC Driver

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