?
Solved

NULL Date Sorting: Oracle vs MSSQL

Posted on 2005-05-04
13
Medium Priority
?
2,048 Views
Last Modified: 2008-01-09
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
Comment
Question by:Dain_Anderson
  • 3
  • 3
  • 2
  • +5
13 Comments
 
LVL 12

Expert Comment

by:geotiger
ID: 13927832

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
 
LVL 9

Author Comment

by:Dain_Anderson
ID: 13927994
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
 
LVL 7

Expert Comment

by:Nievergelt
ID: 13928081
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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
LVL 7

Accepted Solution

by:
Nievergelt earned 1200 total points
ID: 13928091
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
 
LVL 11

Expert Comment

by:pennnn
ID: 13928116
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
 
LVL 48

Assisted Solution

by:schwertner
schwertner earned 800 total points
ID: 13928139
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
 
LVL 6

Expert Comment

by:jwittenm
ID: 13928764
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
 
LVL 6

Expert Comment

by:jwittenm
ID: 13929073
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
 
LVL 9

Author Comment

by:Dain_Anderson
ID: 13929097
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
 
LVL 25

Expert Comment

by:jrb1
ID: 13929098
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
 
LVL 7

Expert Comment

by:Nievergelt
ID: 13929126
Hi Dain,

Sorry, I have no MySql experience at all.
0
 

Expert Comment

by:luegner
ID: 14126556
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
 
LVL 9

Author Comment

by:Dain_Anderson
ID: 14130563
Good to know luegner -- much appreciated.

-Dain
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

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…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
Suggested Courses

839 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