Retrieving the values for domains in SQL queries in the Oracle RDBMS – months, days, numbers and custom value sets
It happens quite frequently that in our database applications – in queries or in PL/SQL packages – we need to use the values from more or less standard domains or custom lists of values. For example the list of all months in the year – to show a breakdown list of some measure per month. Or the days of the week, to do something similar. Sometimes we just need a list of numbers in a certain range. And it is quite common to outer join such a collection of domain values with the set of measurements, using the partition outer join feature – to ensure that for each category of measurements, there is an aggregate for each of the values in the domain.
This article demonstrates several examples of standard domains, some SQL tricks for composing domains out of thin air and a number of features to spell out numbers and internationalize days and month names.
Occasionally, we might want to use a query that returns the alphabet – a single row for every letter in the (ASCII, Western European) alphabet. For example to count the number of employees listed under each letter.
This query really is a special case of the more general ‘all numbers in a certain range’ that we will see next. Note how the subquery (alphabet) is set up, apart from the main query in which the alphabet is used. You will see the WITH clause a lot in this article, to separate the initialization of the domain from the main query that uses the domain. It makes queries much better readable and much easier to maintain. Of course you knew that CHR(65) equals ‘A’.
with alphabet as ( select chr(64+rownum) letter from dual connect by rownum < 27 ) select letter from alphabet
Numbers in a range
A set of numeric values, typically integers, in a certain range – from a start value to an end value – is another common requirement. In fact, even the alphabet is based on the range 1..26 that is then turned to letters using the CHR function. A practical query for the common ‘numbers in range ‘ use case is constructed like this, using the WITH subquery factoring approach.
with range as ( select 5 lowend , 19 highend from dual ) , numbers as ( select lowend+ (level-1) num from range connect by level <= (highend - lowend + 1) ) select num from numbers
This query uses to_char function to extract the name of each month in the year from a series of 12 generated rows:
with months as ( select 0 + rownum month_num , to_char( to_date('01-'||rownum||'-2010', 'DD-MM-YYYY'), 'MONTH') month_name from dual connect by level < 13 ) select months.month_name from months
Days of the Week
Of course, days of the week is first of all a set of seven numerical values in the range 1..7. However, using to_char (and to_date) we can pry the labels of these seven days from the database as well. And, as we will see in the next section of this article, we can retrieve the names of the days in many different languages.
with days as ( select to_char( to_date(rownum||'-01-2010', 'DD-MM-YYYY'), 'DAY') day_name from dual connect by level < 8 ) select day_name from days
If we want to make use of custom domains, in place defined sets of allowable values, the best way of introducing them in queries is usually through a factored sub query – a sub query that is labeled by name, clearly recognizable and easily maintainable is the need arises. The next query demonstrates this approach with the domain of gender indications (Male, Female and Unknown):
create type string_table as table of varchar2(1000) with domain as ( select column_value value from table( string_table('MALE', 'FEMALE', 'UNKNOWN')) ) select value from domain
The essence here is the use of the string_table to provide a compact way to defining the domain values and the table operator to turn the collection of domain values into individual rows again.
Spell out numbers
The database can return numbers as numbers – 1, 5 and 3242 – but we can also create a query that spells out those numbers (in English only) – one, five and three thousand two hunderd forty two. The next query demonstrates this, using the to_char( to_date( num,’J’ ), ‘JSP’) combination.
with range as ( select 5 lowend , 19 highend from dual ) , numbers as ( select lowend+ (level-1) num from range connect by level <= (highend - lowend + 1) ) select to_char( to_date( num,'J' ), 'JSP') from numbers
Internationalize Month and Day names
Our Oracle RDBMS speaks its languages. Well, at least for a limited number of strings – more specifically: the names of week days and of the months – the database can provide translations into a substantial number of languages. The to_char() function takes a third, optional parameter nls_data_language that can be set to retrieve day and month names in the language of choice.
The next query demonstrates this.
with languages as ( select column_value language from table( string_table('dutch', 'english', 'german', 'italian', 'spanish')) ) , range as ( select level day_num from dual connect by level < 8 ) , days as ( select day_num , to_char( to_date(day_num||'-01-2010', 'DD-MM-YYYY'), 'DAY', 'nls_date_language='||language) day_name , language from range cross join languages ) select days.day_name , language from days
Download the source code for this article: http://technology.amis.nl/wp-content/uploads/images/domain_queries.txt
- Used port numbers by the Oracle OEM agent
- Quality Check for Domains in Oracle Designer – fighting problematic AVCON check constraints
- Not all has to be black and white in SQL Queries: returning match scores instead of only perfect fits
- Making up records in SQL Queries – Table Functions and 10g Model clause
- Oracle Developer days in the BeNeLux – support for Wilfred's action
- The AMIS Summary of Oracle OpenWorld 2013 is available for download – 60-page white paper
- On the integrity of data in Java applications – presentation from JFall 2013
- Enriching XMLType data using relational data – XQuery and fn:collection in action
- Java 8 – Collection enhancements leveraging Lambda Expressions – or: How Java emulates SQL
- Oracle Database SQL – Recursive Subquery to inspect events in football matches – find the MVP
- Oracle Database 12c: Find most valuable player using MATCH_RECOGNIZE in SQL
- Oracle Database 12c: Pattern Matching through MATCH_RECOGNIZE in SQL
- Oracle Database 12c: joining and outer joining with collections
- Oracle Database 12c: PL/SQL package UTL_CALL_STACK for programmatically inspecting the PL/SQL Call Stack
- Oracle Database 12c: In Line PL/SQL Functions in SQL queries