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
