T2.4: Uses IRIS-specific Capabilities

Overview

InterSystems SQL uses the SelectMode option to control how data values are displayed in query results and how input values should be formatted in predicates.

• Available modes: Logical (0), ODBC (1), Display (2)
• Internal storage: Data is always stored internally in Logical mode

Transformation Methods

SelectMode applies transformations using methods defined in data type classes:

• LogicalToDisplay()
• LogicalToOdbc()
• DisplayToLogical()
• OdbcToLogical()

Affected Data Types

SelectMode primarily affects:

• Date, Time, and Timestamp data types
• %List encoded data

Mode-Specific Display Formats

Logical mode:

• Dates: $HOROLOG format (integer days from December 31, 1840, comma, seconds since midnight)
• %List data: $LISTBUILD statements like $lb("White","Green")

ODBC mode:

• Dates and times: Standardized as YYYY-MM-DD hh:mm:ss.fff
• Designed for external tool compatibility

Display mode:

• Dates: Locale-specific formats defined in %SYS.NLS.Format or data type's FORMAT parameter

Input and Output Implications

SelectMode affects both output formatting and input requirements for WHERE clause predicates.

Mismatch example: If DOB is stored in $HOROLOG format and WHERE clause specifies WHERE DOB > 2000-01-01 (ODBC format):

• SelectMode=ODBC: Returns intended results
• SelectMode=Display: Generates SQLCODE -146 (unable to convert date input)
• SelectMode=Logical: Attempts to parse ODBC date as Logical format, returning zero rows

Internal Processing

• SelectMode is applied to complete expressions, not individual fields
• Functions and concatenations performed on Logical representations before SelectMode formatting
• SQL statements run internally in Logical mode regardless of SelectMode
• ORDER BY clauses, functions, and SQLPROC methods all operate on Logical values

Setting SelectMode in Dynamic SQL

With %SQL.Statement, SelectMode is set via the %SelectMode property:

• Example: `##class(%SQL.Statement).%New(2,"Sample")` sets Display mode with Sample schema

Overview

InterSystems SQL provides the arrow syntax operator (->) as a powerful shorthand for accessing values from related tables without writing complex explicit JOIN statements.

• Operation type: Performs a LEFT OUTER JOIN operation
• Use cases: Reference properties (column contains ID of referenced table) or relationship properties of parent tables
• Limitations: Cannot be used in ON clauses or with all foreign key types

Basic Usage

The arrow operator allows intuitive navigation across table relationships similar to object dot notation.

Example comparison:

• Explicit JOIN: `SELECT Sample.Employee.Name, Sample.Company.Name FROM Sample.Employee LEFT OUTER JOIN Sample.Company ON Sample.Employee.Company = Sample.Company.ID`
• Arrow syntax: `SELECT Name, Company->Name FROM Sample.Employee`

Valid Placement

Arrow operator can be used anywhere a column expression is valid:

• SELECT lists
• WHERE clauses
• GROUP BY clauses
• ORDER BY clauses

Compound Arrow Syntax

Supported for traversing multiple relationships:

• Example: `Film->Category->CategoryName` accesses three related tables in sequence

Child Table References

When referencing child tables:

• Arrow syntax produces multiple rows in the result set for each parent row
• Example: `SELECT LineItems->amount FROM Orders` is equivalent to `SELECT L.amount FROM Orders O LEFT JOIN LineItems L ON O.id=L.custorder`

Privilege Requirements

Arrow syntax requires appropriate SELECT privileges on all referenced data.

With column-level privileges:

• Need SELECT privilege on both the ID field of the referenced table and the referenced column
• Example: `Company->Name` requires privileges on Company.ID and Company.Name

Compatibility

• Can be combined with explicit JOIN syntax in the same query
• Works with sharded tables