Import a subset of the data by using SQL

When you import data from a database with ODBC, you can use Structured Query Language (SQL) expressions to import only the records (rows) that you want. SQL is used in many database applications. Minitab does not act on the SQL expression, but only passes it to the database.

In This Topic

Select the fields to import
Guidelines for SQL expression syntax
Examples of SQL expressions for Microsoft^® Access
Why some SQL functions might not work

Select the fields to import

Choose File > Query Database (ODBC).
Select a data source. If prompted, select the database.
In the Query Database (ODBC) dialog box, from the Available tables drop-down list, select the table that you want to query.
Use the arrow button to move the fields that you want to import to Selected fields.
Click Use rows.
In Use rows, enter an expression that defines the records that you want to import. These records become rows in Minitab. Expressions start with the keyword "where" and contain field names, logical operators (such as "And" and "=" ), SQL functions, and values. For more information about supported SQL functions, see the documentation for your database application.
Click OK in each dialog box.

Guidelines for SQL expression syntax

Syntax rules vary between the ODBC drivers for the different database applications. If you need help with syntax, contact your database administrator.

Text values

The syntax for text values must follow the syntax rules for the ODBC driver, as follows:

For Microsoft^® Access and many other databases, enclose text values in single quotation marks (for example, 'Morristown').
For Oracle or SQL Server databases, enclose text values in two sets of double quotation marks (for example, ""Morristown"").

Numeric values

Do not enclose numbers, such as 300, in any special characters.

Date/time values

For Access and dBASE databases, enclose dates in pound signs (for example, #12/31/2016#). For other databases, see the application's documentation. Instead of entering a specific date, you can also use a date/time function.

Field names

Field delimiters vary by ODBC driver. If you enter the field name by selecting it from the list of fields in the Use rows dialog box, Minitab encloses the field in the delimiter that is appropriate for your ODBC driver.

Functions

To indicate that the function is an ODBC function, and not a function that is native to the database application that you are importing from, use brackets and the letters "fn" (for example, {fn SQRT(128)}). If you select functions from the list in the Use rows dialog box, Minitab automatically adds the necessary syntax.

Wildcard characters

For wildcards in all SQL queries, use a percent symbol (%) instead of an asterisk (*), and use an underscore (_) instead of a question mark (?).

Examples of SQL expressions for Microsoft^® Access

The syntax for database applications other than Access might be different.

Note

For best results, do not copy and paste these expressions from the table. Type them directly in the dialog box.

Data to import	SQL expression
Records for the first quarter of 2017	`where DATE >= #1/1/17# And DATE < #4/1/17#`
Records for employees hired since January 1, 2010 who are in the Finance department	`where DATE_HIRED > #1/1/10# And DEPT = 'Finance'`
Data from the past 30 days	where `DateFieldName` > Now() - 30
Yesterday's data	`where (Format(Now-1,'mm-dd-yy')=Format(DateFieldName,'mm-dd-yy'))`

Why some SQL functions might not work

SQL has ODBC functions that you can use to build expressions. However, a specific ODBC driver might not support all ODBC functions. For example, the Microsoft^® Access ODBC driver does not support the TIMESTAMPDIFF function. If you receive an error message, the message originates from the ODBC driver, not from Minitab.

The following is an example of the error message you receive when you use ODBC with the TIMESTAMPDIFF function to query Microsoft^® Access:

"SQL Error State: 37000, Native Error Code: FFFFF3E2, ODBC Error: [Microsoft]
[ODBC Microsoft® Access Driver] Undefined function 'TIMESTAMPDIFF' in expression."

Access supplies an alternative function for TIMESTAMPDIFF. The alternative function is DATEDIFF( ). The following query retrieves three columns from an employee record. The first column is the elapsed number of days from the employee's start date through today. The second and third columns are the employee's name and start date.

ODBC;
  Connect "DSN=Test;DBQ=Q:\Trumparc.mdb;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;PWD=msl;SystemDB=Q:\secured.mdw;UID=msl;";
SQLString "SELECT DateDiff('y', `StartDate`, Now()),`Name`,`StartDate` FROM `Employee`".