MacLochlainns Weblog

Michael McLaughlin’s Technical Blog

Site Admin

Unsupported use of WITH clause

with one comment

While helping out in the conversion of some MySQL SQL, I tried the WITH clause inside a subquery for a multiple row INSERT statement. I got a nasty surprise, it’s not supported. I got the following error:

  FROM     dual )
                *
ERROR at line 16:
ORA-32034: unsupported USE of WITH clause

Consistent with how I’m updating old blog pages and posts, you can find the full explanation in the updated blog post on the WITH clause. As Dominic commented, I got the syntax wrong and he’s got it for a single row subquery in the comment too. The blog page is updated with both a single and multiple row subquery.

Written by maclochlainn

November 15th, 2008 at 9:20 pm

Posted in Oracle,sql

Tagged with , ,

«
»

One Response to 'Unsupported use of WITH clause'

Subscribe to comments with RSS or TrackBack to 'Unsupported use of WITH clause'. 

  1. dominic@CSD3> CREATE TABLE contact_copy
  2  (id1       number
  3  ,id2       number
  4  ,contact   number
  5  ,surname   varchar2(24)
  6  ,firstname varchar2(24)
  7  ,col1      varchar2(24)
  8  ,col2      number
  9  ,col3      date
 10  ,col4      number
 11  ,col5      date);
 
TABLE created.
 
dominic@CSD3> CREATE TABLE common_lookup
  2  (common_lookup_id    number
  3  ,common_lookup_type  varchar2(24));
 
TABLE created.
 
dominic@CSD3> INSERT INTO common_lookup
  2  VALUES(1,'A_DAY_RENTAL');
 
1 row created.
 
dominic@CSD3> CREATE sequence contact_s1;
 
Sequence created.
 
dominic@CSD3> INSERT INTO contact_copy
  2  WITH  cl AS
  3   (SELECT   common_lookup_id AS contact_type
  4    FROM     common_lookup
  5    WHERE    common_lookup_type LIKE '%DAY RENTAL')
  6    SELECT   contact_s1.NEXTVAL
  7    ,        1001
  8    ,        (SELECT cl.contact_type FROM cl)
  9    ,        'Doe'
 10    ,        'John'
 11    ,        NULL
 12    ,        3
 13    ,        SYSDATE
 14    ,        3
 15    ,        SYSDATE
 16    FROM     dual;
 
1 row created.

    Dominic Brooks

    17 Nov 08 at 3:06 am

Leave a Reply