%INTERNAL (SQL)

A format-transformation function that returns an expression in LOGICAL format.

Synopsis

%INTERNAL(expression)

%INTERNAL expression

Description

%INTERNAL converts expression to LOGICAL format, regardless of the current select mode (display mode). The LOGICAL format is the in-memory format of data (the format upon which operations are performed). %INTERNAL is commonly used on a SELECT list select-item.

%INTERNAL can be used in a WHERE clause, but this use is strongly discouraged because using %INTERNAL prevents the use of indexes on the specified field, and %INTERNAL forces all comparisons to be case-sensitive, even if the field has default collation.

Applying %INTERNAL changes the column header name to a value such as “Expression_1”; it is therefore usually desirable to specify a column name alias, as shown in the examples below.

%INTERNAL converts a value of data type %Date to an INTEGER data type value. %INTERNAL converts a value of data type %Time to a NUMERIC (15,9) data type value. This conversion is provided because an ODBC or JDBC client does not recognize InterSystems IRIS logical %Date and %Time values.

Whether %INTERNAL converts a date depends on the data type returned by the date field or function. %INTERNAL converts CURDATE, CURRENT_DATE, CURTIME, and CURRENT_TIME values. It does not convert CURRENT_TIMESTAMP, GETDATE, GETUTCDATE, NOW, and $HOROLOG values.

A stream field cannot be specified as an argument to ObjectScript unary functions, including all format-transformation functions, with the exception of %INTERNAL. The %INTERNAL function permits a stream field as an expression value, but performs no operation on that stream field.

%INTERNAL is an InterSystems SQL extension.

To convert an expression to DISPLAY format, regardless of the current select mode, use the %EXTERNAL function. To convert an expression to ODBC format, regardless of the current select mode, use the %ODBCOUT function.

For further details on display format options, refer to “Data Display Options” in the “InterSystems IRIS SQL Basics” chapter of Using InterSystems SQL.

Arguments

expression

The expression to be converted. A field name, an expression containing a field name, or a function that returns a value in a convertible data type, such as DATE or %List.

Examples

The following example returns Date of Birth (DOB) data values in the current select mode format, and the same data using the %INTERNAL function:

SELECT TOP 5 DOB,%INTERNAL(DOB) AS IntDOB
FROM Sample.Person

The following examples show the two syntax forms for this function; they are otherwise identical. They specify the %EXTERNAL (DISPLAY format), %INTERNAL (LOGICAL format), and %ODBCOUT (ODBC format) of a %List field:

SELECT TOP 10 %EXTERNAL(FavoriteColors) AS ExtColors,
              %INTERNAL(FavoriteColors) AS IntColors,
              %ODBCOUT(FavoriteColors) AS ODBCColors
FROM Sample.Person

SELECT TOP 10 %EXTERNAL FavoriteColors AS ExtColors,
              %INTERNAL FavoriteColors AS IntColors,
              %ODBCOUT FavoriteColors AS ODBCColors
FROM Sample.Person