%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.

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