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_at_metratech.com> 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

--
http://www.freelists.org/webpage/oracle-l