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 Appendix A, “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.