DOMAIN_ORDER
Purpose
DOMAIN_ORDER returns expr formatted according to the domain's order expression. This returns NULL if the arguments are not associated with a domain or the domain has no order expression.
When calling DOMAIN_ORDER for multicolumn domains, all values of expr should be from the same domain. It returns NULL if the number 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.
See Also:
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_ORDER returns the result of the ORDER expression (MON = 0, TUE = 1, etc.). Using this in the ORDER BY returns the rows sorted by their position in the week instead of alphabetically.
The query casts the string "MON" to DAY_OF_WEEK to get its sort value.
All other calls to DOMAIN_ORDER pass non-domain values, so return NULL.
CREATE DOMAIN day_of_week AS CHAR(3 CHAR)
ORDER CASE UPPER(day_of_week)
WHEN 'MON' THEN 0
WHEN 'TUE' THEN 1
WHEN 'WED' THEN 2
WHEN 'THU' THEN 3
WHEN 'FRI' THEN 4
WHEN 'SAT' THEN 5
WHEN 'SUN' THEN 6
ELSE 7
END;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'),
(DATE'2023-05-08', 'mon');
SELECT day_of_week_abbr,
DOMAIN_ORDER(day_of_week_abbr) domain_column,
DOMAIN_ORDER(calendar_date) nondomain_column,
DOMAIN_ORDER(CAST('MON' AS day_of_week)) domain_value,
DOMAIN_ORDER('MON') nondomain_value
FROM calendar_dates
ORDER BY DOMAIN_ORDER(day_of_week_abbr);
DAY_OF_WEEK_ABBR DOMAIN_COLUMN NONDOMAIN_COLUMN DOMAIN_VALUE NONDOMAIN_VALUE
---------------- ------------- ---------------- ------------ ---------------
MON 0 <null> 0 <null>
mon 0 <null> 0 <null>
TUE 1 <null> 0 <null>
FRI 4 <null> 0 <null>
The following example creates the multicolumn domain CURRENCY with an order expression. This sorts the values by currency then value. 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 order expression. All other calls to DOMAIN_ORDER 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) ) ORDER currency_code || TO_CHAR(amount, '999999999.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, 'USD'),
(3, 3, 4321, 'EUR'),
(4, 4, 3141592, 'JPY'),
(5, 5, 2718281, 'JPY');
SELECT order_id,
product_id,
DOMAIN_ORDER(amount, currency_code) domain_cols,
DOMAIN_ORDER(currency_code, amount) domain_cols_wrong_order,
DOMAIN_ORDER(order_id, product_id) nondomain_cols,
DOMAIN_ORDER(amount) domain_cols_subset
FROM order_items
ORDER BY domain_cols;
ORDER_ID PRODUCT_ID DOMAIN_COLS DOMAIN_COLS_WRONG_ORDER NONDOMAIN_COLS DOMAIN_COLS_SUBSET
---------- ---------- ---------------- ----------------------- -------------- ------------------
3 3 EUR 4321.00 <null> <null> <null>
5 5 JPY 2718281.00 <null> <null> <null>
4 4 JPY 3141592.00 <null> <null> <null>
1 1 USD 9.99 <null> <null> <null>
2 2 USD 1234.56 <null> <null> <null>