Oracle WITH clause

[TinkietLalouette]

Hello,

Last week, our team have update the Coldfusion jdbc drivers from 3.3 to 3.5.
Since this update, I can't use Oracle WITH clause with many subqueries.

This example below run with Jdbc drivers 3.3 but not with 3.5
WITH X AS (
SELECT ID
FROM TABLE_1
)
, Y AS (
SELECT ID
FROM TABLE_2
)
SELECT *
FROM X, Y
WHERE X.ID = Y.ID

I try to use Query of queries, but the inner join of huge result sets is very slow.

Anyone have an idea to run a WITH clause without revert to jdbc drivers 3.3 ?

Regards.

[Newsgroup_User]

I don't have an instance running the 3.5 JDBC drivers to hand, but I tried
the following query on both 3.3 and 3.60. It worked as expected on both.

<cfquery name="q" datasource="intranet">
with u as (
select *
from user_tables
), a as (
select *
from all_tables
)
select a.owner, a.table_name, a.tablespace_name
from a inner join u
on a.table_name = u.table_name
</cfquery>

<cfdump var="#q#">

Obviously your issue is with 3.5, but I think it'd be unlikely that it
would work on 3.3 and 3.60 but not 3.5. I'll see if I can find a 3.5
instance to run this code on. It might be an idea for you to test my code
too (it's a pretty generic query).

BTW, thanks for asking this question. I have to confess I didn't know
about the WITH construct before, and it's turned out to solve an issue we
had with some of our code :-)

--
Adam