Build SQL Queries in Query Builder
SAS Query Builder is also available for querying SQL databases on SAS servers. See Open SAS Data Sets with SAS Query Builder for details.
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.
|
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.
|
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.
4.
|
Click the Table Snapshot tab for each table to preview the data (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.
|
•
|
To rename a table alias, right-click the table in the Query Builder’s Tables pane and select Change Alias. The alias is updated throughout the query.
|
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.
|
4.
|
In the Left Column list, select g1_books.
|
5.
|
Select Book ID from the Left Column box.
|
6.
|
Select ID from the Right Column box.
|
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.
|
Click OK.
|
•
|
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.
The columns from all data tables appear in the Available Columns list. Prefixes such as t1 and t2 (also called aliases) associate each column with the corresponding data table.
To skip the Query Builder step and import all data, click Import Now instead.
1.
|
Select File > New > Database Query, connect to the database, and select the SQBTest schema. (See Connect to a Database for details.)
|
2.
|
3.
|
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.
|
Tip: To find a column in a long list, enter the name in the search box below Available Columns. The red triangle menu provides options for matching case and searching with regular expressions. To hide the search box, deselect Show Search Box from the Available Columns red triangle menu.
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.
|
You can create a new column from existing columns. For example, you might calculate the mean for two columns and store the mean in a new column. Or date-time values might be in the wrong format. Select the columns in the query, right-click, and select Add Computed Column to construct this new column.
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.
|
From the Available Columns red triangle menu, select Add Computed Column.
|
The Computed Column window appears (Computed Column Window with Formula Editor). The window contains the JMP Formula Editor.
‒
|
Operators and functions are provided in the list on the right side of the Formula Editor (Computed Column Window with Formula Editor). 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 key.
|
8.
|
9.
|
Click OK.
|
10.
|
Right-click the column and select Rename Column.
|
11.
|
12.
|
13.
|
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.
|
5.
|
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.
6.
|
Click Run Query to import the data.
|
7.
|
•
|
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.
|
6.
|
Select Random N Rows and type 5,000.
|
7.
|
Click Run Query to import the data.
|
•
|
To add individual columns to the Included Columns tab, right-click the column and select Include Column. You can also click the Add button.
|
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.
|
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.
|
Select all columns on the Included Columns tab and click the Add Selected Items to Filters button .
|
7.
|
Set the t2.Age filter to ≥ 30.
|
8.
|
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).
9.
|
In the Filters red triangle menu, select All Prompt on Run.
|
10.
|
Click Run Query.
|
11.
|
In the Query Prompts window, click OK to apply the preselected filters and import the data.
|
1.
|
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.
|
7.
|
From the t1.TailNum red triangle menu in the Filters column, select Filter Type, and then select Match Column Values.
|
8.
|
9.
|
10.
|
Click Run Query to import the data.
|
The data table includes only data for rows that are in the Tail Number column.
1.
|
2.
|
From the Filters red triangle menu, select Add Custom Expression.
|
4.
|
1.
|
Select File > New > Database Query, connect to the database, and select the SQBTest schema. (See Connect to a Database for details.)
|
2.
|
3.
|
Click Build Query to show the Query Builder window.
|
4.
|
On the Included Columns tab, click Add All.
|
5.
|
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.
|
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.
On the Query Status tab, view the status of a query as it runs in the background. The query name, SQL statements, and number of processed records appear. You can stop a query at any time and view only the processed records. To view background queries from other JMP windows, select View > Running Queries. The status details are unavailable if you deselect Run queries in the background when possible from the Query Builder preferences.
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.
|
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.
|
4.
|
Click Build Query to show the Query Builder window.
|
5.
|
Click the Add All button on the Included Columns tab.
|
6.
|
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.