Solved

SQL Query Sorting Days.

Posted on 2004-11-01
1,190 Views
Last Modified: 2011-10-03
I have a column titled 'DAYS' and the fields are MON to SUN, these are of character format and I want to order by these. How can I get the SQL query to order by the days and leave it either as MON or Monday as opposed to formatting it to a date.
0
Question by:leigh_richardson
    14 Comments
     
    LVL 11

    Accepted Solution

    by:
    you could use a DECODE() to convert from MON, TUE, etc to a number, and use that for ordering:

    -- Null at the end in case there are other values in table...
    select days from testtable ORDER BY DECODE( days, 'MON', 1, 'TUE', 2, 'WED', 3, 'THUR', 4, 'FRI', 5, 'SAT', 6, 'SUN', 7, null );

    or you could look into using TO_DATE() to convert the day to a date somehow.... (formatter 'day' will provide the day for a given date, so you might be able to use that in reverse, to get a numeric value for a given day....)

    select to_char( sysdate, 'day' ) from dual;
    0
     
    LVL 9

    Expert Comment

    by:pratikroy
    Well, you could try this :

    select DAYS
    from yourtable
    ORDER by to_char(to_date(DAYS,'DY'),'D');

    Note that to_char(to_date(DAYS,'DY'),'D') will return you 1 where DAYS has a value MON, 2 for TUE ... and so on ...

    Hope this helps.

    0
     
    LVL 9

    Expert Comment

    by:pratikroy
    Looks like you might get a trouble with my query. I will have to find out the solution for that, but this is the problem that you may encounter :

    ERROR at line 1:
    ORA-01835: day of week conflicts with Julian date

    So, you may stick to cjjclifford's solution!
    0
     
    LVL 11

    Expert Comment

    by:cjjclifford
    I started to play around with the same type of stuff, and saw the "Julian" error too... I'd like to see if it can be done without the verbose DECODE() I suggested...
    0
     
    LVL 13

    Expert Comment

    by:riazpk
    I agree with  cjjclifford. Decode/case is the only solution.

      1  select A
      2   from mytest1
      3  order by case when a='MON' then 1
      4  when a='TUE' then 2
      5  when a='WED' then 3
      6  when a='THU' then 4
      7  when a='FRI' then 5
      8  when a='SAT' then 6
      9  when a='SUN' then 7
     10* end
    SQL> /

    A
    ===
    MON
    TUE
    WED
    THU
    FRI
    SAT
    SUN
    0
     
    LVL 11

    Expert Comment

    by:cjjclifford
    you might want to add an "else NULL" if you are using the CASE approach, to guard against unexpected data in the table, similar to the end NULL in my DECODE().

    Actually, you should probably set a check constraint on the table to ensure the day text is only one of ( 'MON', 'TUE', etc ), guarantee the values at insert time!
    0
     
    LVL 9

    Expert Comment

    by:pratikroy
    Alternatively you could create a small function and use it in your query.

      1  create or replace function DOW(istr varchar2) return number
      2  is
      3  begin
      4  return case when istr = 'MON' THEN 1
      5              when istr = 'TUE' THEN 2
      6              when istr = 'WED' THEN 3
      7              when istr = 'THU' THEN 4
      8              when istr = 'FRI' THEN 5
      9              when istr = 'SAT' THEN 6
     10              when istr = 'SUN' THEN 7
     11              else null end;
     12* end;
    SQL> /

    Function created.

    SQL> select * from tab_chr1;

    FLD1
    ------------------------
    MON
    TUE
    WED
    FRI
    SUN
    SAT
    THU
    TUE

    8 rows selected.

    SQL> select * from tab_chr1 order by DOW(fld1);

    FLD1
    ------------------------
    MON
    TUE
    TUE
    WED
    THU
    FRI
    SAT
    SUN

    8 rows selected.

    SQL>
    0
     

    Author Comment

    by:leigh_richardson
    Thank you both for your comments and they work when I am using SQL Plus, however, when I use the same SQL command on Oracle Report builder for some reason it does not seem to work. Any suggestions for this?

    Thanks
    0
     
    LVL 11

    Expert Comment

    by:cjjclifford
    There should be no reason for a difference - perhaps it is a problem with the quoting of the 'MON', etc, strings? (I haven't user Report Builder, so I'm guessing...)
    0
     
    LVL 22

    Expert Comment

    by:Henka
    What does it mean " ... it does not seem to work" ? Are there errors there ?
    0
     

    Author Comment

    by:leigh_richardson
    I mean that there are no errors on the Report Builder it just does not sort it to Mon, Tues etc, the output remains as Fri, Tue, Thu etc. I think it may be something to do with the fact that I am using the Days as Group in the Report builder.
    0
     
    LVL 11

    Expert Comment

    by:cjjclifford
    Grouping and ordering are seperate.

    Are you sure your data in the DAYS column is clean (check with "SELECT DISTINCT days FROM tablename")

    Do you have a case-sensitivity issue? use UPPER( days ) in the DECODE() : DECODE( UPPER( days ), 'MON', 1, 'TUE', 2, etc ...

    If you are having problems with using the decode/case in report builder, create a simple view of your table to add the day index:

    CREATE OR REPLACE VIEW table_with_dayid AS
    SELECT *, DECODE( UPPER( days ), 'MON', 1, 'TUE', 2, 'WED', 3, 'THUR', 4, 'FRI', 5, 'SAT', 6, 'SUN', 7, NULL ) day_id
    FROM tablename;

    This view should have all the same columns, and one additional one "DAY_ID".
    Now use this view in your report, rather than the original table, and just ORDER BY day_id.
    0
     
    LVL 22

    Expert Comment

    by:Henka
    I think that you are right - see this topic in the on-line help "Query modifications".
    0
     

    Author Comment

    by:leigh_richardson
    FYI,

    I have now resolved this problem by using the faithful DECODE function. I added a new field with DECODE( days, 'MON', 1, 'TUE', 2, 'WED', 3, 'THU', 4, 'FRI', 5, 'SAT', 6, 'SUN', 7, null ) as "NumDay", in addition to the field days. I ran the report on ORacle Report Builder using NUMDAY as the group level and then hid the Num Day field. Thank you all for your help you got me on the right track
    0

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Anonabox PRO Tor & VPN Router

    PRO is the most advanced way to fortify your privacy and online anonymity by layering the Tor network with VPN services. Use both together or separately, and without needing to download software onto your devices.

    Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many‚Ķ
    Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
    This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
    This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

    884 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now