Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Very perplexing select Problem

Re: Very perplexing select Problem

From: Serge Rielau <srielau_at_ca.ibm.com>
Date: Sat, 17 Sep 2005 08:54:26 -0400
Message-ID: <3p2i05F872vqU1@individual.net>


BN wrote:
> Is this SQL statement portable? I am sorry but I should have mentioned
> that. Powers to be require that the SQL is portable to other RDBMS.
>
> After the fiasco here 5 years ago with developers using SQL Server
> specific features no one wants to repeat this. That app become
> impossible to port to other backend databases if customers want your app
> against another database.
>
> Oracle is great and is our primary choice. But if the customer wants to
> use your app against their own db that they pay for like DB2 or a free
> one then you have to allow for that. I think even SAP allows you the
> flexibility to use many databases.
>
> Can this be done anyother way?

To do it another way you would need to code it "on-foot" which would indeed be very slow.
Both DB2 for LUW and SQL Server 2005 support SQL Standard recursion. There is however a mapping that allows to get from CONNECT BY to the SQL Standard. So if you isolate this query in a function or procedure you can limit the impact when porting.
I actually plan to describe the mapping soon at IBM developerworks DB2, so when the time comes to port do DB2 you'll have instructions there.

Cheers
Serge

PS: Contrary to others I believe that a lot can be achieved sticking with standard SQL. It's the more exotic SQL features where divergence happens. Then the trick is encapsulation.

-- 
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Received on Sat Sep 17 2005 - 07:54:26 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US