Vérification requête SQL

Version imprimable

Bonjour,

J'ai une requête sql, où je voudrais savoir si j'ai des erreurs ou non, enfin plutôt des erreurs de structures.
Je vous demande cela, car quand je la rentre dans mon logiciel, il met plus de 30min à l'éxécuter, et affiche un résultat de plus de 500000lignes, ou il me dis pas assez de mémoire.

Voici la requête:

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
SELECT
f.id_fam,
p.tel_perso_per,
a.adr_rue,
a.adr_ville,
a.adr_cp,
al.id_all,
m.situation_per,
p.id_per,
p.sexe_per,
p.nom_per,
p.prenom_per,
p.date_naissance_per,
il.code_lie,
il.nom_lie,
i.nom_act,
'#Date début de période ../../....#' date_debut,
'#Date fin de période ../../....#' date_fin,
count(distinct date_rsv) nb_jours
FROM
cr_famille f,
cr_personne p,
cr_adresse a,
cr_allocataire al,
cr_inscription i,
cr_inscription_lieu il,
cr_inscription_presence ip,
cr_membre_famille m
WHERE
i.id_fam = f.id_fam and
i.id_per_ins = p.id_per and
i.id_ins = il.id_ins and
i.id_ins = ip.id_ins and
-- Restrcition sur la période
ip.date_rsv >= to_number(to_char(to_date('#Date début de période ../../....#'),'YYYYMMDD')) and
ip.date_rsv <= to_number(to_char(to_date('#Date fin de période ../../....#'),'YYYYMMDD')) and
lower(il.code_lie) = lower('#Libellé court du lieu :#') and
--Que les présences
ip.type_heure_rsv = 'P'
GROUP BY
f.id_fam,
p.tel_perso_per,
a.adr_rue,
a.adr_ville,
a.adr_cp,
al.id_all,
m.situation_per,
p.id_per,
p.sexe_per,
p.nom_per,
p.prenom_per,
p.date_naissance_per,
il.code_lie,
il.nom_lie,
i.nom_act

Merci d'avance pour votre aide.

25/02/2013, 16h28
Pozzo

Bonjour tifil,

Essayez de formater votre code et d'utiliser les balises code ça sera plus facile à lire.

A part ça je ne vois pas de jointure sur les tables adresse, allocataire, membrefamille.
Ca veut dire que vous faites un produit cartésien sur ces tables ce qui démultiplie le résultat.
Ca peut expliquer vos soucis.

Pozzo
25/02/2013, 16h35
tifil

Pardon, pour le code, j'ai rectifié le tir.

Quand vous dites, jointures sur ces 3 tables, pourquoi que ces 3 tables, et non les autres ?
25/02/2013, 17h13
Rei Ichido

Il n'y a aucune restriction sur ces tables.
On peut penser par exemple que l'adresse devrait être liée à la personne, que membre_famille doit être la table de jointure entre personne et famille, et que allocataire doit être liée à la personne et/ou à inscription.

En écrivant vos requêtes en jointure ANSI, vous auriez pu détecter ce problème à la source !

En utilisant la notation normalisée depuis 20 ans pour les jointures, vous obtiendriez un code plus lisible et vous verriez immédiatement que les tables cr_adresse, cr_allocataire et cr_membre_famille ne sont liées à aucune autre table.

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
SELECT  f.id_fam,
        p.tel_perso_per,
        a.adr_rue,
        a.adr_ville,
        a.adr_cp,
        al.id_all,
        m.situation_per,
        p.id_per,
        p.sexe_per,
        p.nom_per,
        p.prenom_per,
        p.date_naissance_per,
        il.code_lie,
        il.nom_lie,
        i.nom_act,
        '#Date début de période ../../....#' date_debut,
        '#Date fin de période ../../....#' date_fin,
        COUNT(DISTINCT date_rsv) nb_jours
FROM    cr_inscription  i
    INNER JOIN
        cr_famille      f
        ON  i.id_fam = f.id_fam
    INNER JOIN
        cr_personne     p
        ON  i.id_per_ins = p.id_per
    INNER JOIN
        cr_inscription_lieu     il
        ON  i.id_ins = il.id_ins
    INNER JOIN
        cr_inscription_presence ip
        ON  i.id_ins = ip.id_ins
    ,   cr_adresse          a
    ,   cr_allocataire      al
    ,   cr_membre_famille   m
WHERE   -- Restrcition sur la période
        ip.date_rsv >= to_number(to_char(to_date('#Date début de période ../../....#'),'YYYYMMDD'))
    AND ip.date_rsv <= to_number(to_char(to_date('#Date fin de période ../../....#'),'YYYYMMDD'))
    AND LOWER(il.code_lie) = lower('#Libellé court du lieu :#')
        --Que les présences
    AND ip.type_heure_rsv = 'P'
GROUP BY
        f.id_fam,
        p.tel_perso_per,
        a.adr_rue,
        a.adr_ville,
        a.adr_cp,
        al.id_all,
        m.situation_per,
        p.id_per,
        p.sexe_per,
        p.nom_per,
        p.prenom_per,
        p.date_naissance_per,
        il.code_lie,
        il.nom_lie,
        i.nom_act

25/02/2013, 18h48
Waldar

En plus des remarques déjà relatées, quel est le type de la colonne cr_inscription_presence.date_rsv ?
Un index existe-t-il sur cette colonne ?
26/02/2013, 10h00
tifil

Pour répondre à al1_24, j'ai rentré votre code, et malheureusement, il met toujours plus de 30minutes à éxécuter la requête.

Pour Rei Ichido, je ne connais pas vraiment la jointure ANSI, je ne saurais donc le faire :(

Pour Waldar, le type est NUMBER.
26/02/2013, 11h14
al1_24

Je n'ai rien corrigé, j'ai simplement mis en évidence les produits cartésiens.

Pour la notation des jointures, voir ici.

Voici mon nouveau code, ou j'ai jouté des jointures:

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
SELECT
t1.id_fam,
t2.tel_perso_per,
t2.prenom_per,
t2.nom_per,
t3.adr_rue,
t4.id_all,
'#Date début de période ../../....#' date_debut,
'#Date fin de période ../../....#' date_fin,
COUNT(DISTINCT date_rsv) nb_jours
FROM
cr_famille as t1,
INNER JOIN 
	cr_personne as t2,
INNER JOIN 
	cr_adresse as t3,
INNER JOIN 
	cr_allocataire as t4,
WHERE   
-- Restrcition sur la période
	ip.date_rsv >= to_number(to_char(to_date('#Date début de période ../../....#'),'YYYYMMDD'))
AND ip.date_rsv <= to_number(to_char(to_date('#Date fin de période ../../....#'),'YYYYMMDD'))
AND LOWER(il.code_lie) = lower('#Libellé court du lieu :#')
--Que les présences
AND ip.type_heure_rsv = 'P'
GROUP BY
f.id_fam,
p.tel_perso_per,
a.adr_rue,
al.id_all,
p.nom_per,
p.prenom_per

Dans les INNER JOIN, je n'ai pas de on etc... car je n'ai pas les mêmes id dans les tables.

Par contre, j'ai une petite erreur, il me dit que la commande sql ne se termine pas correctement.

26/02/2013, 15h57
vttvolant
Code:

1 2 3 4 5 cr_adresse as t3, INNER JOIN cr_allocataire as t4, WHERE
tu as laissé trainer une virgule
26/02/2013, 15h58
Rei Ichido

Et pourtant al1_24 avait commencé à faire le boulot !
La clause ON est obligatoire après un INNER JOIN. Il faut donc la mettre - sinon, votre requête SQL ne se termine pas correctement ... :aie:

Toute la question est de savoir comment il faut faire les jointures - quelles sont les colonnes à mettre en regard, si vous préférez.
26/02/2013, 16h38
tifil

Dans le code de al1_24, il ya des jointures, qui selon moi sont bonnes, mais apparement, cela n'est pas correcte selon lui.

Sinon, juste après le INNER JOIN, je rajoute bien le "AS t2" non ?

Et, ça devrait être correct.
26/02/2013, 17h33
Waldar

Oracle ne supporte pas le mot clef AS pour les alias de tables.

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
SELECT
t1.id_fam,
t2.tel_perso_per,
t2.prenom_per,
t2.nom_per,
t3.adr_rue,
t4.id_all,
'#Date début de période ../../....#' date_debut,
'#Date fin de période ../../....#' date_fin,
COUNT(DISTINCT date_rsv) nb_jours
FROM
cr_famille t1
INNER JOIN 
	cr_personne t2
	ON T1.<tu dois mettre la bonne colonne de jointure> = t2.<tu dois mettre la bonne colonne de jointure>
INNER JOIN 
	cr_adresse t3
	ON T3.<tu dois mettre la bonne colonne de jointure> = t<tu dois mettre la bonne table de jointure>.<tu dois mettre la bonne colonne de jointure>
INNER JOIN 
	cr_allocataire t4
	ON T4.<tu dois mettre la bonne colonne de jointure> = t<tu dois mettre la bonne table de jointure>.<tu dois mettre la bonne colonne de jointure>
WHERE   
-- Restrcition sur la période
	ip.date_rsv >= to_number(to_char(to_date('#Date début de période ../../....#'),'YYYYMMDD'))
AND ip.date_rsv <= to_number(to_char(to_date('#Date fin de période ../../....#'),'YYYYMMDD'))
AND LOWER(il.code_lie) = lower('#Libellé court du lieu :#')
--Que les présences
AND ip.type_heure_rsv = 'P'
GROUP BY
f.id_fam,
p.tel_perso_per,
a.adr_rue,
al.id_all,
p.nom_per,
p.prenom_per

tu ne respectes pas la syntaxe (pas de virgule et les jointures sont obligatoires)

27/02/2013, 18h49
tifil

Mais dans le ON, du INNER JOIN, si je n'ai pas le même id dans les 2 tables, comment faire ?

Par exemple, dans ma table cr_adresse, je n'ai pas d'id qui sont dans la table cr-personne ou autres.
27/02/2013, 18h57
al1_24

Peut-être as-tu dans ton modèle de données d'autres tables permettant d'associer les lignes de celles-ci ?
Sinon, tu entres dans ce qu'on appelle un produit cartésien : si tu as 300 lignes dans cr_personne et 400 dans cr_adresses, tu obtiendras 120000 lignes en résultat :aie:
Fais le même calcul en ajoutant deux autres tables avec 500 et 600 lignes respectivement, cela donnerait 36000000000 lignes en tout.
Je ne suis pas sur que c'est ce que tu attends ! :?
28/02/2013, 16h20
tifil

Oui effectivement, j'ai pu constater cela, le résultat du produit cartésien, où j'attendais plus de 30min, pour avoir un affichage.

Je vais donc regarder, dans mon mcd, voir si j'ai pas des id identiques, et je reviendrais vers vous.
11/03/2013, 09h31
tifil
Désolé du temps écoulés entre mon dernier message et celui-ci. :?
Je reviens vers vous pour une nouvelle question.

Peut-on mettre plusieurs "ON" dans un INNER JOIN ? Je m'explique.

J'ai déja déclaré via un INNER JOIN ceci: cr_famille f, ensuite j'ai mis le "ON" correspondant avec la jointure.
Mais je dois maintenant, relier une table avec cr_famille, donc une autre jointure, donc est-ce que je peut faire ceci:
Code:

1 2 3 4 INNER JOIN cr_famille f ON i.id_fam = f.id_fam ON f.id-fam = i.id-fam
Ou bien je ne peut pas, et je dois faire 2 fois le INNER JOIN ?
11/03/2013, 10h41
al1_24

Un petit peu de lecture ?
11/03/2013, 11h03
mnitu

Citation:

Envoyé par Rei Ichido

...La clause ON est obligatoire après un INNER JOIN. Il faut donc la mettre - sinon, votre requête SQL ne se termine pas correctement ... :aie:
...

Using ?
Quelle marotte cette histoire de ANSI Join. Encore une fois: le problème c’est comprendre les jointures et non pas faire une obsession de la syntaxe.
Libre à chacun d’utiliser ce qui lui plaise.

Je viens de lire le cours sur les jointures, et j'ai effectivement eu ma réponse.
Par contre, je rencontre un probème assez génant, j'ai aussi des doublons, comme si la jointure n'est pas totalement faite.

Voici ma requête:

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
76
77
78
79
80
81
82
83
84
85
86
SELECT  
        p.tel_perso_per,
	a.adr_rue,
	a.adr_num,
	a.adr_cp,
	a.adr_ville,
 
	m.id_fam_rep1,
	m.situation_per,
	m.nom_per,
	m.prenom_per,
	m.email_perso_per,
	m.tel_portable_per,
	m.adr_num_per,
	m.adr_rue_per,
	m.adr_cp_per,
	m.adr_ville_per,
	m.profession_per,
	m.id_fam_rep2,
	m.nom_per,
	m.prenom_per,
	p.id_per,
        p.sexe_per,
        p.nom_per,
        p.prenom_per,
        p.date_naissance_per,
        il.code_lie,
        il.nom_lie,
        '#Date début de période ../../....#' date_debut,
        '#Date fin de période ../../....#' date_fin,
        COUNT(DISTINCT date_rsv) nb_jours
FROM    cr_inscription  i
    INNER JOIN
        cr_famille      f
        ON  i.id_fam = f.id_fam
    INNER JOIN
        cr_personne     p
        ON  i.id_per_ins = p.id_per
    INNER JOIN
        cr_inscription_lieu     il
        ON  i.id_ins = il.id_ins
    INNER JOIN
	cr_adresse	a
	ON f.id_adr = a.id_adr
    INNER JOIN
	cr_membre_famille	m
	ON f.id_fam = m.id_fam
    INNER JOIN
        cr_inscription_presence ip
        ON  i.id_ins = ip.id_ins
 
 
WHERE   -- Restrcition sur la période
        ip.date_rsv >= to_number(to_char(to_date('#Date début de période ../../....#'),'YYYYMMDD'))
    AND ip.date_rsv <= to_number(to_char(to_date('#Date fin de période ../../....#'),'YYYYMMDD'))
    AND LOWER(il.code_lie) = lower('#Libellé court du lieu :#')
        --Que les présences
    AND ip.type_heure_rsv = 'P'
GROUP BY
 
        p.tel_perso_per,
	a.adr_rue,
	a.adr_num,
	a.adr_cp,
	a.adr_ville,
	m.id_fam_rep1,
	m.situation_per,
	m.nom_per,
	m.prenom_per,
	m.email_perso_per,
	m.tel_portable_per,
	m.adr_num_per,
	m.adr_rue_per,
	m.adr_cp_per,
	m.adr_ville_per,
	m.profession_per,
	m.id_fam_rep2,
	m.nom_per,
	m.prenom_per,
	p.id_per,
        p.sexe_per,
        p.nom_per,
        p.prenom_per,
        p.date_naissance_per,
        il.code_lie,
        il.nom_lie

18/03/2013, 09h57
tifil
Up !!! :(

Je voudrais donc croiser 2 tables, cr_personne et cr_membre_famille.
Mais je les ai déja mises dans un inner join chacune.
Via ceci:
Code:

1 2 3 4 5 6 INNER JOIN cr_personne p ON m.id_per_ins = p.id_per INNER JOIN cr_membre_famille m ON f.id_fam = m.id_fam
Or, vu que je ne peut pas, mettre deux fois "ON" dans un inner join, et que je ne peut pas déclarer la table une deuxième fois via un inner join, donc comment faire pour croiser ces 2 tables ?
19/03/2013, 13h07
aieeeuuuuu
Bonjour,

Vous ne mettez qu'un ON, mais vous pouvez mettre plusieurs conditions, un peu comme pour un WHERE :
Code:

1 2 3 4 5 6 7 INNER JOIN B ON [condition1] AND [condition2] OR [condition3] ...

Bonjour,

Désolé du temps d'attente, mais j'étais en congé.
En tout cas, je tenais vraiment à remercier toutes les personnes qui m'ont aidé ici, c'est très gentil de votre part.

Mais j'ai une autre question maintenant. Voici la nouvelle requête:

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
76
77
78
SELECT  
	f.id_fam,
	a.adr_rue,
	a.adr_num,
	a.adr_cp,
	a.adr_ville,
	m.role_per,
	m.situation_per,
	m.prenom_per,
	m.nom_per,
	m.sexe_per,
	m.email_perso_per,
	m.tel_perso_per,
	m.tel_portable_per,
	m.profession_per,
        m.date2_naissance_per,
        il.code_lie,
        il.nom_lie,
	f.numero_alloc_fam,
	f.nb_enfant_fam,
	i.date2_debut_ins,
	i.etat_ins,
        '#Date début de période ../../....#' date_debut,
        '#Date fin de période ../../....#' date_fin,
        COUNT(DISTINCT date_rsv) nb_jours
FROM    cr_inscription  i
    INNER JOIN
        cr_famille      f
        ON  i.id_fam = f.id_fam
    INNER JOIN
        cr_personne     p
        ON  i.id_per_ins = p.id_per
    INNER JOIN
        cr_inscription_lieu     il
        ON  i.id_ins = il.id_ins
    INNER JOIN
	cr_adresse	a
	ON f.id_adr = a.id_adr
    INNER JOIN
	cr_membre_famille	m
	ON f.id_fam = m.id_fam
    INNER JOIN
        cr_inscription_presence ip
        ON  i.id_ins = ip.id_ins
    INNER JOIN
	cr_famille_contact	fc
	ON f.id_fam = fc.id_fam
 
 
WHERE   -- Restrcition sur la période
        ip.date_rsv >= to_number(to_char(to_date('#Date début de période ../../....#'),'YYYYMMDD'))
    AND ip.date_rsv <= to_number(to_char(to_date('#Date fin de période ../../....#'),'YYYYMMDD'))
    AND LOWER(il.code_lie) = lower('#Libellé court du lieu :#')
        --Que les présences
    AND ip.type_heure_rsv = 'P'
GROUP BY
 
        f.id_fam,
	a.adr_rue,
	a.adr_num,
	a.adr_cp,
	a.adr_ville,
	m.role_per,
	m.situation_per,
	m.prenom_per,
	m.nom_per,
	m.sexe_per,
	m.email_perso_per,
	m.tel_perso_per,
	m.tel_portable_per,
	m.profession_per,
        m.date2_naissance_per,
        il.code_lie,
        il.nom_lie,
	f.numero_alloc_fam,
	f.nb_enfant_fam,
	i.date2_debut_ins,
	i.etat_ins

Je voudrais, que le champ "date2_debut_ins" ne soit rempli que dans le cas où "role_per" est égal à 'E'. Pour cela, je met une clause where role_per = 'E', mais dans ce cas là, ça s'applique à toute ma requête, or je voudrais que cette condition ne s'applique que pour le champ "date2_debut_ins".
Comment faire ?

02/04/2013, 12h11
aieeeuuuuu
Bonjour,

Vous pouvez pour cela utiliser un CASE. Dans la partie SELECT, remplacez la colonne par :
Code:

1 2 CASE WHEN role_per = 'E' THEN i.date2_debut_ins END as date2_debut_ins,
Dans le résultat, cette colonne sera à NULL si role_per est différent de 'E'
02/04/2013, 14h41
tifil

Alors la, merci beaucoup l'ami. :)

Mais j'ai encore un petit souci, une fois ceci fait, dans la collone "date2_debut_ins", j'ai la même date pour chaque personne, alors que je devrais voir pourtant une date différente.