Examples

The examples below were created using four separate ODBC databases: Microsoft Access, Microsoft SQL Server, Oracle and MySQL.

See Connecting to ODBC databases for detailed examples on creating Super Q-pointers using Microsoft Access and Microsoft SQL Server.

Note: D3 is not case sensitive.

Microsoft Access

Create a Super Q-pointer called custs to retrieve the customer data from the Customers table in the Microsoft Access sample database, Northwind.
```
          custs
<001>     q
<002>
<003>     msaccess.nw:odbc:custs
<004,1>   customers
<004,2>   
<004,3>   customerid
<004,4>   companyname
<004,5>   address
<004,6>   city
<004,7>   region
<004,8>   postalcode
```
In the example above, customerid is the candidate key and therefore, is the item-ID of the D3 file. The columns companyname, address, city, region, and postalcode are attributes 1 through 5 respectively.
Create a Super Q-pointer called usacustomer to retrieve only the customers located in the USA from the Customers table in the Microsoft Access sample database, Northwind.

The where clause country = 'USA' filters out the desired data. See Connecting to a Microsoft Access database for a detailed example.
```
          usacustomer
<001>     q
<002>
<003>     msaccess.nw:odbc:usacustomer
<004,1>   customers
<004,2>   country = 'USA'
<004,3>   customerid
<004,4>   companyname
<004,5>   city
<004,6>   region
<004,7>   
<004,8>   country
```
Create a Super Q-pointer called ukmanagers to retrieve the phone numbers of contacts from the Customers table that hold the position of manager and reside in the UK.

The where clause country = 'UK' and contacttitle like '%manager%' filters out the desired data.
```
          ukmanagers
<001>     q
<002>
<003>     msaccess.nw:odbc:ukmanagers
<004,1>   customers
<004,2>   country = 'UK' and contacttitle like '%manager%'
<004,3>   
<004,4>   contactname
<004,5>   contacttitle
<004,6>   phone
<004,7>   country
```
The example above does not contain a candidate key, therefore it is read-only.

Note: The where clause in <004,2> can contain any clause that is supported by the ODBC database from which the data is retrieved.

Microsoft SQL Server

Create a Super Q-pointer called cust.ord to retrieve data from the Customers and Orders tables in the SQL Server sample database, Northwind. See Connecting to a Microsoft SQL Server database for a detailed example.
```
          cust.ord
<001>     q
<002>
<003>     sqlsvr.host:odbc:cust.ord
<004,1>   customers,orders
<004,2>   "customers"."customerid" = "orders"."customerid"
<004,3>   
<004,4>   customers.customerid
<004,5>   orderid
<004,6>   orderdate
```
The where clause "customers"."customerid" = "orders"."customerid" creates the join on the two tables.

Note: The column customerid must include the table name because that column exists in both tables.
Create a Super Q-pointer called details to retrieve data from the Order Details table in the Microsoft SQL Server sample database, Northwind. This table has a candidate key consisting of two columns, orderid and productid.
```
          details
<001>     q
<002>
<003>     sqlsvr.host:odbc:details
<004,1>   order details
<004,2>   
<004,3>   orderid|*|productid
<004,4>   unitprice
<004,5>   quantity
```
As displayed in the previous example, a user-defined delimiter is specified to map the two candidate key columns to the D3 item-ID. The data from the orderid column is concatenated with an asterisk (*) and the data from the productid column. The output from an AQL list might look like this:
```
"orderid"*"productid"     "unitprice"     "quantity"
10248*72                  34.8000         5
10249*14                  18.6000         9
10251*22                  16.8000         6
10257*39                  14.4000         6
10258*32                  25.6000         6
```

Oracle

Create a Super Q-pointer called emp.ora to retrieve employee data from the Emp table located in the sample schema, scott on an Oracle database.

          emp.ora
<001>     q
<002>
<003>     oracle:odbc:emp.ora
<004,1>   EMP
<004,2>   
<004,3>   empno
<004,4>   
<004,5>   job
<004,6>   mgr
<004,7>   hiredate

Create a Super Q-pointer called emp85.ora to retrieve employee data from the Emp table for employees that were hired before 1985.
```
          emp.ora
<001>     q
<002>
<003>     oracle:odbc:emp85.ora
<004,1>   EMP
<004,2>   hiredate <'01-jan-85'
<004,3>   empno
<004,4>   ename
<004,5>   job
<004,6>   hiredate
```
The where clause hiredate <'01-jan-85' filters out the desired data. The date format used in this where clause is for Oracle databases. This format may not function on other types of databases.

MySQL

Create a Super Q-Pointer called mycustomers to retrieve customer data from the customers file located in the sqldemo schema on a MySQL database. The schema for the customers table looks like this:

          mycustomers
<001>     q
<002>
<003>     oracle:odbc:emp.ora
<004,1>   customers
<004,2>   
<004,3>   customerid
<004,4>   organization
<004,5>   address
<004,6>   city
<004,7>   state
<004,8>   postalcode
<004,9>   contactname
<004,10>  contacttitle
<004,11>  phonenumber
<004,12>  faxnumber
<004,13>  note

Create a Super Q-pointer called mycustomers.irvine to retrieve customers from a customers table for customers that are in Irvine.

          mycustomers.irvine
<001>     q
<002>
<003>     mysql.host:ODBC:customers
<004,1>   customers
<004,2>   city = 'irvine'
<004,3>   customerid
<004,4>   organization
<004,5>   address
<004,6>   city
<004,7>   state
<004,8>   postalcode
<004,9>   contactname
<004,10>  contacttitle
<004,11>  phonenumber
<004,12>  faxnumber
<004,13>  note