%EXTERNAL
Synopsis
%EXTERNAL(expression) %EXTERNAL expression
Arguments
Argument | Description |
---|---|
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. Cannot be a stream field. |
Description
%EXTERNAL converts expression to DISPLAY format, regardless of the current select mode (display mode). The DISPLAY format represents data in the VARCHAR data type with whatever data conversion the field or data type LogicalToDisplay method performs.
%EXTERNAL is commonly used on a SELECT list select-item. It can be used in a WHERE clause, but this use is discouraged because using %EXTERNAL prevents the use of indexes on the specified field.
Applying %EXTERNAL 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.
Whether %EXTERNAL converts a date depends on the data type returned by the date field or function. %EXTERNAL converts CURDATE, CURRENT_DATE, CURTIME, and CURRENT_TIME values. It does not convert CURRENT_TIMESTAMP, GETDATE, GETUTCDATE, NOW, and $HOROLOG values.
When %EXTERNAL converts a %List structure to DISPLAY format, the displayed list elements appear to be separated by a blank space. This “space” is actually the two non-display characters CHAR(13) and CHAR(10).
%EXTERNAL is a Caché SQL extension.
To convert an expression to LOGICAL format, regardless of the current select mode, use the %INTERNAL 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 “Caché SQL Basics” chapter of Using Caché SQL.
Examples
The following Dynamic SQL example returns Date of Birth (DOB) data values in the current select mode format, and the same data using the %EXTERNAL function. For the purpose of demonstration, in this program the %SelectMode value is determined randomly for each invocation:
ZNSPACE "SAMPLES"
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,%EXTERNAL(DOB) AS ExtDOB "
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
The following example converts date of birth (DOB) and rounded date of birth (DOB) values to %EXTERNAL (DISPLAY format):
SELECT %EXTERNAL(DOB) AS DOB,
%INTERNAL(ROUND(DOB,-3)) AS DOBGroup,
%EXTERNAL(ROUND(DOB,-3)) AS RoundedDOB
FROM Sample.Person
GROUP BY (ROUND(DOB,-3))
ORDER BY DOBGroup
See Also
-
SQL concepts: Data Types, Date and Time Constructs