Problème de comptage sous Oracle 10g

[david71]

Bonjour,

J'ai une problématique pour écrire une requête.

Je donne un exemple.

J'ai une liste de rubriques comptables suivantes qui identifient un salarié : 1000,1176,...
J'ai une autre liste de rubriques comptables qui identifient un non salarié :
1110,1114,....


Jusque là tout va bien sauf qu'un actionnaire peut être présent dans les deux rubriques.
Exemple 1 : l'actionnaire 1 est présent dans 1000, 1176 et 1110 --> il doit être identifié comme SALARIE et non comme NON SALARIE.
Exemple 2 : l'actionnaire 2 est présent dans 1110 et 1114 et doit donc être identifié comme NON SALARIE.
Exemple 3 : l'actionnaire 3 est présent uniquement dans les rubriques 1000 et 1176, il doit donc être identifié comme SALARIE.

Ce qui me pose problème c'est le cas de l'exemple 1 quand l'actionnaire est présent dans les deux groupes....

Car sinon j'aurais fait 2 requêtes :

Code :

1
2
3
4
5
SELECT actionnaire, 'SALARIE' AS 'Statut', ...FROM matable WHERE
cpte_iden IN ('1000','1176',...)
UNION
SELECT actionnaire, 'NON SALARIE' AS 'Statut', ...FROM matable WHERE
cpte_iden IN ('1110','1114',...)

Voilà si vous avez des idées pour traiter le cas 1, je suis preneur.
Merci !

[ORA-007]

Bonjour,

Votre requête me semble bonne à part le UNION.

En effet le UNION dédoublonne alors que UNION ALL serait à mon avis plus adéquat dans votre cas de figure .

[david71]

OK merci, je vais essayer avec le UNION ALL mais que pensez-vous de cette requête ?

Code :

1
2
3
4
5
SELECT actionnaire, 'SALARIE' AS 'Statut', ...FROM matable WHERE
(cpte_iden IN ('1000','1176',...) OR cpte_iden IN ('1110','1114',...))
UNION
SELECT actionnaire, 'NON SALARIE' AS 'Statut', ...FROM matable WHERE
cpte_iden IN ('1110','1114',...)

[Jean.Cri1]

Bonjour,

Ta derniere requete renverra le bon statut salarié , mais aussi le statut non salarié pour ceux qui possedent les deux.

Code :

1
2
3
4
5
6
7
8
9
10
11
12
SELECT 
 actionnaire
,case when sum (case when cpte_iden IN ('1000','1176',les codes de type salarié) 
                               then 1 
                               else 0
                       end
                       ) > 0
        then "salarie"
        else "non salarié"
FROM matable
WHERE cpte_iden IN ( tous les codes type statut)
GROUP BY actionnaire

devrait renvoyer ce que tu cherches.
Si chaque actionnaire a au moins un cpte_iden connu , le where devient inutile.

[tatayo]

Bonjour,
Le UNION ne dédoublonnera rien ici, car la valeur d'une des colonnes du résultat change d'une requête à l'autre.
Est-ce que tu peux nous donnée le schéma de tes tables, ainsi qu'un jeu de test ?

Code SQL :

1
2
3
4
5
6
 
SELECT actionnaire, 'SALARIE' AS 'Statut', ...FROM matable WHERE
(cpte_iden IN ('1000','1176',...) OR cpte_iden IN ('1110','1114',...))
UNION
SELECT actionnaire, 'NON SALARIE' AS 'Statut', ...FROM matable WHERE
cpte_iden IN ('1110','1114',...)

est équivalent à :

Code SQL :

1
2
3
4
5
6
 
SELECT actionnaire, 'SALARIE' AS 'Statut', ...FROM matable WHERE
(cpte_iden IN ('1000','1176','1110','1114',...))
UNION
SELECT actionnaire, 'NON SALARIE' AS 'Statut', ...FROM matable WHERE
cpte_iden IN ('1110','1114',...)

D'après ta demande:
les non salariés sont ceux présent dans la liste des non salariés, et absents de l'autre liste.
les salariés sont ceux présent dans la première liste

Est-ce que j'ai bon ?

Tatayo.

[ORA-007]

Envoyé par david71

OK merci, je vais essayer avec le UNION ALL mais que pensez-vous de cette requête ?

Code :

Et bien la premiere partie de la requete va identifier tout le monde comme étant salarié :

Donne moi les actionnaires qui sont dans le registre des salariés ou des non salariés = tout le monde non ?

[david71]

Ca ne marche pas, cela ramène 4 lignes comme ça.
Il faut peut-être que j'enlève le OR dans la première requête, j'essaie de nouveau.

Je parle de ma requête en tout cas.
Attendez, merci pour votre aide je vais regarder vos requêtes pour voir ce que cela donne.

[ORA-007]

Envoyé par david71

Ca ne marche pas, cela ramène 4 lignes comme ça.
Il faut peut-être que j'enlève le OR dans la première requête, j'essaie de nouveau.

Je parle de ma requête en tout cas.
Attendez, merci pour votre aide je vais regarder vos requêtes pour voir ce que cela donne.

Donne nous un jeu de test complet, ce que tu obtiens actuellement et ce que tu voudrais obtenir.

[skuatamad]

Je pense à quelque chose comme ça (avec id_salarie la PK de la table):

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
WITH salarie AS (
SELECT id_salarie
  FROM matable 
 WHERE cpte_iden IN ('1000','1176',...)
 GROUP BY id_salarie
HAVING count(*) = /*nombre de valeur dans le IN*/
)
SELECT actionnaire, 'salarie' AS statut, ...
  FROM matable
 WHERE id_salarie IN (SELECT id_salarie FROM salarie)
 UNION ALL
SELECT actionnaire, 'NON SALARIE' AS Statut, ...
  FROM matable 
 WHERE cpte_iden NOT IN (SELECT id_salarie FROM salarie)

Si la liste des salariés n'est pas écrite en dur regarde La division relationnelle pour générer la CTE (le WITH).

[Jean.Cri1]

Code :

1
2
3
4
5
6
7
8
9
10
11
SELECT DISTINCT
 actionnaire
,case when EXISTS (SELECT 1 FROM matable T2 
                                       WHERE T2.actionnaire = maTable.actionnaire
                                       AND    T2.cpte_iden IN (codes salarié)
                          )
          then 'salarié'
          else ' non salarié'
 end
FROM matable
WHERE cpte_iden IN ( les codes statuts possibles )

ou le where peut a nouveau etre facultatif.
Ma premiere requete devrait aussi marcher

[david71]

Bon merci pour vos idées.
J'ai essayé de reprendre la première requête de Jean.cri1 qui semblait convenir (avec le SUM et le CASE WHEN, d'ailleurs il manquait un END je pense) mais ça ne marche pas, problème de GROUP BY :

Ci-dessous la requête complète donc :

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
 
SELECT DISTINCT 'SOCIETE', e.emet_iden ||'-'|| a.acti_iden||'-'||t.titu_nume, '',
'I', 
t.titu_nom||','||t.titu_pren,
'',
'',
t.adre_info_rue, 
t.adre_nom_comu,
p1.pays_libe,
p2.pays_libe,
'',
t.titu_date_nais, 
p3.pays_libe || ' - ' || t.titu_comu_nais,
'', 
e.emet_iden,
'',
e.emet_libe,
'', 
'Tiers',
'', 
case when sum(case when a.cpte_iden IN ('1000','1001','1002','1003','1004','1005','1006','1007','1008','1009','1142','1143','1144','1146','1160',
'1161','1163','1165','1166','1168','1176','1177','1196','1197','1199','1499','1510','1520','1535','1540','1544','1549','1565','1576',
'1577','1589','1599','1504','1147','1167','1186','1598','1514','1516','1533') 
                               then 1 
                               else 0
                       end
                       ) > 0
        then 'salarie'
        else 'non salarié'
end, 
'',
'',
case t.titu_qual
when 'MME' then 'F'
when 'MLLE' then 'F'
when 'MR' then 'M'
when 'MR OU MME' then 'M'
when 'MR ET MME' then 'M'
when 'AUTRE' then ''
when 'CORRESP' then ''
when 'SOCIETE' then ''
end,
case t.titu_resi_fisc 
when 'FRANCE' THEN 'R'
else 'N'
end
FROM soaa a,emet e,titu t,vale v, pays p1, pays p2, pays p3
WHERE a.soaa_date=(SELECT max(soaa_date) FROM soaa b WHERE
b.ADMI_IDEN=a.ADMI_IDEN AND
b.CREF_IDEN=a.CREF_IDEN AND
b.VALE_IDEN=a.VALE_IDEN AND
b.CPTE_IDEN=a.CPTE_IDEN AND
b.TYCP_IDEN=a.TYCP_IDEN AND
b.EMET_IDEN=a.EMET_IDEN AND
b.ACTI_IDEN=a.ACTI_IDEN AND
-- pour tester sur deux actionnaires différents (184 est salarié) et 1839 non
e.emet_iden = 63739 AND
(a.ACTI_IDEN = 184 OR a.ACTI_IDEN = 1839) AND 
--
b.SOAA_SENS=a.SOAA_SENS AND trunc(b.soaa_date)<=trunc(sysdate))
AND a.cref_iden=1
-- on prend tous les comptes
AND a.cpte_iden IN ('1000','1001','1002','1003','1004','1005','1006','1007','1008','1009','1142','1143','1144','1146','1160',
'1161','1163','1165','1166','1168','1176','1177','1196','1197','1199','1499','1510','1520','1535','1540','1544','1549','1565','1576',
'1577','1589','1599','1504','1147','1167','1186','1598','1514','1516','1533','1101','1103','1104','1110','1113','1114','1115','1116',
'1117','1118','1119','1120','1125','1126','1130','1133','1135','1136',
'1140','1141','1145','1149','1150','1154','1155','1156','1170','1171','1172','1173','1174','1175','1179','1185','1310','1311','1312','1500','1600')
AND (a.soaa_quan <> 0 OR ( a.soaa_quan=0 AND 
		           a.soaa_date >= add_months(to_date('01/' || to_char(sysdate,'mm/yyyy'),'dd/mm/yyyy'),-1)))
AND e.emet_iden=a.emet_iden
AND e.emet_indi_actf='O'
AND t.emet_iden=a.emet_iden
AND t.acti_iden=a.acti_iden
AND v.vale_iden=a.vale_iden
AND v.vale_indi_actf='O'
AND t.titu_natu_juri = 'PP'
AND t.adre_pays_iden  = p1.pays_iden (+)
AND t.adre_fisc_pays_iden  = p2.pays_iden (+)
AND t.pays_nais_iden  = p3.pays_iden (+);

[david71]

Je travaille sur la requête WHEN EXISTS donc qui me semble plus pertinente.

Bon je donne une exemple donc :

l'actionnaire 1 se retrouve sur les rubriques comptables suivantes :

La requête :

Code :

1
2
3
4
 
SELECT s.acti_iden, s.cpte_iden FROM soaa s
WHERE s.emet_iden = 63739
AND (s.ACTI_IDEN = 1)

Donne :

1 1110
1 1110
1 1410
1 1410
.....

Cet actionnaire N°1 doit être considéré comme "NON SALARIE".

Sur l'actionnaire N°2 la requête
select s.acti_iden, s.cpte_iden from soaa s
where s.emet_iden = 63739
and (s.ACTI_IDEN = 2)


2 1000
2 1000
2 1110
2 1110
2 1176
2 1176
2 1176
.....

Cet actionnaire même s'il est référencé sur un compte non salarié (1110) doit quand même être considéré comme salarié...

[punkoff]

bonjour,

d'où viennent les données de la colonne cpte_iden ?
Sont-elles référencées dans une autre table (foreign key) ?

[david71]

De la table SOAA.
Il n'y a pas de foreign key hélas. Pas à ma connaissance.

[punkoff]

Mais comment savez-vous que telle rubrique comptable doit du coup définir le type "salarié" ou "non salarié" ?

Un mail, xls, ..... une référence dans une table ?

[david71]

Je me base par rapport à une liste de valeurs communiquée.

Bon j'ai une requête intermédiaire qui ramène :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
 
 
SELECT act,
case when 
compte IN ('1000','1001','1002','1003','1004','1005','1006','1007','1008','1009','1142','1143','1144','1146','1160',
'1161','1163','1165','1166','1168','1176','1177','1196','1197','1199','1499','1510','1520','1535','1540','1544','1549','1565','1576',
'1577','1589','1599','1504','1147','1167','1186','1598','1514','1516','1533')
          then 'salarié'
          else 'non salarié' end
		   FROM(
SELECT DISTINCT s.acti_iden AS act, s.cpte_iden AS compte FROM soaa s
WHERE s.emet_iden = 63739
AND (s.ACTI_IDEN = 184 OR s.ACTI_IDEN = 1839) ORDER BY s.acti_iden)

Cela donne :

184 salarié
184 salarié
184 non salarié
184 non salarié
184 salarié
184 salarié
1839 non salarié
1839 non salarié

Au final il faut avoir :
184 salarié
1839 non salarié

Donc tout actionnaire étant à la fois salarié et non salarié est salarié.
Tout actionnaire salarié est salarié.
Tou actionnaire non salarié est salarié.

Voilà donc il faudrait être capable de mettre en place cette règle de gestion en SQL.

[punkoff]

Dans ce cas ... c'est moche.

Réalisez une vue à base d'union all pour gérer la liste de vos rubriques.

ca vous permettra d'avoir une solution maintenable faicllement ...

Code :

1
2
3
4
 
CREATE VIEW v_liste_salarie (cpt_sal) AS (
SELECT '1000' FROM dual union ALL
SELECT '1001' FROM dual union ALL ...

Maintenant que vous avez une stucture propre :

Code :

1
2
3
4
5
6
7
 
SELECT DISTINCT s.acti_iden AS act, s.cpte_iden AS compte, 
case when v.cpt_sal IS NULL then 'non salarie' else 'salarie' end 
FROM soaa s
LEFT OUTER JOIN v_liste_salarie v ON s.cpte_iden = v.cpt_sal
WHERE s.emet_iden = 63739
AND (s.ACTI_IDEN = 184 OR s.ACTI_IDEN = 1839)

le distinct est-il vraiment util ?


edit: ah !! mais je viens de comprendre que vous avez 1 ligne par cpte_iden !!

c'est une modélisation qui ne va pas du tout !

[david71]

Oui l'idée de la vue est intéressante mais il faut que je vois si je peux avoir les droits pour la créer.

Sinon j'ai une solution temporaire avec 2 sous-requêtes qui prend le max du statut 'salarié'/'non salarié' pour exclure le statut 'non salarié'.
Il faut maintenant que j'essaye d'intégrer cette requête dans la requête principale pour voir les performances.

En effet, le modèle de données est mal conçu mais il faut tout de même faire avec hélas.

[david71]

Bon merci punkoff pour l'idée cela renvoie donc le résultat désiré.
Ci-dessous la requête finale :

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
 
SELECT DISTINCT 'SOCIETE', e.emet_iden ||'-'|| a.acti_iden||'-'||t.titu_nume, '',
'I', 
t.titu_nom||','||t.titu_pren,
'',
'',
t.adre_info_rue, 
t.adre_nom_comu,
p1.pays_libe,
p2.pays_libe,
'',
t.titu_date_nais, 
p3.pays_libe || ' - ' || t.titu_comu_nais,
'', 
e.emet_iden,
'',
e.emet_libe,
'', 
'Tiers',
max(case when v.compte IS NULL then 'non salarie' else 'salarie' end), 
'',
'',
case t.titu_qual
when 'MME' then 'F'
when 'MLLE' then 'F'
when 'MR' then 'M'
when 'MR OU MME' then 'M'
when 'MR ET MME' then 'M'
when 'AUTRE' then ''
when 'CORRESP' then ''
when 'SOCIETE' then ''
end,
case t.titu_resi_fisc 
when 'FRANCE' THEN 'R'
else 'N'
end
FROM soaa a,emet e,titu t,vale v, pays p1, pays p2, pays p3, v_fff_salarie v
WHERE a.soaa_date=(SELECT max(soaa_date) FROM soaa b WHERE
b.ADMI_IDEN=a.ADMI_IDEN AND
b.CREF_IDEN=a.CREF_IDEN AND
b.VALE_IDEN=a.VALE_IDEN AND
b.CPTE_IDEN=a.CPTE_IDEN AND
b.TYCP_IDEN=a.TYCP_IDEN AND
b.EMET_IDEN=a.EMET_IDEN AND
b.ACTI_IDEN=a.ACTI_IDEN AND
--
e.emet_iden = 63739 AND
(a.ACTI_IDEN = 184 OR a.ACTI_IDEN = 1839 OR a.ACTI_IDEN = 2438) AND 
--
b.SOAA_SENS=a.SOAA_SENS AND trunc(b.soaa_date)<=trunc(sysdate))
AND a.cref_iden=1
AND a.cpte_iden IN ('1000','1001','1002','1003','1004','1005','1006','1007','1008','1009','1142','1143','1144','1146','1160',
'1161','1163','1165','1166','1168','1176','1177','1196','1197','1199','1499','1510','1520','1535','1540','1544','1549','1565','1576',
'1577','1589','1599','1504','1147','1167','1186','1598','1514','1516','1533','1101','1103','1104','1110','1113','1114','1115','1116',
'1117','1118','1119','1120','1125','1126','1130','1133','1135','1136',
'1140','1141','1145','1149','1150','1154','1155','1156','1170','1171','1172','1173','1174','1175','1179','1185','1310','1311','1312','1500','1600')
AND (a.soaa_quan <> 0 OR ( a.soaa_quan=0 AND 
		           a.soaa_date >= add_months(to_date('01/' || to_char(sysdate,'mm/yyyy'),'dd/mm/yyyy'),-1)))
AND e.emet_iden=a.emet_iden
AND e.emet_indi_actf='O'
AND t.emet_iden=a.emet_iden
AND t.acti_iden=a.acti_iden
AND v.vale_iden=a.vale_iden
AND v.vale_indi_actf='O'
AND t.titu_natu_juri = 'PP'
AND t.adre_pays_iden  = p1.pays_iden (+)
AND t.adre_fisc_pays_iden  = p2.pays_iden (+)
AND t.pays_nais_iden  = p3.pays_iden (+)
AND  a.cpte_iden = v.compte (+)
GROUP BY e.emet_iden,a.acti_iden,t.titu_nume,t.titu_nom,t.titu_pren,t.adre_info_rue, 
t.adre_nom_comu,p1.pays_libe,p2.pays_libe,t.titu_date_nais,p3.pays_libe,t.titu_comu_nais,
e.emet_iden,e.emet_libe,t.titu_qual,t.titu_resi_fisc;

Par contre dans le SELECT concernant :

Code :

1
2
 
max(case when v.compte IS NULL then 'non salarie' else 'salarie' end)

Il y a en fait un autre cas : si ce n'est pas 'salarie' ni 'non salarie', il est possible que ce soit 'opc'.
Dans ce cas il faut interroger une autre table. Peut-on penser faire ainsi ?

Code :

1
2
3
4
5
6
7
8
9
10
11
12
 
max(case when v.compte IS NULL then 'non salarie' else 
(case v.vale_natu 
when 'FCP' then 'OPC'
when 'FCPI' then 'OPC'
when 'FCPR' then 'OPC'
when 'OPCVM' then 'OPC'
when 'SICAV' then 'OPC'
when 'SPPICAV' then 'OPC'
else 'salarie'
end)
end)

Voilà merci pour votre retour.

[Waldar]

Je ne suis pas rentré dans votre problématique, mais j'aime toujours ce genre de formule :

Code :

a.soaa_date >= add_months(to_date('01/' || to_char(sysdate,'mm/yyyy'),'dd/mm/yyyy'),-1))

Que signifie donc tout le charabia de droite :

Code :

1
2
3
4
5
6
SELECT add_months(to_date('01/' || to_char(sysdate,'mm/yyyy'),'dd/mm/yyyy'),-1) AS c1
  FROM dual;
 
C1
----------
01/09/2011

Plus court en évitant les conversions :

Code :

1
2
3
4
5
6
SELECT add_months(trunc(sysdate, 'mm'),-1) AS c2
  FROM dual;
 
C2
----------
01/09/2011

De même, l'expression :

Code :

trunc(b.soaa_date) <= trunc(sysdate)

Est équivalente à :

Code :

b.soaa_date < trunc(sysdate) + 1

Sauf que cette dernière profitera d'un index sur soaa_date s'il existe, et évite de convertir la première colonne - même si c'est un calcul rapide - c'est toujours un calcul inutile.
Évidemment si vous avez un index sur l'expression trunc(b.soaa_date), cette dernière remarque a un peu moins d'intérêt.