Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL Query Sorting Days.

Posted on 2004-11-01
14
Medium Priority
?
1,196 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
Comment
Question by:leigh_richardson
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 3
  • +2
14 Comments
 
LVL 11

Accepted Solution

by:
cjjclifford earned 500 total points
ID: 12461569
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
ID: 12462088
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
ID: 12462146
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
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
LVL 11

Expert Comment

by:cjjclifford
ID: 12462345
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
ID: 12462547
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
ID: 12462572
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
ID: 12463356
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
ID: 12464081
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
ID: 12464251
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:Helena Marková
ID: 12470423
What does it mean " ... it does not seem to work" ? Are there errors there ?
0
 

Author Comment

by:leigh_richardson
ID: 12470829
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
ID: 12470864
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:Helena Marková
ID: 12470898
I think that you are right - see this topic in the on-line help "Query modifications".
0
 

Author Comment

by:leigh_richardson
ID: 12470963
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

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

636 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