Publication date: 08/13/2020

SQL Functions

Note: Database table names that contain the characters $# -+/%()&|;? are not supported.

As SQL Expr(x, <style>)

Description

Converts an expression to code that you can use in an SQL Select statement. Use Expr(...) for literal expressions. Use NameExpr(name) for expressions stored in a variable. Otherwise, the expression returns the expression to convert.

Returns

A string that contains the expression converted to valid SQL syntax for use in an SQL Select statement.

New SQL Query(Connection ("ODBC:connection_string")|("SAS:connection_string"), Select(Column("column", "t1")), From(Table("table", <Schema("schema")>, <Alias("t1")>)), <Options(JMP 12 Compatible(1)|JMP 13 Compatible(1)|Run on Open(1)))>

New SQL Query(Connection("ODBC:connection_string;")|("SAS:connection_string;"), Custom("SELECT col1, col2, col3 FROM table;")), <Options(JMP 12 Compatible(1)|JMP 13 Compatible(1)|Run on Open(1))>

Creates an SQL Query object for the specified connection, columns, data table, or for the custom SQL query.

Returns

A data table that contains the queried data. The data table includes the SQL query string and table scripts for modifying and updating the query.

Arguments

Connection

The string for an ODBC or SAS connection.

Select

The column that you want to select and its alias.

From

The table that is queried and the optional schema and column alias.

Custom

An SQL statement that selects columns from the specified table.

Version

The minimum JMP version required to open the query. If this condition is not met, a message regarding compatibility is written to the log, and the query does not open.

Options

Boolean. JMP 12 Compatible is included in generated scripts when you select the Query Builder preference to create a JMP 12 compatible option or select the corresponding Query Builder red triangle menu option. The option enables JMP 12 users to run a JMP 13 query that might contain compatibility issues. Include Run on Open(1) to run the query when opened rather than opening the query in edit mode.

Example

New SQL Query(
	Connection(
		"ODBC Connection String..."
	),
	QueryName( "g6_Movies" ),
	Select( Column( "ItemNo", "t1" ), Column( "LengthMins", "t1" ), Column( "Genre", "t1" ) ),
	From( Table( "g6_Movies", Schema( "SQBTest" ), Alias( "t1" ) ) )
) << Run Background( On Run Complete( dt = queryResult ) );
 
Show( dt );

Note that Query Builder creates a symbol called queryResult in the context of an On Run Complete() script. This is a reference to the data table imported by the query. queryResult enables you to assign a global variable to the table for later use.

Query(<<dt1|Table(dt1, alias1)>, ..., <dtN, aliasN)>>, <private | invisible>, <scalar>, sqlStatement )

Description

Performs a SQL query on selected data tables.

Returns

The result of the query, either a data table or a single value.

Arguments

dt1, dtN

(Optional) A variable that has been assigned to the data table.

Table

(Optional) Passes a reference to the data table.

alias1, aliasN

Specifies the alias of the database table.

private

(Optional) Avoids showing the resulting data table. Using a private data table speeds the process of getting to the data; it does not save the computer from allocating the memory necessary to hold the data table data.

invisible

(Optional) Hides the resulting data table from view. The data table appears only in the JMP Home Window and the Window menu. Hidden data tables remain in memory until they are explicitly closed, reducing the amount of memory that is available to JMP. To explicitly close the hidden data table, call Close(dt), where dt is the data table reference.

scalar

(Optional) Indicates that the query returns a single value.

sqlStatement

Required. The SQL statement, most likely a SELECT statement. The statement must be the last argument.

Example

The following example selects all data for students who are older than 14 years of age.

dt = Open( "$SAMPLE_DATA/Big Class.jmp" );
result = Query( Table( dt, "t1" ), "SELECT * FROM t1 WHERE age > 14;" );

Notes

See SQL Functions Available for JMP Queries for more information about SQLite commands that Query() supports. See Write a SQL Query in the Scripting Guide for more examples.

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