• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2066
  • Last Modified:

NULL Date Sorting: Oracle vs MSSQL

Hello,

I'm sure this is a simple question, but I'm a bit stumped. We have an application that runs on both Oracle 9i, and MSSQL Server 2000 (it's a distributed application). We're running into an interesting issue where we are sorting by a date column that may or may not contain NULL values. The column is EVENT_DATE, which represents an event date for a simple announcements application, which is entirely optional.

When we use the following query:

SELECT * FROM OUR_SCHEMA.TBL_ANNOUNCEMENTS ORDER BY EVENT_DATE ASC

We get an ordering on Oracle where dates come before NULLs, and where NULLs come before dates in MSSQL Server, such as:

Oracle:

Event:     Date:
Annc 1    10/10/2005
Annc 2    10/10/2006
Annc 3    10/10/2007
Annc 4    NULL
Annc 5    NULL

SQL Server:

Event:     Date:
Annc 1    NULL
Annc 2    NULL
Annc 3    10/10/2005
Annc 4    10/10/2006
Annc 5    10/10/2007

Any ideas on why this is happening and perhaps how to work around it? We would ideally like to have dates come before NULLs when using ASC .

Much appreciated,

-Dain
0
Dain_Anderson
Asked:
Dain_Anderson
  • 3
  • 3
  • 2
  • +5
2 Solutions
 
geotigerCommented:

You could avoid it by adding a wher clause:

SELECT * FROM OUR_SCHEMA.TBL_ANNOUNCEMENTS
where EVENT_DATE is not null
ORDER BY EVENT_DATE ASC
0
 
Dain_AndersonAuthor Commented:
Thanks, but I do not want to filter out NULL events -- I just want them to come last.

For example, we have a Flash-based announcements scroller where we have to have all events with dates to appear first, in ascending order, then show all events with NULL dates last (the order for NULLs isn't important). On Oracle, all is well; on MSSQL, the NULLs are appearing first.

-Dain
0
 
NievergeltSenior SW DevCommented:
You could work around this by using

SELECT * FROM OUR_SCHEMA.TBL_ANNOUNCEMENTS
ORDER BY COALESCE (EVENT_DATE, (SELECT MIN(EVENT_DATE) FROM OUR_SCHEMA.TBL_ANNOUNCEMENTS ) - 1) ASC

or, alternatively

SELECT * FROM OUR_SCHEMA.TBL_ANNOUNCEMENTS
ORDER BY COALESCE (EVENT_DATE, (SELECT MAX(EVENT_DATE) FROM OUR_SCHEMA.TBL_ANNOUNCEMENTS ) + 1) ASC
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
NievergeltSenior SW DevCommented:
Sorry, just saw your remark: So the second query should do:

SELECT * FROM OUR_SCHEMA.TBL_ANNOUNCEMENTS
ORDER BY COALESCE (EVENT_DATE, (SELECT MAX(EVENT_DATE) FROM OUR_SCHEMA.TBL_ANNOUNCEMENTS ) + 1) ASC
0
 
pennnnCommented:
In Oracle you would do it by using the "NULLS LAST" clause:
... ORDER BY event_date ASC NULLS LAST;

However, I have no idea if this is ANSI SQL and if it would work on SQL Server. You can try it, though. :)
Hope that helps!
0
 
schwertnerCommented:
SELECT * FROM OUR_SCHEMA.TBL_ANNOUNCEMENTS
WHERE date IS NOT NULL
ORDER BY EVENT_DATE ASC
UNION
SELECT * FROM OUR_SCHEMA.TBL_ANNOUNCEMENTS
WHERE date IS NULL
ORDER BY EVENT_DATE ASC;

0
 
jwittenmCommented:
Nulls sort high in Oracle.  Looks like the sort low in MSSQL.  If the query is being issued in SQLPLUS, try this at the SQL prompt:
>col new_null new_value mynull
>select chr(9) new_null from dual;
>set null '&mynull'

then issue the query.  chr(9) is a tab, which will be non-visible, but should sort lower than alpha-numerics.  if this is acceptable, put it in the login.sql or glogin.sql script.
0
 
jwittenmCommented:
One more thing,  if you choose to include the statements provided in your login.sql, set termout off before you execute them, and set termout on afterward.
0
 
Dain_AndersonAuthor Commented:
Thanks to everyone. I gave points to Nievergelt and schwertner; while schwertner's solution worked, I found that for my specific situation, Nievergelt's answer fit perfectly. Thanks to you both!

BTW, Nievergelt, does that work in MySQL? We don't need it now, but may in the future.

Thanks,

-Dain
0
 
jrb1Commented:
SELECT * FROM OUR_SCHEMA.TBL_ANNOUNCEMENTS
ORDER BY decode(EVENT_DATE,null,'31-dec-9999',event_date);

test case:

drop table tab1;
create table tab1 (event varchar2(10), event_date date);
insert into tab1 values ('Annc 1','10-oct-2005');
insert into tab1 values ('Annc 2','10-oct-2006');
insert into tab1 values ('Annc 3','10-oct-2007');
insert into tab1 values ('Annc 4',NULL);
insert into tab1 values ('Annc 5',NULL);

select * from tab1
ORDER BY decode(EVENT_DATE,null,'31-dec-9999',event_date);

Results:

EVENT      EVENT_DAT
---------- ---------
Annc 1     10-OCT-05
Annc 2     10-OCT-06
Annc 3     10-OCT-07
Annc 4
Annc 5
0
 
NievergeltSenior SW DevCommented:
Hi Dain,

Sorry, I have no MySql experience at all.
0
 
luegnerCommented:
COALESCE function has existed since MySQL 3.23.3.  Subqueries came into being starting with MySQL 4.1.0.   So if you're using a MySQL version >= 4.1.0, then you should be fine.  
0
 
Dain_AndersonAuthor Commented:
Good to know luegner -- much appreciated.

-Dain
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 3
  • 3
  • 2
  • +5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now