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.

The Alphabet

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

Months

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

Custom Domains

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

Resources

Download the source code for this article: https://technology.amis.nl/wp-content/uploads/images/domain_queries.txt