Discussion :

[sdurandeau]

Bonjour a tous,

Je viens de recuperer la gestion d'une DB, et j'ai un probleme de division par zero.
Cette requete vient fin d'un enseble de 7 requetes qui permet de creer une table dans Excel.

Je n'ai donc pas ecrit cette requete, et mon niveau reste basic.
J'ai checke es requetes liees (FROM), afin de verifier si il y avait effectivement une possible division par zero, mais rien, juste des additions, et des JOIN pour gonfler les resultats.

Voici la requete:

Code sql :

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
SELECT Core.DateSK, Core.Date, Core.Admin, Core.Name, Core.SurName, Core.Role, Core.Team, 
   SUM(iif(Core.Task = 'ActualBonus',Core.Count,0)) AS ActualBonus, 
   SUM(iif(Core.Task = 'AdjustmentsCredit',Core.Count,0)) AS AdjustmentsCredit, 
   SUM(iif(Core.Task = 'AdjustmentsDebit',Core.Count,0)) AS AdjustmentsDebit, 
   SUM(iif(Core.Task = 'WithdrawalsValidated',Core.Count,0)) AS WithdrawalsValidated, 
   SUM(iif(Core.Task = 'WithdrawalsValidated_FirstSafe',Core.Count,0)) AS WithdrawalsValidatedFirstSafe, 
   SUM(iif(Core.Task = 'WithdrawalsValidated_Safe',Core.Count,0)) AS WithdrawalsValidatedSafe,
   SUM(iif(Core.Task = 'WithdrawalsVoided',Core.Count,0)) AS WithdrawalsVoided, 
   SUM(iif(Core.Task = 'WithdrawalsVoided_FirstSafe',Core.Count,0)) AS WithdrawalsVoidedFirstSafe,
   SUM(iif(Core.Task = 'WithdrawalsVoided_Safe',Core.Count,0)) AS WithdrawalsVoidedSafe, 
   SUM(iif(Core.Task = 'Deposit_Control',Core.Count,0)) AS DepositControl, 
   SUM(iif(Core.Task = 'Registration_Control',Core.Count,0)) AS RegistrationControl, 
   SUM(iif(Core.Task = 'Chargebacks',Core.Count,0)) AS Chargebacks, 
   SUM(iif(Core.Task = 'Chats',Core.Count,0)) AS Chats, 
   SUM(iif(Core.Task = 'ClosePerm',Core.Count,0)) AS ClosePerm, 
   SUM(iif(Core.Task = 'CloseTemp',Core.Count,0)) AS CloseTemp, 
   SUM(iif(Core.Task = 'Documents',Core.Count,0)) AS Documents, 
   SUM(iif(Core.Task = 'Emails',Core.Count,0)) AS Emails, 
   SUM(IIF(Core.Task='Affiliate_Emails',Core.count,0)) AS Affiliate_Emails, 
   SUM(iif(Core.Task = 'EmailsOut',Core.Count,0)) AS EmailsOut, 
   SUM(iif(Core.Task = 'FraudAlerts',Core.Count,0)) AS FraudAlerts, 
   SUM(iif(Core.Task = 'ImpliedBonus',Core.Count,0)) AS ImpliedBonus, 
   SUM(iif(Core.Task = 'Iovation',Core.Count,0)) AS Iovation, 
   SUM(iif(Core.Task = 'OpenPerm',Core.Count,0)) AS OpenPerm, 
   SUM(iif(Core.Task = 'OpenTemp',Core.Count,0)) AS OpenTemp, 
   SUM(iif(Core.Task = 'PreAuthsValidated',Core.Count,0)) AS PreAuthsValidated, 
   SUM(iif(Core.Task = 'PreAuthsVoided',Core.Count,0)) AS PreAuthsVoided, 
   SUM(iif(Core.Task = 'Remarks',Core.Count,0)) AS Remarks, 
   SUM(iif(Core.Task = 'Reviews',Core.Count,0)) AS Reviews, 
   SUM(iif(Core.Task = 'SqlQueries',Core.Count,0)) AS SqlQueries, 
   SUM(iif(Core.Task = 'Test_Accounts',Core.Count,0)) AS TestAccounts, 
   SUM(iif(Core.Task = 'PEP Checks',Core.Count,0)) AS PEPChecks, 
   SUM(iif(Core.Task = 'Duty_of_Care',Core.Count,0)) AS DutyOfCare, 
   SUM(iif(Core.Task = 'EAV_Checks',Core.Count,0)) AS EAVChecks, 
   SUM(iif(Core.Task = 'Police_Requests',Core.Count,0)) AS PoliceRequests, 
   SUM(iif(Core.Task = 'EDD_Reviews',Core.Count,0)) AS EDDReviews, 
   SUM(iif(Core.Task = 'CHB_Representments',Core.Count,0)) AS CHBRepresentments, 
   iif(s.DayOff = 'No',480-s.Meeting-s.Training-s.Other-s.Sickness+s.Overtime,0-s.Meeting-s.Training-s.Other-s.Sickness+s.Overtime) AS ProdMin,
   SUM(Core.Tasks) AS Tasks, 
   SUM(Core.Tasks)/iif(s.DayOff = 'No',480-s.Meeting-s.Training-s.Other-s.Sickness+s.Overtime,0-s.Meeting-s.Training-s.Other-s.Sickness+s.Overtime)*480 AS TasksDay, 
   SUM(iif(Core.Task = 'ActualBonus',Core.AmountEur,0)) AS ActualBonusAmountEur, 
   SUM(iif(Core.Task = 'AdjustmentsCredit',Core.AmountEur,0)) AS AdjustmentsCreditAmountEur, 
   SUM(iif(Core.Task = 'AdjustmentsDebit',Core.AmountEur,0)) AS AdjustmentsDebitAmountEur, 
   SUM(iif(Core.Task = 'WithdrawalsValidated',Core.AmountEur,0)) AS WithdrawalsValidatedAmountEur, 
   SUM(iif(Core.Task = 'WithdrawalsVoided',Core.AmountEur,0)) AS WithdrawalsVoidedAmountEur, 
   SUM(iif(Core.Task = 'Chargebacks',Core.AmountEur,0)) AS ChargebacksAmountEur, 
   SUM(iif(Core.Task = 'ImpliedBonus',Core.AmountEur,0)) AS ImpliedBonusAmountEur, 
   SUM(iif(Core.Task = 'ReopenedTrappedFunds',Core.AmountEur,0)) AS ReopenedTrappedFundsEur, 
   SUM(iif(Core.Task = 'ClosedTrappedFunds',Core.AmountEur,0)) AS ClosedTrappedFundsEur, 
   SUM(iif(Core.Task = 'ReopenedTrappedFunds',Core.Count,0)) AS ReopenedTrappedFunds, 
   SUM(iif(Core.Task = 'ClosedTrappedFunds',Core.Count,0)) AS ClosedTrappedFunds, 
   iif(SUM(iif(Core.Task = 'ClosedTrappedFunds',Core.Count,0))+SUM(iif(Core.Task = 'ReopenedTrappedFunds',Core.Count,0))>0,SUM(iif(Core.Task = 'ReopenedTrappedFunds',Core.Count,0))/(SUM(iif(Core.Task = 'ClosedTrappedFunds',Core.Count,0))+SUM(iif(Core.Task = 'ReopenedTrappedFunds',Core.Count,0))),0) AS ReopenRate
FROM 3QryWeighedTasks AS Core INNER JOIN [\\10.56.60.104\......\Prod Shrinkage.accdb].TblShrinkage AS S 
ON (Core.Admin = s.Admin) AND (Core.DateSK = s.DateSK)
GROUP BY Core.DateSK, Core.Date, Core.Admin, Core.Name, Core.SurName, Core.Role, Core.Team, iif(s.DayOff = 'No',480-s.Meeting-s.Training-s.Other-s.Sickness+s.Overtime,0-s.Meeting-s.Training-s.Other-s.Sickness+s.Overtime);

Si quelqu'un a une idee?

Merci d'avance

Seb

[micniv]

Bonjour,

Les erreurs de divisions par 0 se produisent principalement là où il y des divisions
Je vois dans 2 colonnes de ta requete :

Code :

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

1
2
3
SUM(Core.Tasks)/iif(s.DayOff = 'No',480-s.Meeting-s.Training-s.Other-s.Sickness+s.Overtime,0-s.Meeting-s.Training-s.Other-s.Sickness+s.Overtime)*480 AS TasksDay
 
SUM(iif(Core.Task = 'ReopenedTrappedFunds',Core.Count,0))/(SUM(iif(Core.Task = 'ClosedTrappedFunds',Core.Count,0))+SUM(iif(Core.Task = 'ReopenedTrappedFunds',Core.Count,0))),0) AS ReopenRate

il faut surement blinder les diviseurs pour éviter tout 0

CDLT