|
Using the PxPlus SQL ODBC Driver |
ODBC is primarily used to provide access to data files from other products such as Crystal reports, Excel or Word on Windows and OpenOffice/LibreOffice Calc and Base on UNIX/Linux. Most programming languages have an ODBC access facility to allow files to be read or updated as well. The process of bringing PxPlus data to your application begins with making a data connection — the steps involved will vary according to your application and your server technology.
For specifics on PxPlus ODBC DSNs and connection information, see Configuration Procedures.
Once a data connection is established, the PxPlus data itself can be accessed from the database using SQL commands. SQL (Structured Query Language) is the standard interactive programming language for accessing and manipulating databases.
This Help section describes the specific elements of SQL that can be used with the PxPlus SQL ODBC Driver.
For examples of different SQL queries, see Example SQL.
For answers to some commonly asked questions about the PxPlus SQL ODBC Driver and other PxPlus ODBC products, see Frequently Asked Questions.
An SQL statement is used to perform various operations on the database. The PxPlus SQL ODBC Driver supports four types of SQL statements:
|
Type |
Description |
|
SELECT |
Retrieves data from the database. |
|
INSERT |
Adds new data to the database. |
|
DELETE |
Removes data from the database. |
|
UPDATE |
Changes data in the database. |
SQL statements operate with logical sets of data - they declare what data is required, not how the data is to be retrieved.
When data is required from two or more tables, the statement can establish a relationship between the tables. In SQL, this concept is called joining. The join operation selects rows from two different tables such that the value in one column of Table 1 also appears in a column of Table 2.
Example:
A customer table includes a code for sales representatives called SALESREP and the sales representative table includes a SALESREP code among other information about sales representatives (names, addresses, etc.). A join relationship between the customer table and sales representative table can be established because they each have a SALESREP column.
To join three or more tables, you can nest a join operation within another join operation.
The PxPlus SQL ODBC Driver supports four types of joins:
|
Type |
Description |
|
CROSS JOIN |
Returns all the records in Table 2 for each common record in Table 1. To create a cross join, a comma is used to separate the declared tables. The keywords CROSS JOIN can be used in place of the comma. Example: SELECT * FROM Customer, SalesReps |
|
JOIN / INNER JOIN |
Discards unmatched rows in either table. The keyword INNER is optional. The outer join escape sequence is optional. (as of PxPlus SQL ODBC Driver version 6.00) Example: SELECT * FROM {oj Customer INNER JOIN SalesReps ON Customer.SALESREP = SalesReps.SALESREP} |
|
LEFT JOIN / LEFT OUTER JOIN |
LEFT [OUTER] JOIN will, for each record in Table 1, join the matching record in Table 2, if any. The keyword OUTER is optional. The outer join escape sequence is optional. (as of PxPlus SQL ODBC Driver version 6.00) Example: SELECT * FROM {oj Customer LEFT OUTER JOIN SalesReps ON Customer.SALESREP = SalesReps.SALESREP} |
|
RIGHT JOIN / RIGHT OUTER JOIN |
RIGHT [OUTER] JOIN will, for each record in Table 2, join the matching record in Table 1, if any. The keyword OUTER is optional. The outer join escape sequence is optional. (as of PxPlus SQL ODBC Driver version 6.00) Example: SELECT * FROM {oj Customer RIGHT OUTER JOIN SalesReps ON Customer.SALESREP = SalesReps.SALESREP} |
|
FULL JOIN / FULL OUTER JOIN |
FULL [OUTER] JOIN will do a LEFT JOIN and then a RIGHT JOIN. The keyword OUTER is optional. The outer join escape sequence is optional. (as of PxPlus SQL ODBC Driver version 6.00) Example: SELECT * FROM {oj Customer FULL OUTER JOIN SalesReps ON Customer.SALESREP = SalesReps.SALESREP} |
Three or More Table Joins
The PxPlus SQL ODBC Driver supports nesting join operations, which allow for joining three or more tables:
|
Join |
Description |
|
JOIN NESTED IN TABLE 1 POSITION |
This will perform a left outer join with Table 1 and Table 2, take the results of that join and perform a left join with Table 3. The parentheses around the nested join are optional. Example: SELECT * FROM {oj (Customer LEFT OUTER JOIN SalesReps ON Customer.SALESREP = SalesReps.SALESREP) LEFT OUTER JOIN Shipping ON Customer.CUSTID = Shipping.CUSTID} |
|
JOIN NESTED IN TABLE 2 POSITION |
This will perform a left outer join with Table 2 and Table 3, take the results of that join and use them as the right side and left join them with Table 1. Optional parentheses around the nested join are allowed. (as of PxPlus SQL ODBC Driver version 6.00) Example: SELECT * FROM {oj Customer LEFT OUTER JOIN SalesReps LEFT OUTER JOIN Commission ON SalesReps.SALESREP = Commission.SALESREP ON Customer.SALESREP = SalesRep.SALESREP} |
Mixing different types of joins is supported; i.e. you can nest an inner join within a left outer join. It is also possible to join more than three tables by nesting more than once. Caution must be used when joining three or more tables, as the more tables you join, the longer the SQL query will take to complete. This is proportional to the sizes of the tables being joined.
Another method for getting data from two or more tables is to union select statements together. A union combines the results of two or more select statements. The select statements within the union must have the same number of columns. The columns must also have similar data types. In addition, the columns in each select statement must be in the same order.
To union three or more tables together, you can chain unions together one after another.
Example:
If you had an invoice2014 table and an invoice2015 table each containing invoices for their respective years and you wanted to query the invoices for the last two years, you could simply union the results of a select on each table. Using an ORDER BY, it is also possible to sort the combined results from the two tables.
Types of Unions
The two types of unions are UNION and UNION ALL:
|
Type |
Description |
|
UNION |
A UNION will combine the results of the select statements, remove duplicate records and sort the results based on the first column. This is equivalent to a DISTINCT on the combined results. Example: SELECT * FROM invoice2014 UNION SELECT * FROM invoice2015 |
|
UNION ALL |
A UNION ALL will simply combine the results of the select statements. Example: SELECT * FROM invoice2014 UNION ALL SELECT * FROM invoice2015 |