%INTERNAL - InterSystems SQL Reference - InterSystems IRIS Data Platform 2018.1

%INTERNAL(expression)

%INTERNAL expression

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.

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

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.

The following Dynamic SQL example returns Date of Birth (DOB) data values in the current select mode format, and the same data using the %INTERNAL function. For the purpose of demonstration, in this program the %SelectMode value is determined randomly for each invocation:

  SET tStatement = ##class(%SQL.Statement).%New()
  SET tStatement.%SelectMode=$RANDOM(3)
    IF tStatement.%SelectMode=0 {WRITE "Select mode LOGICAL",! }
    ELSEIF tStatement.%SelectMode=1 {WRITE "Select mode ODBC",! }
    ELSEIF tStatement.%SelectMode=2 {WRITE "Select mode DISPLAY",! }
  SET myquery = 2
  SET myquery(1) = "SELECT TOP 5 DOB,%INTERNAL(DOB) AS IntDOB "
  SET myquery(2) = "FROM Sample.Person"
  SET qStatus = tStatement.%Prepare(.myquery)
    IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
  SET rset = tStatement.%Execute()
  DO rset.%Display()
  WRITE !,"End of data"

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