Re: Very perplexing select Problem

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