Write SQL Statements to Query a Database
You can use Structured Query Language (SQL) statements to control what you import from a database. When you open a database file in JMP, you are actually sending a SQL statement to the database. By default, this statement gets all columns and records in the database table. In some cases, this is too much data. When you are interested only in a subset of the table’s data, you can customize the SQL request to only request the data that you want. After you execute a SQL query, the code for the query is stored in the data table in the SQL table variable.
This section describes how to write SQL statements to retrieve data. To interactively query data without writing SQL statements, use Query Builder. You can also start creating a query in Query Builder and then add your own SQL. See Write SQL Statements in Query Builder.
1. Select File > Database > Open Table.
The Database Open Table window appears (Figure 3.66).
2. Connect to the database if necessary or select an existing database connection. Follow the steps in Connect to a Database.
The Connections box lists data sources to which JMP is connected. The Schemas - Tables box lists schemas for those databases that support them.
Note: The SQL Query that you run in this window operates only on the tables and procedures that are displayed in the left panes of the window. Running unrelated SQL here has no results.
3. From the Database Open Table window, click the Advanced button to open specific subsets of a table.
4. Either type in a valid SQL statement, or modify the default statement. Figure 3.67 shows a default SQL Select statement appropriate for the selected file. See Structured Query Language (SQL): A Reference, for a description of SQL statements that you can use.
Instead, you can add expressions by clicking the Where button and using the WHERE Clause editor to create expressions. See Use the WHERE Clause Editor.
Figure 3.67 Reading All Variables from the Solubility Table Stored in an Excel File
5. Click Execute SQL. A JMP data table appears with the columns that you selected. See Use Table Variables in the Enter and Edit Data section.
6. To see the status of all running queries, select View > Running Queries.
Note that you can enter any valid SQL statement and click Execute SQL to execute the command. Valid SQL varies with the data source and ODBC driver.