Discussion :

[losformen]

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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

[ojo77]

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"

[mnitu]

Il faut employer la bonne syntaxe

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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>

[losformen]

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

Exemple :
au lieu de faire ça :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 ?

[losformen]

Ok ça marche merci !!

Il faut employer la bonne syntaxe

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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>

[losformen]

Juste pour apporter une précision :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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é.

[ojo77]

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

[amir.]

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:

...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...

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

[mnitu]

...
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.

[amir.]

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??

[mnitu]

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.

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

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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.

[Waldar]

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

Essayez ceci :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

select * (from) dual;

[amir.]

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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é

[ojo77]

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

Pourquoi

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

create table toto as select * from dual

et

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

create table toto as ( select * from dual )

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

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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

et donc pas

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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

[ojo77]

Essayez ceci :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

select * (from) dual;

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

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

SELECT * FROM (DUAL);

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