Problème dans la récupération de données

**android59** · 18/02/2020, 14h16

bonjour,
je dispose d'une requête , permettant de récupérer le nom, prenom et le nombre d'heure déclaré des personnes d'une entreprise, par année.

Afin de récupéré le nombre d'heure, j'ai établi une sous requête, mais j'ai constaté que parfois il me fait une erreur "ORA-01427: sous-requête ramenant un enregistrement de plus d'une ligne ".
j'ai compris ce que ça voulait dire, car il se trouve, que lorsque je compare avec mon logiciel, dans mon logiciel on peut avoir deux ligne, dans une même année, par exemple :

nom prenom année nbheure
Croc Odile 2018 1640
Croc Odile 2018 17
Gerard 1800
bernard 1500
Luc
.....

Ceci est un exemple, pour vous expliquer le contexte et donc voici ma requete :

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
 
SELECT distinct sal.numcontrat as "NUM",
... as NOM,
.... as PRENOM,
.... as annee,
(
select t1.nbheure
from table1 t1 
inner join tableannee t2 on t1.idreft2 = t2.d010_id 
inner join ....... t3 on t1.idcontrat = t3.=id 
inner join ........ t4 on t3.id2 = t4.id 
inner join ........ t5 on t5.id=t1.idreft5 
inner join ........ t6 on t6.id=t5.idrefent  
where annee =2016 
AND t6.id = ta.id
) as "montant 2016"
FROM ...
inner join.......
inner join........
inner join ........  trel on...
inner join table ta  on ta.id  = trel.idref1 
 
;

Ce que j'aimerais faire c'est si ma sous requête est suceptible de ramener plus d'une ligne, comme je selectionne des chiffres, de faire la somme mais si la sous requete ramène qu'une ligne, de garder le chiffre tel quel;

Vous me direz " faire un sum(t1.nbheure) mais si je fais ça, dans mon fichier excel que j'exporte, je vais avoir le total des heures par salarié or je souhaite avoir le détails.
En faisant le sum, dans mon fichier excel j'ai pu vérifier les totaux sont bon, mais moi je souhaite seulement le détails, je fais le total juste pour vérification.

Pour palier au problème que j'ai cité avant, j'ai tenté de modifié la jonction avec la requête principal et j'ai rajouté un "rownum=1" mais ça fausse mes résultat.
La requête tourne mais si je met le rownum, si une personne a par exemple 0 et 15 , la requête ne prendra en compte que 0 et donc pour peu que 2 ou 3 personnes possèdent deux lignes , mes totaux seront faux, donc vérification ko.

J'avais pensé à une chose, dans la sous requête, un case à savoir si par exemple pour une personne donnée, il a declaré 0 puis 12 ne prendre en compte que 12.

merci pour vos lumières.

**al1_24** · 18/02/2020, 14h42

Plutôt qu'une sous-requête scalaire, utilise une table dérivée. Ainsi tu n'auras plus de message d'erreur, mais le détail des lignes.

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
SELECT distinct sal.numcontrat as "NUM",
... as NOM,
.... as PRENOM,
.... as annee,
srq.nbheure as "montant 2016"
FROM ...
inner join.......
inner join........
inner join ........  trel on...
inner join table ta  on ta.id  = trel.idref1 
inner join
    (   select  t6.id
            ,   t1.nbheure
        from table1 t1 
        inner join tableannee t2 on t1.idreft2 = t2.d010_id 
        inner join ....... t3 on t1.idcontrat = t3.=id 
        inner join ........ t4 on t3.id2 = t4.id 
        inner join ........ t5 on t5.id=t1.idreft5 
        inner join ........ t6 on t6.id=t5.idrefent  
        where annee =2016 
    )   srq 
    on srq.id = ta.id
;

**android59** · 19/02/2020, 16h03

merci pour ton retour,

j'ai le message d'erreur suivant qui s'affiche, j'avoue que je ne comprend pas

Code :

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

1
2
3
4
5
6
7
 
ORA-01652: impossible d'étendre le segment temporaire de 128 dans le tablespace TEMP
01652. 00000 -  "unable to extend temp segment by %s in tablespace %s"
*Cause:    Failed to allocate an extent of the required number of blocks for
           a temporary segment in the tablespace indicated.
*Action:   Use ALTER TABLESPACE ADD DATAFILE statement to add one or more
           files to the tablespace indicated.

**vanagreg** · 19/02/2020, 16h34

La requête n'avait plus suffisamment d'espace temporaire. Quelle est la taille de ton tablespace temp? Tu peux vérifier ainsi:

select file_name, bytes/1024/1024 from dba_temp_files;

**android59** · 24/02/2020, 09h53

bonjour,
je suis sous oracle et sqldeveloper, et ça me dit :

ORA-00942: Table ou vue inexistante
00942. 00000 - "table or view does not exist"
*Cause:
*Action:
Erreur à la ligne 1, colonne 52

merci

**android59** · 24/02/2020, 09h54

j'ia mis une capture d'écran

**vanagreg** · 24/02/2020, 10h09

Bonjour,

Si tu as cette erreur c'est que n'as pas le privilège nécessaire pour interroger cette vue. Il faudra voir avec ton DBA.

**android59** · 24/02/2020, 10h30

j'ai également fait un schema pour illustrer ce que je souhaite obtenir au final :

merci

**android59** · 24/02/2020, 10h52

Envoyé par vanagreg

Bonjour,

Si tu as cette erreur c'est que n'as pas le privilège nécessaire pour interroger cette vue. Il faudra voir avec ton DBA.

merci pour ton retour,
en quelque sorte, je suis le "dba" lol, mais en ce qui concerne la base de données c'est autre chose, je n'ai accès qu'à la base prep mise à jour quotidiennement , au niveau des privilèges je suis assez restreint, si j'avais plus de privilèges , meme en prep, ça m'aiderais enormément sur mes requête mais bon j'ai pas mon mot à dire sur ce point là lol

**vanagreg** · 24/02/2020, 11h04

En effet si tu es le DBA et que tu n'as même pas accès aux vues du dictionnaire de données c'est qu'il y a un problème. Tu ne peux pas te connecter en SYSTEM ou en SYS?

**vanagreg** · 24/02/2020, 11h42

Je viens de voir ton schéma. Ce serait mieux si tu postais ta requête directement. J'ai l'impression que tu auras besoin de jointures externes. Il ne faut pas faire plusieurs sous-requêtes pour chaque année. Il vaut mieux utiliser des instructions CASE pour travailler sur l'année concernée.

**android59** · 25/02/2020, 09h15

bonjour,

voici un cas concret :

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
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
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
 
 
SELECT distinct sal.numerocontrat as "NUM.contrat",
tpers.nom as NOM,
tpers.prenom as PRENOM,
(
select t1.nbheure as Nombre_heures_2017
from table1 t1 
inner join EntbleAnnee t2 on t1.idrefEntnnee = t2.id 
inner join EntbleRelation  t3 on t1.referenceContrat = t3.id 
inner join tableEtablissement t4 on t3.idrefsal = t4.id 
inner join tableRecap t5 on t5.id=t1.idrefTableRecap 
inner join tableEtablissement t6 on t6.id=t5.idreft6 
inner join tablePersonne t7 on ..................
...... 
.....
where t2.annee =2017 
and t6.numentreprise='123456'
AND t6.id = Ent.id
and t1.id = nomi.id
) as "nbheure_2017",
(
select round(t1.montant,2) as montant_2017
from table1 t1 
inner join EntbleAnnee t2 on t1.idrefEntnnee = t2.id 
inner join EntbleRelation  t3 on t1.referenceContrat = t3.id 
inner join tableEtablissement t4 on t3.idrefsal = t4.id 
inner join tableRecap t5 on t5.id=t1.idrefTableRecap 
inner join tableEtablissement t6 on t6.id=t5.idreft6 
inner join tablePersonne t7 on ..................
...... 
.....
where t2.annee =2017 
and t6.numentreprise='123456'
AND t6.id = Ent.id
and t1.id = nomi.id
) as "montant_2017",
(
select t1.nbheure as Nombre_heures_2018
from table1 t1 
inner join EntbleAnnee t2 on t1.idrefEntnnee = t2.id 
inner join EntbleRelation  t3 on t1.referenceContrat = t3.id 
inner join tableEtablissement t4 on t3.idrefsal = t4.id 
inner join tableRecap t5 on t5.id=t1.idrefTableRecap 
inner join tableEtablissement t6 on t6.id=t5.idreft6 
inner join tablePersonne t7 on ..................
...... 
.....
where t2.annee =2018 
and t6.numentreprise='123456'
AND t6.id = Ent.id
and t1.id = nomi.id
) as "nbheure 2018",
(
select round(t1.montant,2) as MonEntnt_2018
from table1 t1 
inner join EntbleAnnee t2 on t1.idrefEntnnee = t2.id 
inner join EntbleRelation  t3 on t1.referenceContrat = t3.id 
inner join tableEtablissement t4 on t3.idrefsal = t4.id 
inner join tableRecap t5 on t5.id=t1.idrefTableRecap 
inner join tableEtablissement t6 on t6.id=t5.idreft6 
inner join tablePersonne t7 on ..................
...... 
.....
where t2.anneee =2018 
and t6.numentreprse='123456'
AND t6.id = Ent.id
and t1.id = nomi.id
) as "montant 2018",
(
select t1.nbheure as Nombre_heures_2019
from table1 t1 
inner join EntbleAnnee t2 on t1.idrefEntnnee = t2.id 
inner join EntbleRelation  t3 on t1.referenceContrat = t3.id 
inner join tableEtablissement t4 on t3.idrefsal = t4.id 
inner join tableRecap t5 on t5.id=t1.idrefTableRecap 
inner join tableEtablissement t6 on t6.id=t5.idreft6 
inner join tablePersonne t7 on ..................
...... 
.....
where t2.annee =2019 
and t6.numentreprise='123456'
AND t6.id = Ent.id
and t1.id = nomi.id
) as "nbheure 2019",
(
select round(t1.montant,2) as MonEntnt_2019
from table1 t1 
inner join EntbleAnnee t2 on t1.idrefEntnnee = t2.id 
inner join EntbleRelation  t3 on t1.referenceContrat = t3.id 
inner join tableEtablissement t4 on t3.idrefsal = t4.id 
inner join tableRecap t5 on t5.id=t1.idrefTableRecap 
inner join tableEtablissement t6 on t6.id=t5.idreft6 
inner join tablePersonne t7 on ..................
...... 
.....
where t2.annee =2019 
and t6.numentreprise='123456'
AND t6.id = Ent.id
and t1.id = nomi.id
) as "montant 2019",
(
select t1.nbheure as Nombre_heures_2020
from table1 t1 
inner join EntbleAnnee t2 on t1.idrefEntnnee = t2.id 
inner join EntbleRelation  t3 on t1.referenceContrat = t3.id 
inner join tableEtablissement t4 on t3.idrefsal = t4.id 
inner join tableRecap t5 on t5.id=t1.idrefTableRecap 
inner join tableEtablissement t6 on t6.id=t5.idreft6 
inner join tablePersonne t7 on ..................
...... 
.....
where t2.annee =2020 
and t6.numentreprise='123456'
AND t6.id = Ent.id
and t1.id = nomi.id
) as "nbheure 2020",
(
select round(t1.montant,2) as MonEntnt_2019
from table1 t1 
inner join EntbleAnnee t2 on t1.idrefEntnnee = t2.id 
inner join EntbleRelation  t3 on t1.referenceContrat = t3.id 
inner join tableEtablissement t4 on t3.idrefsal = t4.id 
inner join tableRecap t5 on t5.id=t1.idrefTableRecap 
inner join tableEtablissement t6 on t6.id=t5.idreft6 
inner join tablePersonne t7 on ..................
...... 
.....
where t2.annee =2020 
and t6.numentreprise='123456'
AND t6.id = Ent.id
and t1.id = nomi.id
) as "montant 2020"
FROM table1 nomi 
inner join EntbleAnnee c1 on nomi.idrefEntnnee = c1.id
inner join EntbleRelation trel on nomi.referenceContrat = trel.id
inner join tableEtablissement sal on sal.id = trel.idrefsal  
inner join tableEtablissement Ent  on Ent.id  = trel.idgrp_1 
inner join tablePersonne tpers on tpers.x530_idx510 = sal.idx510 
inner join tableRecap trec on trec.d020_idd010 = c1.id
where  ................
and ......................
and ..................
and c1.annee = 2020        
and Ent.numentreprise in ('123456') 
;

je sélectionne :
- nom et prénom,
- num contrat,
- nbheure et montant sur 2017,
- nbheure et montant sur 2018,
- nbheure et montant sur 2019,
- nbheure et montant sur 2020.

Mon bloc principal ayant pour condition l'année 2020, seul les données portant sur le nbheure et montant 2020 seront correct.
Sur 2018, par exemple, on aura un écart, écart que j'ai découvert car il manque des personnes, c'est logique puisqu'en 2018 on avait 50 personnes en moins.

J'ai testé d'enlevé la condition qui porte sur l'année ou encore mieux de faire : and c1.annee >= 2017 , mais mon fichier est inexploitable, car je le génère automatiquement via un batch et quand je clique sur une macro que j'ai créé, il me génère plusieurs feuille et au lieu d'avoir par exemple 300 personnes au total, il m'en trouve 900 et les totaux ne sont pas bon du tout :s

merci

**android59** · 25/02/2020, 09h18

Envoyé par vanagreg

En effet si tu es le DBA et que tu n'as même pas accès aux vues du dictionnaire de données c'est qu'il y a un problème. Tu ne peux pas te connecter en SYSTEM ou en SYS?

Effectivement, je fais beaucoup de base de données, et un peu de développement, mais on est limité sinon je pourrais faire plein de chose.
Encore les tables all_tables_columns par exemple je peux le voir mais si sqldeveloper plante, je peux même pas faire un kill alors qu'avant mon arrivé, ils pouvaient le faire.

Non je ne peux pas me connecter avec sys ou system lol

**vanagreg** · 25/02/2020, 10h29

Bonjour,

Comme je disais, il ne faut pas faire de requête scalaire pour chaque nombre d'heures et chaque montant, c'est certainement ça qui a explosé ton temp. Il faut d'abord tout aller chercher:

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
select t1.id nomi_id, t6.id ent_id, t1.nbheure as Nb_heures, round(t1.montant,2) as montant
 from table1 t1 
 inner join EntbleAnnee t2 on t1.idrefEntnnee = t2.id 
 inner join EntbleRelation  t3 on t1.referenceContrat = t3.id 
 inner join tableEtablissement t4 on t3.idrefsal = t4.id 
 inner join tableRecap t5 on t5.id=t1.idrefTableRecap 
 inner join tableEtablissement t6 on t6.id=t5.idreft6 
 inner join tablePersonne t7 on ..................
 ...... 
 .....
 where t2.annee >= 2017 
 and t6.numentreprise='123456'

Donc tu vois ici que je choisis les années 2017 à 2020 sur le numentreprise 123456. Dans la clause SELECT j'ai besoin de t1.id et t6.id pour faire la jointure avec les tables principales. Je récupère bien sûr le nombre d'heures et le montant.
Maintenant, au lieu d'appeler 8 fois cette requête (une pour chaque année et chaque valeur), tu la pivotes pour avoir les nombre d'heures et les montants par colonne. Il faut au moins Oracle 11g1 (au pire au peut faire un pivot à l'ancienne si version inférieure). Donc, si on pivote:

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
with nb_heures_et_montants as
(select t1.id nomi_id, t6.id ent_id, t1.nbheure as Nb_heures, round(t1.montant,2) as montant
 from table1 t1 
 inner join EntbleAnnee t2 on t1.idrefEntnnee = t2.id 
 inner join EntbleRelation  t3 on t1.referenceContrat = t3.id 
 inner join tableEtablissement t4 on t3.idrefsal = t4.id 
 inner join tableRecap t5 on t5.id=t1.idrefTableRecap 
 inner join tableEtablissement t6 on t6.id=t5.idreft6 
 inner join tablePersonne t7 on ..................
 ...... 
 .....
 where t2.annee >= 2017 
 and t6.numentreprise='123456'
 )
select nomi_id, ent_id, 
         a2017_nb_heures, a2018_nb_heures, a2019_nb_heures, a2020_nb_heures,
        a2017_montant, a2018_montant, a2019_montant, a2020_montant
 from 
 pivot (sum(nb_heures) nb_heures, sum(montant) montant for annee in (2017 as a2017, 2018 as a2018, 2019 as a2019, 2020 as a2020))

L'avantage c'est que tu n'auras plus à te soucier des personnes manquantes.
Maintenant il ne reste plus qu'à joindre ces données pivotées au données principales:

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
with nb_heures_et_montants as
(select t1.id nomi_id, t6.id ent_id, t1.nbheure as Nb_heures, round(t1.montant,2) as montant
 from table1 t1 
 inner join EntbleAnnee t2 on t1.idrefEntnnee = t2.id 
 inner join EntbleRelation  t3 on t1.referenceContrat = t3.id 
 inner join tableEtablissement t4 on t3.idrefsal = t4.id 
 inner join tableRecap t5 on t5.id=t1.idrefTableRecap 
 inner join tableEtablissement t6 on t6.id=t5.idreft6 
 inner join tablePersonne t7 on ..................
 ...... 
 .....
 where t2.annee >= 2017 
 and t6.numentreprise='123456'
 ),
 nb_heures_et_montants_pivot as
(select nomi_id, ent_id, 
        a2017_nb_heures, a2018_nb_heures, a2019_nb_heures, a2020_nb_heures,
        a2017_montant, a2018_montant, a2019_montant, a2020_montant
 from 
 pivot (sum(nb_heures) nb_heures, sum(montant) montant for annee in (2017 as a2017, 2018 as a2018, 2019 as a2019, 2020 as a2020))
)
select distinct 
sal.numerocontrat as "NUM.contrat",
tpers.nom as NOM,
tpers.prenom as PRENOM,
hm.a2017_nb_heures as "nbheure_2017",
hm.a2017_montant as "montant_2017",
hm.a2018_nb_heures as "nbheure_2018",
hm.a2018_montant as "montant_2018",
hm.a2019_nb_heures as "nbheure_2019",
hm.a2019_montant as "montant_2019",
hm.a2020_nb_heures as "nbheure_2020",
hm.a2020_montant as "montant_2020"
FROM table1 nomi 
inner join EntbleAnnee c1 on nomi.idrefEntnnee = c1.id
inner join EntbleRelation trel on nomi.referenceContrat = trel.id
inner join tableEtablissement sal on sal.id = trel.idrefsal  
inner join tableEtablissement Ent  on Ent.id  = trel.idgrp_1 
inner join tablePersonne tpers on tpers.x530_idx510 = sal.idx510 
inner join tableRecap trec on trec.d020_idd010 = c1.id
inner join nb_heures_et_montants_pivot hm on hm.nomi_id = Ent.id and hm.ent_id = Ent.id
where  ................
and ......................
and ..................
and c1.annee = 2020        
and Ent.numentreprise in ('123456') 
;

**android59** · 26/02/2020, 10h42

merci pour ton retour.

Je ne comprend pas pourquoi, quand tu fais :

Code :

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

1
2
3
4
5
6
7
8
 
 nb_heures_et_montants_pivot as
(select nomi_id, ent_id, 
        a2017_nb_heures, a2018_nb_heures, a2019_nb_heures, a2020_nb_heures,
        a2017_montant, a2018_montant, a2019_montant, a2020_montant
 from 
 pivot (sum(nb_heures) nb_heures, sum(montant) montant for annee in (2017 as a2017, 2018 as a2018, 2019 as a2019, 2020 as a2020))
)

lorsque je met sum(nb_heures) nb_heures, sum(montant) montant, ça me met une erreur mais quand je ne met qu'un champs ça marche, est ce normal?

Voici comment j'ai fais :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
 
pivot (
    sum(heure2017)--,
 
--,
--    sum(heure2018),
--    sum(montant2018)
--    ,
--    sum(heure2019),
--    sum(_montant2019),
--    sum(heure2020),
--    sum(montant2020)
 for annee in ('2017','2018','2019','2020')

si je laisse heure2017 ça marche, sinon si je rajoute les autres ça ne marche plus.

**vanagreg** · 26/02/2020, 11h05

Bonjour,

Je me rends compte qu'il manque l'année dans le premier bloc WITH, ainsi que le nom de la vue dans la clause FROM du pivot. C'est peut-être ça ton problème. Quand tu fais cette requête là, cela fonctionne-t-il:

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
with nb_heures_et_montants as
(select t1.id nomi_id, t6.id ent_id, t2.annee, t1.nbheure as Nb_heures, round(t1.montant,2) as montant
 from table1 t1 
 inner join EntbleAnnee t2 on t1.idrefEntnnee = t2.id 
 inner join EntbleRelation  t3 on t1.referenceContrat = t3.id 
 inner join tableEtablissement t4 on t3.idrefsal = t4.id 
 inner join tableRecap t5 on t5.id=t1.idrefTableRecap 
 inner join tableEtablissement t6 on t6.id=t5.idreft6 
 inner join tablePersonne t7 on ..................
 ...... 
 .....
 where t2.annee >= 2017 
 and t6.numentreprise='123456'
 )
select nomi_id, ent_id, 
         a2017_nb_heures, a2018_nb_heures, a2019_nb_heures, a2020_nb_heures,
        a2017_montant, a2018_montant, a2019_montant, a2020_montant
 from nb_heures_et_montants 
 pivot (sum(nb_heures) nb_heures, sum(montant) montant for annee in (2017 as a2017, 2018 as a2018, 2019 as a2019, 2020 as a2020))

Il ne faut pas faire les sommes sur les autres années, c'est justement le pivot qui s'en charge.