Execute Query Options

Execute Query Options

The SQL execution interface has the following options:

• The Select Mode drop-down list with a SELECT specifies the format that the query should use to supply data values (for example, in the WHERE clause) and to display data values in the query result set. The options are Display Mode (the default), ODBC Mode, and Logical Mode. For further details on these options, refer to Data Display Options.

  The Select Mode drop-down list with an INSERT or UPDATE allows you to specify whether input data will be converted from display format to logical storage format. For this data conversion to occur, the SQL code must have been compiled with a select mode of RUNTIME. At execution time, the Select Mode drop-down list must be set to Logical Mode. For further details, refer to the INSERT or UPDATE statements.

  Select Mode is meaningful for data types whose Logical storage format differs from the desired display format (Display or ODBC), such as InterSystems IRIS dates and times and ObjectScript %List structured data.

• The Max field allows you to limit how many rows of data to return from a query. It can be set to any positive integer, including 0. Once you set Max, that value is used for all queries for the duration of the session, unless explicitly changed. The default is 1000. The maximum value is 100,000, which is the default if you enter no value (set Max to null), enter a value greater than 100,000, or a non-numeric value. You can also limit the number of rows of data to return by using a TOP clause. Max has no effect on other SQL statements, such as DELETE.

If you click the more option, the SQL execution interface displays the following additional options:

• Dialect: the dialect of SQL code. Available values are IRIS, Sybase, and MSSQL. The default is IRIS. Sybase and MSSQL are described in the InterSystems Transact-SQL (TSQL) Migration Guide. Note that the dialect you select becomes the user customized default the next time you access the Management Portal.

  If Dialect is Sybase or MSSQL, you can specify multiple SQL commands in the Execute Query text box. All insert, delete, and update commands are executed first, then SELECT commands in the order specified. Multiple result sets are returned in separate tabs.

• Row Number: a check box specifying whether to include a row count number for each row in the result set display. Row Number is a sequential integer assigned to each row in the result set. This is simply a numbering of the returned rows, it does not correspond to either the RowID or the %VID. The row number column header name is #. The default is to not display row numbers.

• Execute Query in the foreground: a check box specifying whether or not to run the query in the foreground. Simple queries run in the foreground are often significantly faster than those run in the background. However, long queries run in the foreground may cause the Management Portal to be unresponsive during query execution. The default is to run all queries in the background.

All of these options are user customized.