Using the PxPlus SQL ODBC Driver

The examples below use three tables: Customer, SalesRep and Commission.

The Customer table contains four fields: CustomerId, Name, SalesRepId and ARBalance.
The SalesRep table contains two fields: SalesRepId and Name.
The Commission table contains two fields: SalesRepId and Rate.

The Customer table contains two rows:

The SalesRep table contains three rows:

The Commission table contains three rows:

The following examples illustrate the results of a variety of different SQL queries.

Date Filter

SELECT *
FROM Customer
WHERE HireDate > { d '2010-01-01' }

Result set …

Note:
The date escape syntax is optional, and it is possible to omit the { d  … }. (as of PxPlus SQL ODBC Driver version 6.00)

Sort and Limit

SELECT Rate
FROM Commission
LIMIT 2
ORDER BY Rate DESC

Result set …

Scalar Functions

SELECT { FN SUBSTR(Name, 1, { FN POSITION(' ' IN Name)} – 1)}
FROM SalesRep

Result set …

Note:
The scalar function escape syntax is optional, and it is possible to omit the { FN … }. (as of PxPlus SQL ODBC Driver version 6.00)

Aggregate Function and Group By

SELECT District, SUM(TotalSales)
FROM Customer
GROUP BY District

Result set …

Case Conditional (as of PxPlus SQL ODBC Driver version 6.00)

SELECT
SalesRepId,
CASE District
    WHEN 1 THEN 'Canada'
    WHEN 2 THEN 'U.S.'
END AS Country
FROM SalesRep

Result set …

(Support for SQL CASE syntax was added in PxPlus 2016.)

Sub-Query

SELECT *
FROM SalesRep
WHERE SalesRepId IN (SELECT SalesRepId
                                    FROM Customer)

Result set …

Cross Join

SELECT Customer.Name, SalesRep.Name
FROM Customer Customer, SalesRepSalesRep

Result set ...

Inner Join

SELECT Customer.Name, SalesRep.Name
FROM { OJ Customer Customer INNER JOIN SalesRep SalesRep ON Customer.SalesRepId = SalesRep.SalesRepId }

Result set ...

Note:
The join escape syntax is optional, and it is possible to omit the { OJ … }. (as of PxPlus SQL ODBC Driver version 6.00)

Left Outer Join

SELECT Customer.Name, SalesRep.Name
FROM { OJ Customer LEFT OUTER JOIN SalesRep ON Customer.SalesRepId = SalesRep.SalesRepId }

Result set ...

Right Outer Join (as of PxPlus SQL ODBC Driver version 6.00)

SELECT Customer.Name, SalesRep.Name
FROM { OJ Customer RIGHT OUTER JOIN SalesRep ON Customer.SalesRepId = SalesRep.SalesRepId }

Result set ...

Note:
The join escape syntax is optional, and it is possible to omit the { OJ … }. (as of PxPlus SQL ODBC Driver version 6.00)

(Support for RIGHT OUTER JOIN was added in PxPlus 2016.)

Full Outer Join (as of PxPlus SQL ODBC Driver version 6.10)

SELECT Customer.Name, SalesRep.Name
FROM { OJ Customer FULL OUTER JOIN SalesRep ON Customer.SalesRepId = SalesRep.SalesRepId }

Result set ...

Note:
The join escape syntax is optional, and it is possible to omit the { OJ … }. (as of PxPlus SQL ODBC Driver version 6.00)

(Support for FULL OUTER JOIN was added in PxPlus 2018.)

Distinct

SELECT DISTINCT Customer.Name, SalesRep.Name
FROM { OJ Customer FULL OUTER JOIN SalesRep ON Customer.SalesRepId = SalesRep.SalesRepId }

Result set ...

Note:
The join escape syntax is optional, and it is possible to omit the { OJ … }. (as of PxPlus SQL ODBC Driver version 6.00)

Nested Join

SELECT Customer.Name, SalesRep.Name, Commission.Rate
FROM { OJ Customer RIGHT OUTER JOIN SalesRep LEFT OUTER JOIN Commission ON SalesRep.SalesRepId = Commission.SalesRepId ON Customer.SalesRepId = SalesRep.SalesRepId }

Result set ...

Note:
The join escape syntax is optional, and it is possible to omit the { OJ … }. (as of PxPlus SQL ODBC Driver version 6.00)

Union (as of PxPlus SQL ODBC Driver version 6.00)

SELECT Name FROM Customer UNION SELECT Name FROM SalesRep

Result set …

(Support for SQL UNION syntax was added in PxPlus 2016.)

Delete

DELETE
FROM Customer
WHERE SalesRepId IS NULL

Customer table after delete … 

Note:
The IS NULL could also be written as = ''. (as of PxPlus SQL ODBC Driver version 6.00)

Insert

INSERT INTO Customer
VALUES ('0003','New Customer', 02, 9999.99)

Customer table after insert …

Update

UPDATE Customer
SET SalesRepId = '02'
WHERE CustomerId = '0002'

Customer table after update …

Hint Key

SELECT * FROM my_table WHERE id='John Smith' AND amount > 50000 HINT KEY 2

In this example, my_table has two keys. The first key is id+amount, and the second key is id+amount in descending order.

By default, the PxPlus SQL ODBC Driver would select the first key that best matches the WHERE conditions. In this example, the first key matches perfectly so it would be selected. The second key also matches, but the PxPlus SQL ODBC Driver would choose the first key by default with no hint. However, using the above SQL statement tells the PxPlus SQL ODBC Driver to use the second key, which would be faster in this case. This is because amount values > 50000 are at the top when ordered in descending order.

(Support for SQL HINT was added in PxPlus 2016.)

Constructing a Left Outer Join Using the Syntax Table

The following example applies the syntax descriptors explained in the SQL Syntax Table. In the descriptions below, a "::=" symbol represents the phrase "consists of" and a "|" symbol represents an exclusive OR.

Suppose we want a statement that will retrieve all rows from the Customer table. We want to display the customer name and also display the name of the sales representative when the customer has a sales representative assigned. Because the Customer table and the SalesRep table both contain a column called Name, we must use an alias so that the PxPlus SQL ODBC Driver can determine to which column we are referring.

We begin with …

statement ::= SELECT select orderby

which consists of

select ::=selectcols FROM tablelist where groupby having

which has

selectcols ::= selectallcols * | selectallcols selectlist

We want to display a limited number of columns, so we want a

selectlist ::= selectlistitem , selectlist | selectlistitem

which consists of two

selectlistitem ::= expression | expression aliasname | expression AS aliasname | aliasname.* | colref

Our two selectlist items are

colref ::= aliasname . columnname | columnname

which are composed of an

aliasname ::= identifier

and a

columnname ::= identifier

An identifier consists of an identifier (identifiers containing spaces must be enclosed in double quotes).

As the alias, we use the name of the table; however, an alias is not limited to the table name. Thus far, we have:

SELECT Customer.Name, SalesRep.Name FROM

Now we parse the tablelist.

tablelist ::= tablelistitem crossjointerm tablelist | tablelistitem

where a

tablelistitem ::= tableref | outerjoin

and we want all rows from the first table and matching rows from the second table, if any. Therefore, we need an

outerjoin ::= ojshorthand | oj

Since we do not like to type, we use ojshorthand ::= {OJ oj }

where

oj:= tablerefojterm tableref ON boolean
ojterm := LEFT OUTER JOIN

We have two

tableref ::= tablename | tablename aliasname

where

tablename ::= identifier

Thus far, we have:

SELECT Customer.Name, SalesRep.Name FROM {OJ Customer LEFT OUTER JOIN SalesRep ON }

The final piece is the relationship between the Customer table and the SalesRep table, which is specified with

boolean ::= and | and OR boolean

Ultimately, we want a comparison which is part of

not ::= comparison | NOT comparison

which is part of

and ::= not | not AND and

The comparison is expression op expression where the expressions are colref and the op is an =.

The result is:

SELECT Customer.Name, SalesRep.Name FROM {OJ Customer LEFT OUTER JOIN SalesRep ON Customer.SalesRepId = SalesRep.SalesRepId }