1 Reply Latest reply on Nov 18, 2008 12:25 PM by Newsgroup_User

    Oracle WITH clause


      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 (
      FROM TABLE_1
      , Y AS (
      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 ?

        • 1. Re: Oracle WITH clause
          Level 7
          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

          <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 :-)