Using the PxPlus SQL ODBC Driver

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')