Skip to main content

%EXTERNAL (SQL)

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

Synopsis

%EXTERNAL(expression)

%EXTERNAL expression

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 an InterSystems 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.

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. Cannot be a stream field.

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:

SELECT TOP 5 DOB,%EXTERNAL(DOB) AS ExtDOB
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

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

FeedbackOpens in a new tab