Re: Order By Clause-MS to Oracle Conversion

From: Alan Pettigrew <alan.pettigrew_at_fox-europe.com>
Date: Thu, 24 Aug 2000 09:19:43 +0100
Message-ID: <39a4dab9_2@nnrp1.news.uk.psi.net>

The default action in SQL Server is wrong if you need portability. You need to

SET ANSI_NULLS
which will sort NULL after other values, as Oracle does. Beware, because it changes other things as well. The result of ANY test against NULL will become NULL, i.e. not true, not false (as Oracle does) Old SQL server action where x=NULL

    IF x = NULL then
      ... This is executed
    ELSE
      ...
    END IF
    IF x <> NULL then
      ...
    ELSE
      ... This is executed
    END IF
New (and Oracle) action

    IF x = NULL then
      ...
    ELSE
      ... This is executed
    END IF
    IF x <> NULL then
      ...
    ELSE
      ... This is executed
    END IF If you need to simulate the SQL Server actions in Oracle, then you have problems.

Good luck,

Alan

"Rognvald Bjarne" <wear_u_out_at_nospam.hotmail.com> wrote in message news:POVo5.206$uzc7.2752892_at_news.randori.com...
> Decode?
>
> <afedirko_at_my-deja.com> wrote in message
news:8o165a$5n4$1_at_nnrp1.deja.com...
> > When converting a database from MS SQL Server 7 to Oracle 8x, I
> > discovered that the Order By Clause behaves differently between the two
> > databases. When I do an Order By in SQL Server, a null will appear
> > before a field with a character. When in Oracle, it is the opposite.
> >
> > It is very important to get the same resultset from both databases, as
> > the Application can point to either one. Besides placing a space
> > character instead of a null, do I have any other option for
> > accomplishing this sort in Oracle?
> >
> >
> > Sent via Deja.com http://www.deja.com/
> > Before you buy.
>
>
Received on Thu Aug 24 2000 - 03:19:43 CDT