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 are interested in. SQL is used in many database applications. Minitab does not act on the SQL expression, but only passes it to the database.

Select the fields to import

  1. Choose File > Query Database (ODBC).
  2. Choose a machine or file data source. If prompted, choose the database.
  3. In the Query Database (ODBC) dialog box, choose the table you want to query from the Available tables drop-down list.
  4. Select the fields you want to import so that they appear under Selected fields.
  5. Click Use rows.
  6. In the Use rows field, enter an expression that defines which records from the fields will be imported. 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.
  7. Click OK in each dialog box.

Guidelines for SQL expression syntax

Syntax rules vary between the ODBC drivers for the different database applications; there is no standard syntax. You may need assistance from your database administrator to determine the appropriate syntax to use.

Expression element Syntax rule
Text values
This syntax differs from other parts of Minitab, where text values should be enclosed by double quotation marks. This syntax must follow the syntax rules for the ODBC driver, not Minitab rules.
  • For Microsoft Access and many other databases, enclose text values in single quotation marks, as in, 'Morristown'.
  • For Oracle or SQL Server databases, enclose text values in two sets of double quotation marks, as in, ""Morristown"".
Numeric values Do not enclose numbers, such as 300, with any special characters.
Date/time values For Access and dBASE databases, enclose dates in pound signs, as in #12/31/2012#. For other databases, see the application's documentation. Or, 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 appropriate for your ODBC driver.
Functions Enclose functions with braces and the characters "fn" as follows: {fn SQRT(128)}. This syntax identifies the function as an ODBC function, as opposed to a function native to the database application you are importing from. If you select functions from the list in the Use rows dialog box, Minitab automatically adds the necessary enclosure characters.
Wildcard characters For all SQL queries, the wild card characters are percent (%) and underscore (_), instead of asterisk (*) and question mark (?), respectively.

Examples of SQL expressions for Microsoft Access

The syntax for databases other than Access may be different.

Data to import SQL expression
Records for the first quarter of 2014
where DATE >= #1/1/14# And DATE < #4/1/14#
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'))
Note

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

Why some SQL functions may not work

Structured Query Language (SQL) is used in many database applications. SQL has defined ODBC functions that you can use to build expressions. However, a specific ODBC driver may not support all available ODBC functions. For example, the ODBC driver supplied by Microsoft for Microsoft Access does not support the TIMESTAMPDIFF function. If you get an error message, the message originates from the ODBC driver, not from Minitab.

Here 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 does supply an alternative function for TIMESTAMPDIFF and that is DATEDIFF( ). In the following example, the 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, respectively.

ODBC;
  Connect "DSN=Test;DBQ=Q:\Trumparc.mdb;"&
   "DriverId=25;FIL=MS Access;Max" & "BufferSize=2048;PageTimeout=5;PWD=msl;"&
   "SystemDB=Q:\secured.mdw;UID=msl;";
  SQLString "SELECT DateDiff('y', `StartDate`, Now()),"&
   "`Name`,`StartDate` FROM `Employee`".
By using this site you agree to the use of cookies for analytics and personalized content.  Read our policy