Re: SELECT statement issue

When "porting" from SQL Server to Oracle (or many other combinations, for
that matter) you need to recognise that you are changing much more
than "just" the database.  Frequently large parts of the application may
need to be replaced -- or even redesigned.

This particular construct might be perfectly natural with SQLserver, but it
definitely is not with Oracle -- as you have already noticed, it is not
valid
Oracle SQL.  The following construct, while looking rather "unusual" MIGHT
do what you want:

SELECT ...
  WHERE ...
    AND values = 'xyz'
UNION ALL
SELECT ...
  WHERE ...
     AND values <> 'xyz';

With this, like almost everything else you will face while "porting" from
one database to another, you may want to think twice about what you are
really trying to achieve an how you should actually best go about doing
it...


On 9/14/06, Harvinder Singh <Harvinder.Singh@xxxxxxxxxxxxx> wrote:

Hi,



We are porting the application from SQL Server to Oracle, we have lot of
queries in the xml file and the application read the queries from the file.

In some scenarios we have following construct in sql server where we are
checking the value and depending upon it running one of the 2 sql's:

If (values = 'xyz')

Begin

Select….

End

Else

begin

Select…..

End



Oracle somehow don't like this construct and giving error : ORA-00900:
invalid SQL statement



What is the way to port this to oracle?



Thanks

--Harvinder






-- Cheers, -- Mark Brinsmead Staff DBA, The Pythian Group http://www.pythian.com/blogs

Other related posts: