Oracle WITH clause

Question

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 · Answer

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

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded