Writing a SQL Query

The Query() function enables you to manipulate JMP data tables using SQL statements. The SQL statement is the only required argument to Query(). However, most interesting SQL statements refer to one or more data tables. Any data table that the SQL statement refers to must be passed in using a table reference argument.

A table reference argument can be a data table reference (dt in the following example):

dt = Open( "$SAMPLE_DATA/Big Class.jmp" );

result = Query( dt, "SELECT * FROM 'Big Class' WHERE age > 14;" );

The SQL statement in the preceding example includes the name of the JMP data table. If the data table path is long, you might want to use an alias for the data table in the SQL statement. To use an alias, pass the table reference as a Table() argument:

dt = Open( "$SAMPLE_DATA/Big Class.jmp" );

result = Query( Table( dt, "t1" ), "SELECT * FROM t1 WHERE age > 14;" );

Using Table() to alias your data tables also enables you to write SQL statements for multiple queries with tables that have different names without rewriting the statements.

Private and Hidden Data Tables

If you want the data table that is produced by Query() to be either private or hidden, you can pass either Private or Invisible as arguments to Query().

dt = Open( "$SAMPLE_DATA/Big Class.jmp" );

result = Query( dt, invisible, "SELECT * FROM 'Big Class' WHERE age > 14;" );

Returning a Single Value

The Query() function normally returns the result of the query as a JMP data table. However, sometimes you might write a SQL query that returns a single value; you would rather have Query() return that value instead of placing the value in a JMP data table. You can do that by passing the Scalar argument.

dt = Open( "$SAMPLE_DATA/Big Class.jmp" );

result = Query(

	Table( dt, "t1" ),

	Scalar,

	"SELECT AVG(height) FROM t1 WHERE age > 14;"

);

The preceding query returns 65, the average height of students older than 14.