For the latest version of JMP Help, visit JMP.com/help.


Using JMP > Import Your Data > Build SQL Queries in Query Builder > Write SQL Statements in Query Builder
Publication date: 04/28/2021

Write SQL Statements in Query Builder

Query Builder enables you to interactively create SQL queries without writing SQL statements. You can also build a query in Query Builder and then add custom statements to the query.

Note: The Query Builder examples are based on a database that is not installed with JMP.

1. Select File > New > Database Query, connect to the database, and select the SQBTest schema. See Connect to a Database.

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. Click the Query Builder red triangle and select Convert to Custom SQL and click OK.

The SQL that Query Builder generated appears on the Custom SQL tab.

7. Click before the semicolon and type the following SQL statement:

WHERE (  (  ( t2.Gender IN  ( 'F' )  )  AND  (  (t2.Age >= 20)  AND  (t2.Age <= 50)  )  )  )

8. Click Run Query to import the data into JMP.

The data table scripts include the custom query.

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.

Want more information? Have questions? Get answers in the JMP User Community (community.jmp.com).