Syntax

Purpose

DOMAIN_DISPLAY returns expr formatted according to the ___domain's display expression. This returns NULL if the arguments are not associated with a ___domain or the ___domain has no display expression.

When calling DOMAIN_DISPLAY for multicolumn domains, all values of expr should be from the same ___domain. It returns NULL if the number of expr arguments are different from the number of ___domain columns or they are in a different order in the ___domain.

To get the display expression for a non-___domain value, cast expr to the ___domain type. This is only possible for single column domains.

Examples

The following example creates the ___domain DAY_OF_WEEK and associates it with the column CALENDAR_DATES.DAY_OF_WEEK_ABBR. Passing this column to DOMAIN_DISPLAY returns it with the first letter of each word capitalized and all other letters in lowercase. DOMAIN_DISPLAY also returns this format when casting a string to the ___domain.

All other calls to DOMAIN_DISPLAY pass non-___domain values, so return NULL.

CREATE DOMAIN day_of_week AS CHAR(3 CHAR)
  DISPLAY INITCAP(day_of_week);

CREATE TABLE calendar_dates (
  calendar_date DATE,
  day_of_week_abbr day_of_week
);

INSERT INTO calendar_dates 
VALUES(DATE'2023-05-01', 'MON'), 
      (DATE'2023-05-02', 'tue'), 
      (DATE'2023-05-05', 'fRI');

SELECT day_of_week_abbr, 
       DOMAIN_DISPLAY(day_of_week_abbr) domain_column, 
       DOMAIN_DISPLAY(calendar_date) nondomain_column,
       DOMAIN_DISPLAY(CAST('MON' AS day_of_week)) domain_value, 
       DOMAIN_DISPLAY('MON') nondomain_value
  FROM calendar_dates;

DAY_OF_WEEK_ABBR DOMAIN_COLUMN NONDOMAIN_COLUMN DOMAIN_VALUE NONDOMAIN_VALUE
---------------- ------------- ---------------- ------------ ---------------
MON              Mon           <null>           Mon          <null>         
tue              Tue           <null>           Mon          <null>             
fRI              Fri           <null>           Mon          <null> 

The following example creates the multicolumn ___domain CURRENCY with a display expression. The columns AMOUNT and CURRENCY_CODE in ORDER_ITEMS are associated with this ___domain.

In the query, only the domain_cols expression formats the columns according to the ___domain expression. All other calls to DOMAIN_DISPLAY have a mismatch between its arguments and the ___domain columns so return NULL:

CREATE DOMAIN currency AS (
  amount        AS NUMBER(10, 2)
  currency_code AS CHAR(3 CHAR)
)
DISPLAY CASE currency_code
  WHEN 'USD' THEN '$'
  WHEN 'GBP' THEN '£'
  WHEN 'EUR' THEN '€'
  WHEN 'JPY' THEN '¥'
END || TO_CHAR(amount, '999,999,999.00');

CREATE TABLE order_items (
  order_id      INTEGER,
  product_id    INTEGER,
  amount        NUMBER(10, 2),
  currency_code CHAR(3 CHAR),
  DOMAIN currency(amount, currency_code)
);

INSERT INTO order_items
VALUES (1, 1,    9.99, 'USD'),
       (2, 2, 1234.56, 'GBP'),
       (3, 3, 4321,    'EUR'),
       (4, 4, 3141592, 'JPY');

SELECT order_id,
       product_id,
       DOMAIN_DISPLAY(amount, currency_code) domain_cols,
       DOMAIN_DISPLAY(currency_code, amount) domain_cols_wrong_order,
       DOMAIN_DISPLAY(order_id, product_id) nondomain_cols,
       DOMAIN_DISPLAY(amount) domain_cols_subset
  FROM order_items;
  
  ORDER_ID PRODUCT_ID DOMAIN_COLS      DOMAIN_COLS_WRONG_ORDER NONDOMAIN_COLS DOMAIN_COLS_SUBSET
---------- ---------- ---------------- ----------------------- -------------- ------------------
         1          1 $           9.99 <null>                  <null>         <null>            
         2          2 £       1,234.56 <null>                  <null>         <null>            
         3          3 €       4,321.00 <null>                  <null>         <null>            
         4          4 ¥   3,141,592.00 <null>                  <null>         <null>