JSL Syntax Reference • JSL Functions • SQL Functions
Previous
 • 
Next
SQL Functions
Note: Database table names that contain the characters $# -+/%()&|;? are not supported.
As SQL Expr(x)
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.
New SQL Query(Connection ("ODBC:connection_string")|("SAS:connection_string"), Select(Column("column", "t1")), From(Table("table", <Schema("schema")>, <Alias("t1")>))
New SQL Query(Connection("ODBC:connection_string;")|("SAS:connection_string;"), Custom("SELECT col1, col2, col3 FROM table;"))
Description
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.
Run Background(<OnRunComplete(script), <private>>, <OnRunCanceled(script)>, <OnError(script)>
Description
Runs the SQL query in the background. The running query is not displayed.
Returns
Null (or the data table object, if OnRunComplete is included).
Arguments
OnRunComplete
Optional. Specifies a script to run after the query is complete. To get the resulting data table, include OnRunComplete.
private
Optional. Does not open the resulting data table. Specify only with OnRunComplete.
OnRunCanceled
Optional. Specifies a script to run after the user cancels the query.
OnError
Optional. Specifies a script to run if an error occurs.
Notes
All queries except for SAS queries run in the background based on the Query Builder preference “Run the queries in the background when possible”, which is selected by default. For SAS queries, Run Background() is ignored.
You can include a .jmpquery file in a script and run the query in the background using the <<Run Background message.
query = Include( "C:\Queries\movies.jmpquery");
query <<Run Background();
Run Foreground(<OnRunComplete(script), <private>>, <OnRunCanceled(script)>, <OnError(script)>
Description
Runs the SQL query in the foreground.
Returns
A data table that opens when the query is finished.
See Run Background(<OnRunComplete(script), <private>>, <OnRunCanceled(script)>, <OnError(script)> for details about the arguments.