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.
Figure 2.33 Selected Columns
Notes: 
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.
Tips: 
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. Consider limiting the maximum number of rows that can be previewed. In the JMP Query Builder preferences, change the value of Maximum number of rows for previews.
To omit duplicate rows from the database, select Distinct rows only on the Included Columns tab.
If you add a JMP 13 feature to a query, that query will no longer load in JMP 12. If you are using JMP 13, but you need to create queries that will still run in JMP 12, select Keep this query compatible with JMP 12 in the Query Builder Preferences. After you select the option, features that create compatibility problems are hidden in Query Builder.
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.
The Computed Column window appears (Figure 2.34). The window contains the JMP Formula Editor.
Figure 2.34 Computed Column Window with Formula Editor
Notes: 
Operators and functions are provided in the list on the left side of the Formula Editor (Figure 2.34). In some instances, you might need to change the server type based on your database.
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 button.
Figure 2.35 Computed Column
Figure 2.36 First Portion of the Formula
8.
Select g6_Movies from the list on the left and then select LengthMins.
Figure 2.37 Second Portion of the Formula
9.
11.
Type MaximumTimesWatched and click OK.
12.
In the Available Columns list, select MaximumTimesWatched and click Add.
13.
Select t2.Name and click Add.
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 (Figure 2.38). All instances of a specific movie name will be grouped into one row.
Figure 2.38 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.
Figure 2.39 Sorted Count-ItemNo Column
Tips: 
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_Rentals 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.
Tips: 
Age > 14 matches ages that are greater than 14.
12 ≤ Age ≤ 17 matches ages that are between 12 and 17.
Either NULL or not NULL matches missing values and non-missing values.
(  (  ( t2.Gender IN  ( 'F' )  )  AND  (  (t2.Age >= 20)  AND  (t2.Age <= 50)  )  )  )
matches the F Gender. It also matches Age between 20 and 50.
Note: List Box, Manual List, and Check Box List include a Not in list option that enables you to retrieve rows that do not match the selected values.
Matches a string that contains or does not contain the specified value. Contains Comedy OR Romance matches Comedy and Romance.
Matches a string that is similar to or not similar to the specified value. Supports the % wildcard (zero or more characters) and _ wildcard (exactly one character).
Genre Like %com matches any number of characters before “com”, as in “RomCom”. To also match “Comedy”, use %com% or Contains com.
Matches the specified column value. Select the table and then select the columns. The Select non-matching option enables you to filter all rows except for the selected rows. See Import Matching Data from an Existing Data Table for an example.
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.
7.
Set the t2.Age filter to ≥ 30.
8.
From the t3.Genre red triangle menu, select Like, type %com%, and press Enter.
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 (Figure 2.40).
Figure 2.40 Selecting Filters
10.
Click Run Query.
11.
In the Query Prompts window, click OK to apply the preselected filters and import the data.
Notes: 
The Query Builder preference called Retrieve category levels for tables whose size cannot be determined is selected by default so that JMP automatically retrieves the levels. If you deselect the preference, the Contains fallback filter type in the Query Builder preferences is selected.
If the categorical column has more than 1 million rows, JMP does not automatically retrieve the unique category levels for the filtered column. The Query Builder preference called Maximum rows in table for which category levels will be automatically retrieved supports a minimum of -1 (no limit) and a maximum value of 1 billion rows.
The default filter for categorical columns is a list box unless the Keep this query compatible with JMP 12 Query Builder preference is selected.
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 Add Selected Items to Filters .
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.
(  (  ( t2.Gender IN  ( 'F' )  )  AND  (  (t2.Age >= 20)  AND  (t2.Age <= 50)  )  )  )
This expression matches the F Gender. It also matches Age between 20 and 50.
Figure 2.41 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 Order by the Selected Items .
The columns are sorted by age first (youngest to oldest) and then height (shortest to tallest).
Figure 2.42 Selecting the Order By Columns
6.
In the Order By outline, select t1.height and then click Sort the values in descending order below the columns.
The height column is sorted from tallest to shortest.
7.
Select t1.height and click Move the Selected Items Up in the List .
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.
Figure 2.43 Result of Reordering Columns
Distribution( Column( :age, :gender ) );

Help created on 7/12/2018