# Using the PxPlus SQL ODBC Driver

Example SQL |

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:

CustomerId | Name | SalesRepId | ARBalance
0001 | ABC Corp. | 01 | 1234.99
0002 | Acme Inc. |  | 1.23

The SalesRep table contains three rows:

SalesRepId | Name | HireDate | TotalSales | District
01 | John Doe | 20060323 | 58246.21 | 1
02 | Jane Smith | 20120714 | 93220.03 | 2
03 | House Account | 20110122 | 67349.88 | 1

The Commission table contains three rows:

SalesRepId | Rate
01 | 4.0
02 | 4.8
03 | 4.2

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 …

SalesRepId | Name | HireDate | TotalSales | District
02 | Jane Smith | 20120714 | 93220.03 | 2
03 | House Account | 20110122 | 67349.88 | 1

#### 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 …

Rate
4.8
4.2

## Scalar Functions

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

Result set …

Name
John
Jane
House

#### 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 …

District | TotalSales
1 | 125596.09
2 | 93220.03

## 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 …

SalesRepId | Country
01 | Canada
02 | U.S.
03 | Canada

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

## Sub-Query

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

Result set …

SalesRepId | Name | HireDate | TotalSales | District
01 | John Doe | 20060323 | 58246.21 | 1

## Cross Join

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

Customer.Name | SalesRep.Name
ABC Corp. | John Doe
ABC Corp. | Jane Smith
ABC Corp. | House Account
Acme Inc. | John Doe
Acme Inc. | Jane Smith
Acme Inc. | House Account

## Inner Join

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

Customer.Name | SalesRep.Name
ABC Corp. | John Doe

#### 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 ...

Customer.Name | SalesRep.Name
ABC Corp. | John Doe
Acme Inc. |

## 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 ...

Customer.Name | SalesRep.Name
ABC Corp. | John Doe
| Jane Smith
| House Account

#### 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 ...

Customer.Name | SalesRep.Name
ABC Corp. | John Doe
Acme Inc. |
ABC Corp. | John Doe
| Jane Smith
| House Account

#### 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 ...

Customer.Name | SalesRep.Name
ABC Corp. | John Doe
Acme Inc. |
| Jane Smith
| House Account

#### 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 ...

Customer.Name | SalesRep.Name | Commission.Rate
ABC Corp. | John Doe | 4.0
| Jane Smith | 4.8
| House Account | 4.2

#### 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 …

Name
ABC Corp.
Acme Inc.
House Account
Jane Smith
John Doe

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

## Delete

DELETE
FROM Customer
WHERE SalesRepId IS NULL

Customer table after delete …

CustomerId | Name | SalesRepId | ARBalance
0001 | ABC Corp. | 01 | 1234.99

#### 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 …

CustomerId | Name | SalesRepId | ARBalance
0001 | ABC Corp. | 01 | 1234.99
0002 | Acme Inc. |  | 1.23
0003 | New Customer | 02 | 9999.99

## Update

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

Customer table after update …

CustomerId | Name | SalesRepId | ARBalance
0001 | ABC Corp. | 01 | 1234.99
0002 | Acme Inc. | 02 | 1.23

## 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 }