Writing SQL Statements
The Execute Query text box allows you to write not only SELECT and CALL queries, but most SQL statements, including DDL statements such as CREATE TABLE, and DML statements such as INSERT, UPDATE, and DELETE.
You can specify SQL code in the Execute Query text box using the following:
Type (or paste) the SQL code into the text box. The SQL code area does not colorize SQL text or provide any syntax or existence validation. However, it does provide automatic spelling verification. You can erase the contents of the text box using the X icon.
Use the Show History list to select a prior SQL statement. The selected statement is copied into the text box. Upon execution, this statement moves to the top of the Show History list. Note that Show History lists all previously executed statements, including those that failed execution.
Use Table Drag and Drop to construct SQL code in the text box.
You can use the Query Builder, rather than the Execute Query text box, to specify and execute a SELECT query. A SELECT query executed using Query Builder is not shown in Execute Query or listed in Show History.
SQL code in the Execute Query text box can include:
? Input Parameters. If you specify input parameters, such as TOP ? or WHERE Age BETWEEN ? AND ?, the Execute button displays the Enter Parameter Value for Query window, with entry fields for each input parameter in the order specified in the query. For further details on ? input parameters, refer to Executing an SQL Statement in the “Using Dynamic SQL” chapter of this manual.
Whitespace Characters. You can specify multiple blank spaces, single and multiple line returns. The tab key is disabled; when copying code into the SQL code area, existing tabs are converted to single blank spaces. Line returns and multiple blank spaces are not retained.
Comments. The SQL code area supports single-line and multiline comments. Comments are retained and shown in the Show History display. Comments are not shown in the Show Plan Statement Text display or in cached queries.
Queries that Return Multiple Result Sets.
After writing SQL code in the text box, you can click the Show Plan button to check the SQL code without executing the SQL code. If the code is valid, Show Plan displays a Query Plan. If the code is invalid, Show Plan displays an SQLCODE error value and message. You can also use the Show Plan button to display this information for the most-recently-executed SQL code.
To execute the SQL code, click the Execute button.
Table Drag and Drop
You can generate a query by dragging a table (or view) from the Tables list (or Views list) on the left side of the screen and dropping it into the Execute Query text box. This generates a SELECT with a select-item list of all of the non-hidden fields in the table and a FROM clause specifying the table. You can then further modify this query and execute it using the Execute button.
You can also drag and drop a procedure name from the Procedures list on the left side of the screen.
SQL Statement Results
After writing SQL code in the Execute Query text box, you can execute the code by clicking the Execute button. This either successfully executes the SQL statement and displays the results below the code window, or the SQL code fails. If the SQL code fails, it displays an error message (in red) below the code window; pressing the Show Plan button displays the SQLCODE error and error message.
Execute Query SQL code execution is performed as a background process. While the code is executing, the Execute button is replaced by a Cancel button. This allows you to cancel execution of a long-running query.
Query Data Display
The result set is returned as a table with a row counter displayed as the first column (#), if the Row Number box is checked. The remaining columns are displayed in the order specified. The RowID (ID field) may be displayed or hidden. Each column is identified by the column name (or the column alias, if specified). An aggregate, expression, subquery, host variable, or literal SELECT item is either identified by a column alias (if specified), or by the word Aggregate_, Expression_, Subquery_, HostVar_, or Literal_ followed by the SELECT item sequence number (by default).
If a row column contains no data (NULL) the result set displays a blank table cell. Specifying an empty string literal displays a HostVar_ field with a blank table cell. Specify NULL displays a Literal_ field with a blank table cell.
If a selected field is a date, time, timestamp, or %List-encoded field, the displayed value depends on the Display Mode.
The following display features are unique to the Management Portal SQL interface Execute Query results display and Open Table data display:
A stream field of data type %Stream.GlobalCharacterOpens in a new tab displays the actual data (up to 100 characters) as a string. If the data in a stream field is longer than 100 characters, the first 100 characters of the data are displayed followed by an ellipsis (...) indicating additional data.
A stream field of data type %Stream.GlobalBinaryOpens in a new tab displays as <binary>.
A string data field displays the actual data in full with line wrapping as needed.
An integer field is right-aligned within the result table cell. RowID, numeric, and all other fields are left-aligned.
These result display features do not occur when the same query is executed using Dynamic SQL code, the SQL Shell, or Embedded SQL code.
If the specified query returns more than one result set, Execute Query displays these result sets as named tabs: Result #1, Result #2, and so forth.
Query Execution Metrics
If successful, Execute Query displays performance information and the name of the cached query routine. If there is resulting data to display, this appears below the performance information. The execution information includes the Row count, the Performance, the Cached Query showing the cached query name, and Last update specifying the timestamp for the last execution of the query.
Row count: For a DDL statement such as CREATE TABLE, displays Row count: 0 if the operation was successful. For a DML statement such as INSERT, UPDATE, or DELETE, displays the number of rows affected.
For a SELECT, displays the number of rows returned as a result set. Note that the number of rows returned is governed by the Max setting, which may be lower than the number of rows which could have been selected. For multiple result sets, the number of rows for each result set are listed, separated by the / character. A query that specifies one or more aggregate functions (and no selected fields) always displays Row count: 1 and returns the results of expressions, subqueries, and aggregate functions, even if the FROM clause table contains no rows. A query that specifies no aggregate functions and selects no rows always displays Row count: 0 and returns no results, even if the query specifies only expressions and subqueries that do not reference the FROM clause table. A query with no FROM clause always displays Row count: 1 and returns the results of expressions, subqueries, and aggregate functions.
Performance: measured in elapsed time (in fractional seconds), total number of global references, total number of commands executed, and disk read latency (in milliseconds). If a cached query exists for the query these performance metrics are for executing the cached query. Therefore, the first execution of a query will have substantially higher performance metrics than subsequent executions. If the specified query returns more than one result set, these performance metrics are totals for all of the queries.
To analyze these performance metrics in greater depth you can run MONLBL (the monitor line-by-line utility) and specify the Routine Name using the asterisk wildcard as %sqlcq*. Refer to Examining Routine Performance Using ^%SYS.MONLBL.
Cached Query: the automatically generated cached query class name. For example, %sqlcq.USER.cls2 indicating the second cached query in the USER namespace. Each new query is assigned a new cached query name with the next consecutive integer. By clicking this cached query name, you can display information about the cached query and further links to display its Show Plan or to Execute the cached query. (For a DDL statement, see SQL Commands That Are Not Cached.)
Closing the Management Portal or stopping InterSystems IRIS does not delete cached queries or reset cached query numbering. To purge cached queries from the current namespace, invoke the %SYSTEM.SQL.Purge()Opens in a new tab method.
Not all SQL statements result in a cached query. A query that is the same as an existing cached query, except for literal substitution values (such as the TOP clause value and predicate literals) does not create a new cached query. Some SQL statements are not cached, including DDL statements and privilege assignment statements. Non-query SQL statements, such as CREATE TABLE, also display a cached query name. However, this cached query name is created then immediately deleted; the next SQL statement (query or non-query) reuses the same cached query name.
Frozen state: if a query plan is frozen, the frozen plan state (in parentheses) is displayed after the cached query class name. For example (Frozen/Explicit). If the query plan is not frozen, nothing is displayed here.
Last update: the date and time that the last Execute Query (or other SQL operation) was performed. This timestamp is reset each time the query is executed, even when repeatedly executing the identical query.
Successful execution also provides a Print link that displays the Print Query window, which gives you the options to either print or export to a file the query text and/or the query result set. The clickable Query and Result toggles enable you to display or hide the query text or the query result set. The displayed query result set includes the namespace name, the result set data and row count, a timestamp, and the cached query name. (Note that the timestamp is the time when the Print Query window was invoked, not the time when the query was executed.) The Print Query window Print button prints a screenshot of the Print Query window. The Export to File check box displays options to specify an export file format (xml, hdml, pdf, txt, csv) and an export file pathname. The Export option ignores the Query and Result toggles and always exports only the result set data (by default to: exportQuery.pdf) and the row count (by default to: exportQueryMessages.pdf); the query text, namespace, timestamp, and cached query name are not included.
If unsuccessful, Execute Query displays an error message. You can click the Show Plan button to display the corresponding SQLCODE error value and message.
Click Show History to list prior SQL statements executed during the current session. Show History lists all SQL statements invoked from this interface, both those successfully executed and those whose execution failed. By default, SQL statements are listed by Execution Time, with the most recently executed appearing at the top of the list. You can click on any of the column headings to order the SQL statements in ascending or descending order by column values. Executing an SQL Statement from the Show History listing updates its Execution Time (local date and time stamp), and increments its Count (number of times executed).
You can filter the Show History listing, as follows: in the Filter box specify a string then press the Tab key. Only those history items that contain that string will be included in the refreshed listing. The filter string can either be a string found in the SQL Statement column (such as a table name), or it can be a string found in the Execution Time column (such as a date). The filter string is not case-sensitive. A filter string remains in effect until you explicitly change it.
You can modify and execute an SQL statement from Show History by selecting the statement, which causes it to be displayed in the Execute Query text box. In Execute Query you can modify the SQL code and then click Execute. Making any change to an SQL statement retrieved from Show History causes it to be stored in Show History as a new statement; this include changes that do not affect execution, such as changing letter case, whitespace, or comments. Whitespace is not shown in Show History, but it is preserved when an SQL statement is retrieved from Show History.
You can execute (re-run) an unmodified SQL statement directly from the Show History list by clicking the Execute button found to the right of the SQL statement in the Show History listing.
You can also select buttons for Plan, Print, or Delete. The Print button displays the Print Query window, which gives you the options to either print or export to a file the query text and/or the query result set. The Delete button deletes the SQL statement from the history; the Delete All button at the end of the Show History list deletes all the SQL statements in the history.
Note that the Show History listing is not the same as the list of cached queries. Show History lists all invoked SQL statements from the current session, including those that failed during execution.