Table doesn't exist

Version imprimable

Table doesn't exist

Bonjour,

j'ai une requete sql a faire dans laquelle je dois afficher des données en comptant un nombre d'apparitions en fonction d'autres donnés,

en un peu plus clair :

maTable1 :

Defaut | valeur 1
-------|---------
a | xx1
b | xx2
c | xx1
d | xx3
e | xx1
f | xx2

maTable2 :

Defaut | occ
-------|-----
a | 3
b | 2
c | 7
d | 12
e | 1
f | 2

et je voudrais ressortir :

valeur 1 | nbApparitions | nb_occ > 3 | nb_occ>10
--------|--------------|------------|------------
xx1 | 3 | 2 | 0
xx2 | 2 | 0 | 0
xx3 | 1 | 1 | 1

en gros, pour chaque valeurs xxx je compte combien j'ai de défauts , combien j'ai de défauts avec 3 occurences ou plus et combien j'ai de défauts avec 10 occurences ou plus.

j'ai fait une requete du type
Code:

1 2 3 4 5 6 SELECT maTable1.valeur1, (select count(*) from tmp_table) as nbApparitions, (select count(*) from tmp_table where occ>3) as nb_occ>3, (select count(*) from tmp_table where occ>10) as nb_occ>10 FROM maTable1, maTable2 tmp_table where maTable1.Defaut = maTable2.Defaut
Quand je lance la requete, il me dis que tmp_table n'existe pas

Si quelqu'un a une idée,

Merci beaucoup

L'alias n'est pas valide dans les requêtes scalaire (les requêtes au niveau du select) :

Code:

1
2
3
4
5
6
SELECT maTable1.valeur1,
(select count(*) from maTable2) as nbApparitions,
(select count(*) from maTable2 where occ>3) as nb_occ>3,
(select count(*) from maTable2 where occ>10) as nb_occ>10
FROM maTable1, maTable2 tmp_table
where maTable1.Defaut = maTable2.Defaut

Maintenant cette requête est largement améliorable :

Code:

1
2
3
4
5
6
7
8
9
10
11
SELECT
    mt1.valeur1,
    count(*) as nbApparitions,
    count(case when mt2.occ >  3 then 1 else null end) as nb_occ_sup3,
    count(case when mt2.occ > 10 then 1 else null end) as nb_occ_sup10
FROM
    maTable1 mt1
    INNER JOIN maTable2 mt2
      on mt1.Defaut = mt2.Defaut
GROUP BY
    mt1.valeur1

28/07/2009, 09h06
raph382

Salut et merci pour ta réponse. Ca marche maintenant, je savait pas que les alias etaient pas exploitables comme ca!

Sinon pour l'amelioration, le probleme c'est qu'en fait ma requete est un peu plus compliquée que ca, je vais prendre d'autres données dans d'autres tables et tout, enfin bref ma requete fais environ 40 lignes...

et si je met directement des count dans mon select, j'ai le message d'erreur suivant :

Code:

SQL Error: ORA-00937: not a single-group group function

alors je sait pas trop ce que ca veut dire, si t'as une idée!!

En tout cas déja comme ca ca a bien optimisé puisque ma requete met 40s a répondre alors qu'en ayant fait d'une autre maniere, elle mettait 11minutes!!!

Une autre question, j'ai des données relatives aux défauts et j'aimerais faire un count sur les 5 premiers défauts rangés par ordre de cette donnée,
Est-ce que je peux, dans le "FROM" demander a ranger maTable2 dans un certain ordre afin de faire

Code:

( select count(*) from maTable2 where rownum<=5 and maConditionSurLaDonnée),
28/07/2009, 09h16
raph382

ah modification, en fait ca marche pas, il me compte le nombre de défauts au total, sans tronquer sur les xxx!!!!!
28/07/2009, 09h21
CinePhil

Et si tu nous donnais ta vraie requête ainsi que la structure des tables, comme il est demandé dans les règle de ce forum ?

alors voila ma requete qui fonctionne :

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
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
select distinct OPC.LAYER as MYLAYER,
                TD.DETECTOR,
                TL.TECHNOS_LAYERS,
                REPLACE(RM.OWNERNAME,'.',' ') as OWNERNAME,
                ( select Count(*) from OPC OPC1, TACHYON_DEFECT_BF TD1, JOB_TACHYON JT1,TACHYON_DEFECT_PROPERTIES TDP1
                  where JT1.NODE = OPC1.NODE and OPC1.tech = OPC.TECH and OPC1.LAYER = opc.layer and TD1.DETECTOR= td.detector
                  and JT1.TACHYON_LEVEL = OPC1.ABR and TD1.JOB_ID=JT1.JOB_ID and TD1.DEFECT_NAME = TDP1.TACHYON_NAME) as nb_def,
 
                ( select Count(*) from OPC OPC1, TACHYON_DEFECT_BF TD1, JOB_TACHYON JT1,TACHYON_DEFECT_PROPERTIES TDP1
                  where JT1.NODE = OPC1.NODE and OPC1.tech = OPC.TECH and OPC1.LAYER = opc.layer and TD1.DETECTOR= td.detector
                  and JT1.TACHYON_LEVEL = OPC1.ABR and TD1.JOB_ID=JT1.JOB_ID and TD1.DEFECT_NAME = TDP1.TACHYON_NAME
                  and tdp1.meta_family<>'Not_defined') as nb_defc,
 
                ( select Count(*) from OPC OPC1, TACHYON_DEFECT_BF TD1, JOB_TACHYON JT1,TACHYON_DEFECT_PROPERTIES TDP1
                  where JT1.NODE = OPC1.NODE and OPC1.tech = OPC.TECH and OPC1.LAYER = opc.layer and TD1.DETECTOR= td.detector
                  and JT1.TACHYON_LEVEL = OPC1.ABR and TD1.JOB_ID=JT1.JOB_ID and TD1.DEFECT_NAME = TDP1.TACHYON_NAME
                  and tdp1.occurence > 3) as nb_def3,
 
                ( select Count(*) from OPC OPC1, TACHYON_DEFECT_BF TD1, JOB_TACHYON JT1,TACHYON_DEFECT_PROPERTIES TDP1
                  where JT1.NODE = OPC1.NODE and OPC1.tech = OPC.TECH and OPC1.LAYER = opc.layer and TD1.DETECTOR= td.detector
                  and JT1.TACHYON_LEVEL = OPC1.ABR and TD1.JOB_ID=JT1.JOB_ID and TD1.DEFECT_NAME = TDP1.TACHYON_NAME
                  and tdp1.occurence > 3) as nb_defc3,
 
                ( select Count(*) from OPC OPC1, TACHYON_DEFECT_BF TD1, JOB_TACHYON JT1,TACHYON_DEFECT_PROPERTIES TDP1
                  where JT1.NODE = OPC1.NODE and OPC1.tech = OPC.TECH and OPC1.LAYER = opc.layer and TD1.DETECTOR= td.detector
                  and JT1.TACHYON_LEVEL = OPC1.ABR and TD1.JOB_ID=JT1.JOB_ID and TD1.DEFECT_NAME = TDP1.TACHYON_NAME
                  and tdp1.occurence > 10) as nb_def10,
 
                ( select Count(*) from OPC OPC1, TACHYON_DEFECT_BF TD1, JOB_TACHYON JT1,TACHYON_DEFECT_PROPERTIES TDP1
                  where JT1.NODE = OPC1.NODE and OPC1.tech = OPC.TECH and OPC1.LAYER = opc.layer and TD1.DETECTOR= td.detector
                  and JT1.TACHYON_LEVEL = OPC1.ABR and TD1.JOB_ID=JT1.JOB_ID and TD1.DEFECT_NAME = TDP1.TACHYON_NAME
                  and tdp1.occurence > 10 and tdp1.meta_family<>'Not_defined') as nb_defc10,
 
                ( select Count(*) from OPC OPC1, TACHYON_DEFECT_BF TD1, JOB_TACHYON JT1,TACHYON_DEFECT_PROPERTIES TDP1
                  where JT1.NODE = OPC1.NODE and OPC1.tech = OPC.TECH and OPC1.LAYER = opc.layer and TD1.DETECTOR= td.detector
                  and JT1.TACHYON_LEVEL = OPC1.ABR and TD1.JOB_ID=JT1.JOB_ID and TD1.DEFECT_NAME = TDP1.TACHYON_NAME
                  and tdp1.occurence > 30) as nb_def30,
 
                ( select Count(*) from OPC OPC1, TACHYON_DEFECT_BF TD1, JOB_TACHYON JT1,TACHYON_DEFECT_PROPERTIES TDP1
                  where JT1.NODE = OPC1.NODE and OPC1.tech = OPC.TECH and OPC1.LAYER = opc.layer and TD1.DETECTOR= td.detector
                  and JT1.TACHYON_LEVEL = OPC1.ABR and TD1.JOB_ID=JT1.JOB_ID and TD1.DEFECT_NAME = TDP1.TACHYON_NAME
                  and tdp1.occurence > 30 and tdp1.meta_family<>'Not_defined') as nb_defc30,
 
                ( select Count(*) from OPC OPC1, TACHYON_DEFECT_BF TD1, JOB_TACHYON JT1,TACHYON_DEFECT_PROPERTIES TDP1
                  where JT1.NODE = OPC1.NODE and OPC1.tech = OPC.TECH and OPC1.LAYER = opc.layer and TD1.DETECTOR= td.detector
                  and JT1.TACHYON_LEVEL = OPC1.ABR and TD1.JOB_ID=JT1.JOB_ID and TD1.DEFECT_NAME = TDP1.TACHYON_NAME
                  and tdp1.occurence > 100) as nb_def100,
 
                ( select Count(*) from OPC OPC1, TACHYON_DEFECT_BF TD1, JOB_TACHYON JT1,TACHYON_DEFECT_PROPERTIES TDP1
                  where JT1.NODE = OPC1.NODE and OPC1.tech = OPC.TECH and OPC1.LAYER = opc.layer and TD1.DETECTOR= td.detector
                  and JT1.TACHYON_LEVEL = OPC1.ABR and TD1.JOB_ID=JT1.JOB_ID and TD1.DEFECT_NAME = TDP1.TACHYON_NAME
                  and tdp1.occurence > 100 and tdp1.meta_family<>'Not_defined') as nb_defc100
from OPC, 
    TACHYON_DEFECT_BF TD, 
    JOB_TACHYON JT,
    ( select distinct TECH,LAYER, wmsys.wm_concat(TECHNO_LAYER) TECHNOS_LAYERS
      from (select distinct OPC2.TECH, OPC2.LAYER ,'{' || JT2.NODE || ' ' || JT2.TACHYON_LEVEL || '}' as TECHNO_LAYER
            from JOB_TACHYON JT2, OPC OPC2
            where JT2.NODE = OPC2.NODE
            and OPC2.TECH='12S'
            and JT2.TACHYON_LEVEL = OPC2.ABR)
      group by TECH,LAYER) TL,
    RET_MATRIX RM,
    TECHNO_GROUPS TG,
    LAYER_GROUPS LG
where JT.NODE = OPC.NODE
and OPC.TECH = TL.TECH
and JT.TACHYON_LEVEL = OPC.ABR
and TD.JOB_ID=JT.JOB_ID
and TL.LAYER = OPC.LAYER
and RM.LAYER_GROUP = LG.LAYER_GROUP
and RM.TECHNO_GROUP = TG.TECHNO_GROUP
and OPC.NODE = TG.NODE
and OPC.LAYER = LG.LAYER
order by MYLAYER,DETECTOR ASC

Et en ce qui concerne la structure des tables ...

(Je met que ce qui est nécéssaire sinon...)

Code:

1
2
3
4
5
6
JOB_TACHYON (JOB_ID,TACHYON_LEVEL,NODE)
LAYER_GROUPS (LAYER,LAYER_GROUP)
OPC (NODE,TECH,ABR,LAYER,NUM)
RET_MATRIX (TECHNO_GROUP,LAYER_GROUP,OWNERNAME)
TACHYON_DEFECT_BF (JOB_ID,DETECTOR,DEFECT_NAME,OCCURENCE)
TACHYON_DEFECT_PROPERTIES (TACHYON_NAME,PROPERTY_KEY,META_FAMILY)

En gros, je veux pour un OPC.TECH donné, recuperer les differents TACHYON_DEFECT_BF.DETECTORS et, les regrouper et compter combien de fois ils apparaissent selon la valeur de l'occurence et la valeur de TACHYON_DEFECT_PROPERTIES.PROPERTY_KEY

Le probleme c'est que comme ca, il me faut 11min pour faire ma requete, et ca fais un peu long et j'aimerais gagner du temps sur les count puisque c'est la meme table a chaque fois

Bon alors c'est bon, j'ai reussi a optimiser :

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
select distinct OPC.LAYER as MYLAYER,
                TD.DETECTOR,
                TL.TECHNOS_LAYERS,
                REPLACE(RM.OWNERNAME,'.',' ') as OWNERNAME,
                Count(*) as nb_def,
                Count(case when TDP.META_FAMILY <> 'Not_defined' then 1 else NULL end) AS nb_defc,
                Count(case when TDP.OCCURENCE >  3 then 1 else NULL end) AS nb_def3,
                Count(case when TDP.OCCURENCE >  3 and TDP.META_FAMILY <> 'Not_defined' then 1 else NULL end) AS nb_defc3,
                Count(case when TDP.OCCURENCE >  10 then 1 else NULL end) AS nb_def10,
                Count(case when TDP.OCCURENCE >  10 and TDP.META_FAMILY <> 'Not_defined' then 1 else NULL end) AS nb_defc10,
                Count(case when TDP.OCCURENCE >  30 then 1 else NULL end) AS nb_def30,
                Count(case when TDP.OCCURENCE >  30 and TDP.META_FAMILY <> 'Not_defined' then 1 else NULL end) AS nb_defc30,
                Count(case when TDP.OCCURENCE >  100 then 1 else NULL end) AS nb_def100,
                Count(case when TDP.OCCURENCE >  100 and TDP.META_FAMILY <> 'Not_defined' then 1 else NULL end) AS nb_defc100
from OPC, 
    TACHYON_DEFECT_BF TD, 
    JOB_TACHYON JT,
    ( select distinct TECH,LAYER, wmsys.wm_concat(TECHNO_LAYER) TECHNOS_LAYERS
      from (select distinct OPC2.TECH, OPC2.LAYER ,'{' || JT2.NODE || ' ' || JT2.TACHYON_LEVEL || '}' as TECHNO_LAYER
            from JOB_TACHYON JT2, OPC OPC2
            where JT2.NODE = OPC2.NODE
            and OPC2.TECH='12S'
            and JT2.TACHYON_LEVEL = OPC2.ABR)
      group by TECH,LAYER) TL,
    RET_MATRIX RM,
    TECHNO_GROUPS TG,
    LAYER_GROUPS LG,
    TACHYON_DEFECT_PROPERTIES TDP
where JT.NODE = OPC.NODE
and TD.DEFECT_NAME = TDP.TACHYON_NAME
and OPC.TECH = TL.TECH
and JT.TACHYON_LEVEL = OPC.ABR
and TD.JOB_ID=JT.JOB_ID
and TL.LAYER = OPC.LAYER
and RM.LAYER_GROUP = LG.LAYER_GROUP
and RM.TECHNO_GROUP = TG.TECHNO_GROUP
and OPC.NODE = TG.NODE
and OPC.LAYER = LG.LAYER
group by OPC.LAYER,DETECTOR,TECHNOS_LAYERS,OWNERNAME

27 secondes... merci WALDAR!!!!!

Bravo à vous pour avoir su l'implémenter à votre requête réelle.

Quelques petites choses à savoir :
Si vous faites une opération d'aggrégat, le distinct n'est pas nécessaire dans votre requête. Il n'apporte rien et peut être supprimé.

Dans votre group by, sauf besoin particulier, il faut remettre les mêmes éléments que dans votre select (moins les aggrégats).

Je vous invite très fortement à utiliser les jointures ANSI, c'est un vrai plus en écriture et en maintenance.

Je constate que vous avez utilisé wm_concat. Attention cette fonction n'est pas officiellement supportée par Oracle. Celà signifie qu'en cas de plantage vous n'obtiendrez pas de support de la part de l'éditeur et surtout que le comportement de cette fonction peut évoluer dans une prochaine release et donc le résultat de votre requête n'est pas garanti.

Personnellement j'utilise cette solution-ci :

Code:

substr(replace(replace(XMLAgg(XMLElement("x",<mon_champ>)),'</x>'),'<x>',';'),2)

Si ça ne fonctionne pas avec votre version, vous trouverez votre bonheur dans les liens suivant :
http://www.sqlsnippets.com/en/topic-11787.html
http://www.oracle-base.com/articles/...Techniques.php

Au final, votre requête s'écrirait :
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 50 51 52 53 54 select opc.layer as mylayer, td.detector, tl.technos_layers, replace(rm.ownername,'.',' ') as ownername, count(*) as nb_def, count(case when tdp.meta_family <> 'Not_defined' then 1 else null end) as nb_defc, count(case when tdp.occurence > 3 then 1 else null end) as nb_def3, count(case when tdp.occurence > 3 and tdp.meta_family <> 'Not_defined' then 1 else null end) as nb_defc3, count(case when tdp.occurence > 10 then 1 else null end) as nb_def10, count(case when tdp.occurence > 10 and tdp.meta_family <> 'Not_defined' then 1 else null end) as nb_defc10, count(case when tdp.occurence > 30 then 1 else null end) as nb_def30, count(case when tdp.occurence > 30 and tdp.meta_family <> 'Not_defined' then 1 else null end) as nb_defc30, count(case when tdp.occurence > 100 then 1 else null end) as nb_def100, count(case when tdp.occurence > 100 and tdp.meta_family <> 'Not_defined' then 1 else null end) as nb_defc100 from ( select opc2.tech, opc2.layer, substr(replace(replace(XMLAgg(XMLElement("x", '{' || jt2.node || ' ' || jt2.tachyon_level || '}')),'</x>'),'<x>',';'),2) as technos_layers from job_tachyon jt2 inner join opc opc2 on opc2.node = jt2.node and opc2.abr = jt2.tachyon_level where opc2.tech = '12S' group by opc2.tech, opc2.layer ) tl inner join opc on opc.tech = tl.tech and opc.layer = tl.layer inner join job_tachyon jt on jt.node = opc.node and jt.tachyon_level = opc.abr inner join tachyon_defect_bf td on td.job_id = jt.job_id inner join tachyon_defect_properties tdp on tdp.tachyon_name = td.defect_name inner join techno_groups tg on tg.node = opc.node inner join layer_groups lg on lg.layer = opc.layer inner join ret_matrix rm on rm.layer_group = lg.layer_group and rm.techno_group = tg.techno_group group by opc.layer, td.detector, tl.technos_layers, replace(rm.ownername,'.',' ')

Merci beaucoup pour les petites corrections, c'est vrai que c'est beauoup plus visible!

par contre, selon les données que je veux récuperer, ca me renvoie une erreur :
Code:

1 2 3 4 SQL Error: ORA-19011: Character string buffer too small 19011. 00000 - "Character string buffer too small" *Cause: The string result asked for is too big to return back *Action: Get the result as a lob instead
ca marche si j'utilise le wm_concat, mais si ca risque de poser des problemes pour la suite j'aimerais eviter.

apparament, le probleme viens du fait que les buffer XML ne peuvent dépasser 4Ko

y'a-t-il une autre solution ou je vais devoir utiliser le wm_concat?

29/07/2009, 10h55
Waldar

Il y a plusieurs solutions dans les deux liens que j'ai mis plus haut, effectivement si le XML n'est pas satisfaisant il faut utiliser autre chose.