Combiner WITH et CREATE TABLE AS

Bonjour,

je n'arrive pas à combiner l'utilisation de "tables temporaires" avec l'instruction "WITH" et l'instruction "Create table as select".
Je ne comprends pas

exemple :
je fais :

Code:

---

1 2	with coco as (select c1 from aaa ) select * from coco;

---

ça marche.
(j'ai simplifié la vraie requête)

Je voudrais stocker le résultat dans une nouvelle table, donc je fais :

Code:

---

1 2	with coco as (select c1 from aaa ) create table aaa_ajeter as (select * from coco);

---

ça ne marche pas, ça me donne l'erreur suivante :
"ORA-00928: missing SELECT keyword
00928. 00000 - "missing SELECT keyword"

Ce que je souhaite faire est-il possible ?

Je vous remercie par avance

Les vue dynamiques créée avec WITH ne sont utilisables que pour des consultation.

Ceci dit ce que vous souhaitez faire reste possible en remplaçant la clause WITH par une vue dynamique directement au niveau du "FROM"

Il faut employer la bonne syntaxe

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 Connected as mni   SQL> SQL> CREATE TABLE aaa_ajeter AS 2 with data as 3 (select 1 as id from dual 4 ) 5 Select id 6 from data 7 /   Table created   SQL>

---

OK
Mais du coup, c'est vraiment pas top, pourquoi cette limitation ??

Exemple :
au lieu de faire ça :

Code:

---

1 2 3

with toto as (select ...), tata as (select ... from toto, t1) create table as (select .. from toto, tata).

---

Je vais être obligé de faire :

Code:

---

create table as (select ... from (select ...) , (select ... from (select... )))

---

en somme d'imbriquer les select from (select ...)

Question subsidiaire : Oracle est il capable de "voir" qu'il y a des vue dynamiques identiques dans plusieurs requêtes et d'optimiser tout ça ?

Ok ça marche merci !!

Citation:

---

Envoyé par mnitu

Il faut employer la bonne syntaxe

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 Connected as mni   SQL> SQL> CREATE TABLE aaa_ajeter AS 2 with data as 3 (select 1 as id from dual 4 ) 5 Select id 6 from data 7 /   Table created   SQL>

---

---

Juste pour apporter une précision :

Code:

---

1 2 3 4 5 6

CREATE TABLE aaa_ajeter AS WITH DATA AS (SELECT 1 AS id FROM dual) SELECT id FROM DATA /

---

va marcher, alors que :

Code:

---

1 2 3 4 5 6

CREATE TABLE aaa_ajeter AS ( WITH DATA AS (SELECT 1 AS id FROM dual) SELECT id FROM DATA ) /

---

ne marchera pas. (je ne comprends pas la logique dans tout cela)
C'était pourquoi mes essais précédents avaient échoué.

Peut-être est-il utile d'ouvrir une SR chez Oracle sur le sujet. Ce n'est en effet pas logique

Citation:

---

Envoyé par ojo77

Peut-être est-il utile d'ouvrir une SR chez Oracle sur le sujet. Ce n'est en effet pas logique

---

Je ne vois pas en quoi c'est illogique. Il faut suivre la syntaxe définie dans la documentation et pas s'inventer des règles.

Dans le CREATE TABLE, tu as ceci:

Citation:

---

...Tables are created with no data unless a subquery is specified. ...

---

Tu vas donc dans la partie qui explique ce qu'est le AS subquery et tu lis...

Citation:

---

Specify a subquery to determine the contents of the table. The rows returned by the subquery are inserted into the table upon its creation.

For object tables, subquery can contain either one expression corresponding to the table type, or the number of top-level attributes of the table type. Please refer to SELECT for more information.

---

Et quand tu te réfères à la syntaxe d'un SELECT <added>NULLE PART</added> tu vois qu'une parenthèse peut précéder un WITH!!!!


Ceci ne marche pas!!
(WITH data AS (SELECT * FROm dual) SELECT * FROM data); non plus. Et c'est logique

Citation:

---

Envoyé par amir.

...
Et quand tu te réfères à la syntaxe d'un SELECT tu vois qu'une parenthèse peut précéder un WITH!!!!
...

---

Personnellement je ne vois pas la même chose que vous, c’est-à-dire qu’une parenthèse pourrait précéder le with.

Citation:

---

Envoyé par mnitu

Personnellement je ne vois pas la même chose que vous, c’est-à-dire qu’une parenthèse pourrait précéder le with.

---

Faut me montrer où alors...


1. http://docs.oracle.com/cd/B19306_01/...ents_10002.htm

select::=
2. http://docs.oracle.com/cd/B19306_01/...img/select.gif

subquery::=
3. http://docs.oracle.com/cd/B19306_01/...g/subquery.gif

subquery_factoring_clause ::=
4. http://docs.oracle.com/cd/B19306_01/...ing_clause.gif


A quel moment avant d'arriver au subquery_factoring_clause tu vois une parenthèse??

Bref, si vous dite qu'une parenthèse ne peut pas précéder le With nous sommes d'accord et je vous ai mal compris.

Normalement l'utilisaation de parenthèses superfétatoires est transparente dans le SQL oracle donc je ne comprends pas qu'on ne puisse pas en mettre autour le la requête select permettant l'alimentation d'une table lors d'un CTAS d'autant que d'une par lorsqu'il y a CTAS sans with on peut le faire :

Code:

---

1 2 3 4

SQL> create table toto as ( select * from dba_objects where rownum < 10 ) 2 /   Table created.

---

Et qu'on peut le faire hors d'un CTAS :

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49

SQL> select * 2 from ( with t as ( select * from dba_objects where rownum < 10 ) select * from t ) 3 /   OWNER ------------------------------ OBJECT_NAME -------------------------------------------------------------------------------------------------------------------------------- SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED LAST_DDL TIMESTAMP STATUS T G S ------------------------------ ---------- -------------- ------------------- -------- -------- ------------------- ------- - - - SYS ICOL$ 20 2 TABLE 18/03/12 18/03/12 2012-03-18:18:31:38 VALID N N N   SYS I_USER1 44 44 INDEX 18/03/12 18/03/12 2012-03-18:18:31:38 VALID N N N   SYS CON$ 28 28 TABLE 18/03/12 18/03/12 2012-03-18:18:31:38 VALID N N N   SYS UNDO$ 15 15 TABLE 18/03/12 18/03/12 2012-03-18:18:31:38 VALID N N N   SYS C_COBJ# 29 29 CLUSTER 18/03/12 18/03/12 2012-03-18:18:31:38 VALID N N N   SYS I_OBJ# 3 3 INDEX 18/03/12 18/03/12 2012-03-18:18:31:38 VALID N N N   SYS PROXY_ROLE_DATA$ 25 25 TABLE 18/03/12 18/03/12 2012-03-18:18:31:38 VALID N N N   SYS I_IND1 39 39 INDEX 18/03/12 18/03/12 2012-03-18:18:31:38 VALID N N N   SYS I_CDEF2 51 51 INDEX 18/03/12 18/03/12 2012-03-18:18:31:38 VALID N N N     9 rows selected.

---

Le fait qu'on puisse parenthéser dans un cas et pas dans un autre me parait incohérent.

Citation:

---

Envoyé par ojo77

Normalement l'utilisaation de parenthèses superfétatoires est transparente dans le SQL oracle

---

Essayez ceci :

Code:

---

select * (from) dual;

---

Citation:

---

Envoyé par ojo77

Normalement l'utilisaation de parenthèses superfétatoires est transparente dans le SQL oracle donc je ne comprends pas qu'on ne puisse pas en mettre autour le la requête select permettant l'alimentation d'une table lors d'un CTAS d'autant que d'une par lorsqu'il y a CTAS sans with on peut le faire :

Code:

---

1 2 3 4

SQL> create table toto as ( select * from dba_objects where rownum < 10 ) 2 /   Table created.

---

Et qu'on peut le faire hors d'un CTAS :

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49

SQL> select * 2 from ( with t as ( select * from dba_objects where rownum < 10 ) select * from t ) 3 /   OWNER ------------------------------ OBJECT_NAME -------------------------------------------------------------------------------------------------------------------------------- SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED LAST_DDL TIMESTAMP STATUS T G S ------------------------------ ---------- -------------- ------------------- -------- -------- ------------------- ------- - - - SYS ICOL$ 20 2 TABLE 18/03/12 18/03/12 2012-03-18:18:31:38 VALID N N N   SYS I_USER1 44 44 INDEX 18/03/12 18/03/12 2012-03-18:18:31:38 VALID N N N   SYS CON$ 28 28 TABLE 18/03/12 18/03/12 2012-03-18:18:31:38 VALID N N N   SYS UNDO$ 15 15 TABLE 18/03/12 18/03/12 2012-03-18:18:31:38 VALID N N N   SYS C_COBJ# 29 29 CLUSTER 18/03/12 18/03/12 2012-03-18:18:31:38 VALID N N N   SYS I_OBJ# 3 3 INDEX 18/03/12 18/03/12 2012-03-18:18:31:38 VALID N N N   SYS PROXY_ROLE_DATA$ 25 25 TABLE 18/03/12 18/03/12 2012-03-18:18:31:38 VALID N N N   SYS I_IND1 39 39 INDEX 18/03/12 18/03/12 2012-03-18:18:31:38 VALID N N N   SYS I_CDEF2 51 51 INDEX 18/03/12 18/03/12 2012-03-18:18:31:38 VALID N N N     9 rows selected.

---

Le fait qu'on puisse parenthéser dans un cas et pas dans un autre me parait incohérent.

---

Tu dois faire la distinction entre une Common Table Expression et une SubQuery. Je suis d'accord avec toi; ceci fonctionnera car c'est une SubQuery et que les parenthèses sont nécessaires dans ce cas ci. C'est une SubQuery.

Code:

---

1 2 3

SELECT * FROM ( WITH t AS ( SELECT * FROM dba_objects WHERE rownum < 10 ) SELECT * FROM t );

---

Par contre ceci ne sera pas autorisé:

[code]
(WITH t AS ( SELECT * FROM dba_objects WHERE rownum < 10 ))
SELECT *
FROM t ;
[code]

Une SubQuery peut contenir une CTE. Mais dans ce cas là, le WITH doit toujours être le premier mot clé; avant même le SELECT et ne peut pas être précédé d'une parenthèse. C'est pour cette raison que ton create table as select (select * from dual) fonctionnera. Tu as une simple subquery. Mais ton create table as (with ...) ne fonctionnera pas car le with est précédé d'une parenthèse.

Honnêtement, regarde dans la documentation et les petits schémas très bien fait qui te décrivent la syntaxe correcte. Et non, les parenthèses ne sont pas toujours transparentes. Déjà l'exemple que Waldar a mis ne fonctionnera pas. Déjà une première preuve. Mais ceci ne fonctionnera pas non plus.

SELECT * FROM dual (t);
SELECT 2 + 4 FROM dual (WHERE) 1 = 2;

Et ceci est correctement documenté :)

Je re re (re ?) formule ce qui me parait incohérent :

Pourquoi

Code:

---

create table toto as select * from dual

---

et

Code:

---

create table toto as ( select * from dual )

---

fonctionnent l'une et l'autre alors que seule fonctionne

Code:

---

create table toto as with titi as (select * from dual) select * from titi

---

et donc pas

Code:

---

create table toto as (with titi as (select * from dual) select * from titi)

---

ça ne me parait pas cohérent.

Mais bon maintenant que je le sais j'arriverai à vivre avec ne vous en faites pas ;)

Citation:

---

Envoyé par Waldar

Essayez ceci :

Code:

---

select * (from) dual;

---

---

Superfétatoire veut dire inutile, mais qui respecte tout de même la grammaire du langage comme dans cet exemple :

Code:

---

SELECT * FROM (DUAL);

---

Exemple qui fonctionne je vous épargne le test ...