Build SQL Queries in Query Builder
Note: Database table names that contain the characters $# -+/%()&|;? are not supported.
1.
Select File > Database > Query Builder to display the Select Database Connection window.
2.
If the desired data source is not listed in the Connections box, click New Connection to choose a data source. The method of choosing a data source depends on your operating system and the ODBC driver. See Connect to a Database for details.
Select the Database Schema
1.
Select File > New > Database Query, connect to the database, and select the SQBTest schema. (See Connect to a Database for details.)
2.
In the Select Tables for Query window, select g6_Customers from the Available Tables list, and then click Primary.
The Columns tab shows that CustID is the primary key. The data is indexed, which speeds up the query.
3.
Select g6_Movies and g6_Rentals from the Available Tables list, and then click Secondary.
The Left Join icon indicates that the tables were automatically joined (Selecting Primary and Secondary Tables). CustID is the primary key in g6_Customers and matches a foreign key in one of the other tables.
Tip: After you add a primary or secondary table, click Add Related Tables to add tables that have matching columns. The button is unavailable when no related tables are found or when a primary or secondary table is not selected.
Selecting Primary and Secondary Tables shows the completed window.
Selecting Primary and Secondary Tables
4.
Click the Table Snapshot tab for each table to preview the data (Matched Rows on the Table Snapshot Tab).
Matched Rows on the Table Snapshot Tab
5.
Below the primary and secondary tables, click Preview Join to see a preview of the table that was created from the specified joins.
The icon next to a secondary table indicates that the table is not joined in the query. Click the Edit Join button to specify the columns to join. If you cannot find columns to join, click the Remove button to remove the table. See Edit the Conditions for Joining Tables for details.
Click Change Data Source to query a different schema or database.
If there are no keys, or when column names do not match, click the Edit Join button to specify the columns to join.
1.
Select File > New > Database Query, connect to the database, and select the SQBTest schema. (See Connect to a Database for details.)
2.
In the Select Tables for Query window, select g1_books as the Primary table and g1_charges as the Secondary table.
3.
Select g1_charges in the Secondary table pane and click the Edit Join button .
5.
Select Book ID from the Left Column box.
6.
Select ID from the Right Column box.
The Edit Condition Window
8.
Click Next.
To do a full join and import all rows, you would select Include non-matching rows from g1_charges. If only one of the non-matching options is available, the database does not support full joins.
9.
Note: The OK button is unavailable until all of the secondary tables are joined.
Deselect Auto join Database Tables from the Select Tables for Query red triangle menu above the primary table.
If you frequently query large databases, deselect Automatically join tables added to a query in Preferences > Query Builder to prevent memory issues.
After selecting database tables (and joining them if necessary), click Build Query to open the Query Builder window. You can continue to refine the query by selecting which columns to include and specifying criteria for sampling and filtering. You can also save the query to edit and run again later.
1.
Select File > New > Database Query, connect to the database, and select the SQBTest schema. (See Connect to a Database for details.)
2.
In the Select Tables for Query window, select g6_Customers and click Primary.
3.
Select g6_Movies and g6_Rentals and click Secondary.
4.
Click Build Query to show the Query Builder window.
5.
In the Available Columns box, select t1.Gender, t1.Age, t1.Married, t1.KidsUnder12, t2.Rating, and t2.Genre.
6.
Click Add on the Included Columns tab.
Selected Columns
7.
Select the SQL tab below the columns to view the SQL statements for your query. This code is saved as a data table property after you run the query.
8.
Click Save in the lower right corner.
Your work is saved as g6_Customers.jmpquery, which you can open later to return to this point or to run the query.
9.
Click Run Query to import the data.
To rename an alias, right-click the table in the Tables pane and select Change Alias. You can also right-click the table name in the Select Tables for Query window to rename the alias. The change appears throughout the query. Aliases are not case sensitive.
To view the number of records being read during the query, deselect the preference or view the Query Status tab. The query runs in the background unless you deselect Run queries in the background when possible from the Query Builder ODBC preferences. You can also check the progress of all ODBC queries by selecting View > Running Queries.
Deselect Update preview automatically if the preview loads too slowly. Click Update below the Query Preview tab to update the data view. Consider changing the Preview options in the JMP Query Builder preferences if you frequently work with large databases.
To omit duplicate rows from the database, select Distinct rows only on the Included Columns tab.
1.
Select File > New > Database Query, connect to the database, and select the SQBTest schema. (See Connect to a Database for details.)
2.
In the Select Tables for Query window, select g6_Rentals as the Primary table and g6_Movies as the Secondary table.
3.
Click Build Query to show the Query Builder window.
Computed Column Window with Formula Editor
The Operators list does not provide a Concatenate (||) operator. You must type the formula in the Formula Editor box.
5.
From the g6_Rentals list on the left, select Days Out and click the multiplication key.
Computed Column
First Portion of the Formula
8.
Select g2_Movies from the list on the left and then select LengthMins.
Second Portion of the Formula
9.
11.
Type MaximumTimesWatched and click OK.
12.
With MaximumTimesWatched selected, click Add.
13.
Select t2.Name and click Add.
14.
If necessary, select Update preview automatically on the Query Preview tab.
1.
Select File > New > Database Query, connect to the database, and select the SQBTest schema. (See Connect to a Database for details.)
2.
In the Select Tables for Query window, select g6_Movies as the Primary table and g6_Rentals as the Secondary table.
3.
Click Build Query to show the Query Builder window.
4.
In the Available Columns box, select t1.Name and t2.ItemNo and click Add.
5.
Select t2.ItemNo and select Count from the Aggregation list.
The Group By check box is selected for t1.Name (Grouped Columns). All instances of a specific movie name will be grouped into one row.
Grouped Columns
6.
Click Run Query to import the data.
7.
In the data table, right-click the Count-ItemNo column and select Sort > Descending.
Sorted Count-ItemNo Column
To clear the grouped rows, select None from the column’s Aggregation list.
1.
Select File > New > Database Query, connect to the database, and select the SQBTest schema. (See Connect to a Database for details.)
2.
In the Select Tables for Query window, select g6_Rental as the Primary table and g6_Movies as the Secondary table.
3.
Click Build Query to show the Query Builder window.
4.
Click the Add All button on the Included Columns tab.
5.
Click the Sample tab and select Sample this result set.
6.
Select Random N Rows and type 5,000.
7.
Click Run Query to import the data.
1.
Select File > New > Database Query, connect to the database, and select the SQBTest schema. (See Connect to a Database for details.)
2.
In the Select Tables for Query window, select g6_Rentals from the Available Tables list, and then click Primary.
3.
Select g6_Customers and G6_Movies and then click Secondary.
4.
Click Build Query to show the Query Builder window.
5.
In the Available Columns box, select t2.Gender, t2.Age, and t3.Genre, and then click Add on the Included Columns tab.
6.
7.
Set the t2.Age filter to ≥ 30.
The % wildcards match any number of characters before and after “com”. On the Query Preview tab, notice that movies in both the RomCom and Comedy genres are shown (Selecting Filters).
Selecting Filters
10.
Click Run Query.
11.
In the Query Prompts window, click OK to apply the preselected filters and import the data.
1.
Select Help > Sample Data Library and open Air Traffic.jmp.
2.
Select File > New > Database Query, connect to the database, and select the SQBTest schema. (See Connect to a Database for details.)
3.
In the Select Tables for Query window, select g5_AIRLINE_ONTIMEPERF from the Available Tables list, and then click Primary.
4.
Click Build Query to show the Query Builder window.
5.
Click Add All on the Included Columns tab.
6.
Select t1.TailNum on the Included Columns tab and click the Add Selected Items to Filters button .
7.
From the t1.TailNum red triangle menu in the Filters column, select Filter Type, and then select Match Column Values.
8.
Select Air Traffic below Match values from table.
9.
Select the Tail Number column and then select All rows (38,118) from the list.
10.
Click Run Query to import the data.
Writing a Custom Filter Expression
1.
Select File > New > Database Query, connect to the database, and select the SQBTest schema. (See Connect to a Database for details.)
2.
In the Select Tables for Query window, select g4_bigclass as the Primary table.
3.
Click Build Query to show the Query Builder window.
5.
Select t1.age and t1.height and click the Order by the Selected Items button .
The columns are sorted by age first (youngest to oldest) and then height (shortest to tallest).
Selecting the Order By Columns
6.
In the Order By outline, select t1.height and then click the Sort the values in descending order button below the columns.
The height column is sorted from tallest to shortest.
7.
Select t1.height and click the Move the Selected Items Up in the List button .
The height column is sorted first. Values in the age column are sorted within each level of height. For a height of 68, age is sorted from 14 to 17.
Result of Reordering Columns
Save your query as a .jmpquery file to modify or run the query later. You are prompted to enter the password if the server connection string does not specify it. The .jmpquery file can also be opened and run by a JSL script.
After you build a query, click Save in the lower right corner to save the settings as a .jmpquery file. Clicking Save again overwrites the file with your latest changes. Clicking Save As saves the query in a new .jmpquery file.
You can also use the .jmpquery file as a starting point for a new query. This option prevents you from overwriting your original query if you accidentally click Save. It works the same as clicking Save As after you modify the query.
1.
Right-click the .jmpquery file in the JMP Home Window’s Recent Files list.
2.
Select Open as Template.
3.
Modify the query and click Save to create a new .jmpquery file.
Note: The .jmpquery file contains database login information. You must have set up the database connection before running the query. See Connect to a Database for details.
After you specify the columns and data to import, click Run Query to open the data in a data table. The SQL statements are saved as a table variable. The following scripts are available:
1.
Select File > New > Database Query, connect to the database, and select the SQBTest schema. (See Connect to a Database for details.)
2.
In the Select Tables for Query window, select g6_Rentals as the Primary table.
3.
Select g6_Movies and g6_Customers as the Secondary tables.
4.
Click Build Query to show the Query Builder window.
5.
Click the Add All button on the Included Columns tab.
6.
From the Query Builder red triangle menu, select Convert to Custom SQL and click OK.
8.
Click Run Query to import the data into JMP.
Note: If you select Revert to Interactive from the red triangle menu, the changes that you made on the Custom SQL tab are not saved. If you save the custom query and reopen it, Revert to Interactive is not available.
See Structured Query Language (SQL): A Reference for a brief primer of SQL statements.