InterSystems SQL Reference
%INTERNAL
|
|
A format-transformation function that returns an expression in LOGICAL format.
Synopsis
%INTERNAL(expression)
%INTERNAL expression
%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.
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
Content Date/Time: 2019-02-20 23:00:21