Oracle 10g-Express Query

Question

I want to perform a special query on a table in my oracle database.

I want the result to be sorted according to an enum that I have.

the enum goes as follows:

private enum days
{
    Saturday = 1,
    Sunday,
    Monday,
    Tuesday,
    Wednesday,
    Thursday,
}

I want the result to be sorted according to this enum.

Answer 1

order by
  case to_char(datecol, 'D')
    when 1 then 2
    when 2 then 3
    ...
    when 7 then 1
  end

Answer 2

So, your ENUM is a front-end thing which the database cannot see. All you can do is duplicate its logic in the query. In Oracle we can use functions like CASE() to translate a column value into a different value for sorting.

SQL> select d2
  2         , to_char(d2, 'DY')
  3  from t34
  4  ORDER BY CASE to_char(d2, 'DY')
  5               WHEN 'SAT' THEN 1
  6               WHEN 'SUN' THEN 2
  7               WHEN 'MON' THEN 3
  8               WHEN 'TUE' THEN 4
  9               WHEN 'WED' THEN 5
 10               WHEN 'THU' THEN 6
 11               WHEN 'FRI' THEN 7
 12      ELSE 100 END
 13  /

D2        TO_
--------- ---
25-JUL-10 SUN
24-AUG-10 TUE
13-JUL-10 TUE
26-MAY-10 WED
15-APR-10 THU
25-JUN-10 FRI

6 rows selected.

SQL>

The exact values of the date abbreviations will depend on the setting of NLS_DATE_LANGUAGE parameter. Find out more.

Oracle date formatting also allows us to convert a DATE into a number for day of the week. This is a cultural thing: some societies have Monday as the first day of the week, others Sunday or Saturday. So TO_CHAR(some_date, 'D') will return 1 for a date which is a Monday in Europe but 2 in USA. This is controlled by the NLS_TERRITORY settings. Find out more.

If your Territory is set so that TO_CHAR(date_col, 'D') returns 1 for a date which is a Saturday the ORDER BY clause is much simpler:

SQL> select * from nls_session_parameters
  2  where parameter = 'NLS_TERRITORY'
  3  /

PARAMETER                      VALUE
------------------------------ -----------------
NLS_TERRITORY                  UNITED KINGDOM

SQL> select d2
  2         , to_char(d2, 'DY')
  3         , to_char(d2, 'D')
  4  from t34
  5  ORDER BY to_char(d2, 'D')
  6  /

D2        TO_ T
--------- --- -
13-JUL-10 TUE 2
24-AUG-10 TUE 2
26-MAY-10 WED 3
15-APR-10 THU 4
25-JUN-10 FRI 5
25-JUL-10 SUN 7

6 rows selected.
SQL>

If I change the NLS_TERRITORY the result set order changes accordingly:

SQL> alter session set nls_territory='MOROCCO'
  2  /

Session altered.

SQL> select d2
  2         , to_char(d2, 'DY')
  3         , to_char(d2, 'D')
  4  from t34
  5  ORDER BY to_char(d2, 'D')
  6  /

D2       TO_ T
-------- --- -
25-07-10 SUN 2
24-08-10 TUE 4
13-07-10 TUE 4
26-05-10 WED 5
15-04-10 THU 6
25-06-10 FRI 7

6 rows selected.

SQL> 

Answer 3

I've upvoted APCs solution, this is the best / right way to do it.

However, I wanted to add an answer that applies to the general case of ordering by an arbitrary set of data.

(In this specific case, the ordering is not arbitrary - it is something we can write as a simple SQL function - so should).

The key thing here is that an enum is a SET. A Table is also a set, and so is a View - so simply convert your enum into a table or view, then in your SQL queries you can join to / order by this table.

This is especially useful if you need to re-use it in many queries.

For a small fixed set like this I would simply hard-code it into a view - for larger sets an indexed table would be better.

Being honest - if it was real one-off - if I knew that the ordering would only ever be used in a single query - I would use the CASE approach in the main answer.