Skip to main content

InterSystems SQL Basics

This topic provides an overview of the features of InterSystems SQL, especially those that are not covered by the SQL standard or are related to the InterSystems IRIS® data platform unified data architecture. It assumes prior knowledge of SQL and is not designed to serve as an introduction to SQL concepts or syntax.

Tables

Within InterSystems SQL, data is presented within tables. Each table is defined to contain a number of columns. A table may contain zero or more rows of data values. The following terms are roughly equivalent:

Data Terms Relational Database Terms InterSystems IRIS Terms
database schema package
table persistent class
field column property
record row  

For further details, see Introduction to the Default SQL Projection.

There are two basic types of tables: base tables (which contain data and are usually referred to simply as tables) and views (which present a logical view based on one or more tables).

To find out more on how to define tables, see Defining Tables.

To find out more on how to define views, see Defining Views.

In order to make queries against tables more efficient, you can define indexes on tables. See Defining and Building Indexes.

In order to enforce referential integrity you can define foreign keys and triggers on tables. See Defining Foreign Keys and Defining Triggers.

Schemas

SQL schemas provides a means of grouping sets of related tables, views, stored procedures, and cached queries. The use of schemas helps prevent naming collisions at the table level, because a table, view, or stored procedure name must only be unique within its schema. An application can specify tables in multiple schemas.

SQL schemas correspond to persistent class packages. Commonly a schema has the same name as its corresponding package, but these names may differ because of different schema naming conventions or because different names have been deliberately specified. Schema-to-package mapping is further described in SQL to Class Name Transformations.

Schemas are defined within a specific namespace. A schema name must be unique within its namespace. A schema (and its corresponding package) is automatically created when the first item is assigned to it and automatically deleted when the last item is deleted from it.

You can specify an SQL name as qualified or unqualified. A qualified name specifies the schema: schema.name. An unqualified name does not specify the schema: name. If you do not specify the schema, InterSystems IRIS supplies the schema as follows:

To view all the existing schemas within a namespace:

  1. From the Management Portal select System Explorer, then SQL. Select a namespace by clicking the name of the current namespace displayed at the top of the page; this displays the list of available namespaces. Select a namespace.

  2. Select the Schema drop-down list on the left side of the screen. This displays a list of the schemas in the current namespace. Select a schema from this list; the selected name appears in the Schema box.

  3. The applies to drop-down list allows you to select Tables, Views, Procedures, or Cached Queries, or All of these that belong to the schema. After setting this option, click the triangles to view a list of the items. If there are no items, clicking a triangle has no effect.

Queries

Within InterSystems SQL, you view and modify data within tables by means of queries. Roughly speaking, queries come in two flavors: those that retrieve data (SELECT statements), and those that modify data (INSERT, UPDATE, and DELETE statements).

You can use SQL queries in a number of ways:

SELECT queries are described in Querying the Database.

Queries are part of InterSystems IRIS objects or ObjectScript routines.

Privileges

InterSystems SQL provides a way to limit access to tables, views, and so on via privileges. You can define a set of users and roles and grant various privileges (read, write, and so on) to them. See SQL Users, Roles, and Privileges.

SelectMode

InterSystems SQL uses a SelectMode option to specify how data is to be displayed or stored. The available options are Logical, Display, and ODBC. Data is stored internally in Logical mode, and can be displayed in any of these modes. Every data type class can define transformations between internal Logical format and Display format or ODBC format by using the LogicalToDisplay(), LogicalToOdbc(), DisplayToLogical(), and OdbcToLogical() methods. When SQL SelectMode is Display, the LogicalToDisplay transformation is applied, and returned values are formatted for display. The default SQL SelectMode is Logical; thus by default returned values are displayed in their storage format.

SelectMode affects the format that in which query result set data is displayed. It also affects the format in which data values should be supplied, for example in the WHERE clause. InterSystems IRIS applies the appropriate transformation method based on the storage mode and the specified SelectMode. A mismatch between a supplied data value and the SelectMode can result in an error or in erroneous results. For example, if DOB is a date stored in $HOROLOG Logical format, and a WHERE clause specifies WHERE DOB > 2000–01–01 (ODBC format), SelectMode = ODBC returns the intended results. SelectMode = Display generates SQLCODE -146 Unable to convert date input to a valid logical date value. SelectMode = Logical attempts to parse 2000–01–01 as a Logical date value, and returns zero rows.

SelectMode is applied to complete expressions returned from SQL queries, not to individual fields specified in the query. This behavior primarily affects:

  • Queries that apply functions to returned fields: When a function is applied to a field, the SelectMode does not format the field before the function is applied. Instead, the SelectMode formats the result of applying the function to the field. As a result, the function is always performed on the Logical representation of the field.

  • Queries that concatenate a field to a string: When a field is concatenated to a string (or to another field), the SelectMode does not format the field (or the string) before performing the concatenation. Instead, the SelectMode formats the result of the concatenation. As a result, the concatenation is always performed on the Logical representation of the field.

For most data types, the three SelectMode modes return the same results. The following data types are affected by the SelectMode option:

  • Date, Time, and Timestamp data types. InterSystems SQL supports numerous Date, Time, and Timestamp data types (%Library.DateOpens in a new tab, %Library.TimeOpens in a new tab, %Library.PosixTimeOpens in a new tab, %Library.TimeStampOpens in a new tab, and %MV.Date). With the exception of %Library.TimeStampOpens in a new tab, these data types use different representations for Logical, ODBC, and Display modes.

    In Logical mode, InterSystems IRIS displays the dates in the format they were stored in. For most of these data types, dates are stored (and therefore displayed) in the $HOROLOG format. This format consists of an integer count of the number of days from an arbitrary starting date (December 31st, 1840), a comma separator, and an integer count of the number of seconds since midnight of the current day. However, %Library.PosixTimeOpens in a new tab timestamps are not stored in the $HOROLOG format, and are instead stored and displayed as an encoded 64-bit signed integer.

    In ODBC mode, dates and times are always represented as YYYY-MM-DD hh:mm:ss.fff. The %Library.TimeStampOpens in a new tab data type also uses this ODBC format for Logical and Display modes.

    In Display mode, dates and times commonly appear in the format specified by the data type’s FORMAT parameter or the date and time format defaults for the current locale in %SYS.NLS.FormatOpens in a new tab. The default for the American locale is DD/MM/YYYY hh:mm:ss.

  • %List data type. InterSystems IRIS Logical mode stores lists using two non-printing characters that appear before the first item in the list, and appear as a separator between list items. In ODBC SelectMode, list items are displayed with a comma separator between list items. In Display SelectMode, list items are displayed with a blank space separator between list items.

  • Data types that specify VALUELIST and DISPLAYLIST. For required fields, if you are in display mode and you insert a value into a table where the field has a DISPLAYLIST, the display value you enter must exactly match one of the items in the DISPLAYLIST. For non-required fields, non-matching values are converted to NULL values.

  • Empty strings, and empty BLOBs (stream fields). In Logical mode empty strings and BLOBs are represented by the non-display character $CHAR(0). In Display mode they are represented by an empty string ("").

The SQL SelectMode may be specified as follows:

Data Collation

Collation specifies how values are ordered and compared, and is part of both InterSystems SQL and InterSystems IRIS objects.

You can specify a collation type as part of field/property definition. Unless otherwise specified, a string field/property defaults to the namespace default collation. By default, the namespace default collation for strings is SQLUPPER. SQLUPPER collation transforms strings into uppercase for the purposes of sorting and comparing. Thus, unless otherwise specified, string ordering and comparison is not case-sensitive.

You can specify a collation type as part of index definition, or use the collation type of the indexed field.

An SQL query can override the defined field/property collation type by applying a collation function to a field name. The ORDER BY clause specifies the result set sequence for a query; if a specified string field is defined as SQLUPPER, query results order is not case-sensitive.

For further details refer to Collation.

Executing SQL

InterSystems IRIS supports numerous ways to write and execute SQL code. These include:

You can use InterSystems IRIS objects (classes and methods) to:

FeedbackOpens in a new tab