M
Mathieu Pagé
Hi,
I have a problem with a query that is returning me a "Divide by Zero"
error when it should not (Talked about this in an other thread). I have
found what is the problem, however I don't know how to correct it since,
I think, it's caused by a bug in Access (I should not say that
.
I'll try to simplify the problems.
I have these queries :
qryA : SELECT a, b, c FROM tableA WHERE d IS NOT NULL; // a, b and c are
never equal to zero
qryB : SELECT e, f FROM tableB WHERE g = 1; // f is never equal to zero
when g = 1, so this query should only return rows with "f <> 0".
qryC : SELECT a, b, IIF(a < b; a / b; b/ a) as CalculatedField FROM qryA
INNER JOIN qryB ON qryA.a = qryB.e WHERE IIF(a < b; a / b; b/ a) > 0;
Now, what I expect is that Access execute qryA and qryB then qryC.
However, I asked Access to output its plan in a text file and what I get
is that the condition in qryB and qryC are evaluated at the same time as
if they were in a single query.
So two things may explain the error I get.
1) Even if "g = 1" evaluate to false, "IIF(a < b; a / b; b/ a) > 0" is
evaluated, causing a division by zero.
2) "IIF(a < b; a / b; b/ a) > 0" is evaluated first, before "g = 1".
In each case it should not happen, because the first condition was put
there to ensure the second can be evaluated correctly.
Does anyone have a hint about what is going on?
Here is the Access execution plan, the condition are evaluated in the
step '04)':
<code>
- Inputs to Query -
Table 'tblAffectations'
Using index 'tblTaches_tblAffectations_FK1'
Having Indexes:
tblTaches_tblAffectations_FK1 1313 entries, 4 pages, 9 values
which has 1 column, fixed
tblEmployes_tblAffectations_FK1 1313 entries, 3 pages, 27 values
which has 1 column, fixed
tblDetailtblAffectations 1313 entries, 4 pages, 597 values
which has 1 column, fixed
tblAffectations_PK 1313 entries, 4 pages, 1313 values
which has 1 column, fixed, unique, clustered and/or counter,
primary-key, no-nulls
Table 'tblTaches'
Table 'tblDetailPreDefinit'
Using index 'tblDetailPreDefinit_PK'
Having Indexes:
tblDetailPreDefinit_PK 647 entries, 3 pages, 647 values
which has 1 column, fixed, unique, primary-key, no-nulls
Table 'tblDetailPreDefinitLinguistique'
Using index 'tblDetailPreDefinitNonLinguistique1_PK'
Having Indexes:
tblDetailPreDefinitNonLinguistique1_PK 647 entries, 3 pages, 647 values
which has 1 column, fixed, unique, primary-key, no-nulls
- End inputs to Query -
01) Restrict rows of table tblTaches
by scanning
testing expression "Not tblTaches.CodeService Is Null"
02) Inner Join result of '01)' to table 'tblAffectations'
using index 'tblAffectations!tblTaches_tblAffectations_FK1'
join expression "tblTaches.CodeTache=tblAffectations.CodeTache"
03) Group result of '02)'
04) Inner Join result of '03)' to table 'tblDetailPreDefinit'
using index 'tblDetailPreDefinit!tblDetailPreDefinit_PK'
join expression
"reqNbMotsAffectesParDetailParService.CodeDetail=reqNbMotsParDetailParService.CodeDetail"
then test expression "tblDetailPreDefinit.CodeUnite=1 And
IIf([SommeDeNbMots]>[Quantite],([SommeDeNbMots]-[Quantite])/[Quantite],([Quantite]-[SommeDeNbMots])/[SommeDeNbMots])>0"
05) Inner Join result of '04)' to table 'tblDetailPreDefinitLinguistique'
using index
'tblDetailPreDefinitLinguistique!tblDetailPreDefinitNonLinguistique1_PK'
join expression
"tblDetailPreDefinit.CodeDetail=tblDetailPreDefinitLinguistique.CodeDetail"
then test expression
"reqNbMotsAffectesParDetailParService.CodeService=reqNbMotsParDetailParService.CodeService"
</code>
I have a problem with a query that is returning me a "Divide by Zero"
error when it should not (Talked about this in an other thread). I have
found what is the problem, however I don't know how to correct it since,
I think, it's caused by a bug in Access (I should not say that
.I'll try to simplify the problems.
I have these queries :
qryA : SELECT a, b, c FROM tableA WHERE d IS NOT NULL; // a, b and c are
never equal to zero
qryB : SELECT e, f FROM tableB WHERE g = 1; // f is never equal to zero
when g = 1, so this query should only return rows with "f <> 0".
qryC : SELECT a, b, IIF(a < b; a / b; b/ a) as CalculatedField FROM qryA
INNER JOIN qryB ON qryA.a = qryB.e WHERE IIF(a < b; a / b; b/ a) > 0;
Now, what I expect is that Access execute qryA and qryB then qryC.
However, I asked Access to output its plan in a text file and what I get
is that the condition in qryB and qryC are evaluated at the same time as
if they were in a single query.
So two things may explain the error I get.
1) Even if "g = 1" evaluate to false, "IIF(a < b; a / b; b/ a) > 0" is
evaluated, causing a division by zero.
2) "IIF(a < b; a / b; b/ a) > 0" is evaluated first, before "g = 1".
In each case it should not happen, because the first condition was put
there to ensure the second can be evaluated correctly.
Does anyone have a hint about what is going on?
Here is the Access execution plan, the condition are evaluated in the
step '04)':
<code>
- Inputs to Query -
Table 'tblAffectations'
Using index 'tblTaches_tblAffectations_FK1'
Having Indexes:
tblTaches_tblAffectations_FK1 1313 entries, 4 pages, 9 values
which has 1 column, fixed
tblEmployes_tblAffectations_FK1 1313 entries, 3 pages, 27 values
which has 1 column, fixed
tblDetailtblAffectations 1313 entries, 4 pages, 597 values
which has 1 column, fixed
tblAffectations_PK 1313 entries, 4 pages, 1313 values
which has 1 column, fixed, unique, clustered and/or counter,
primary-key, no-nulls
Table 'tblTaches'
Table 'tblDetailPreDefinit'
Using index 'tblDetailPreDefinit_PK'
Having Indexes:
tblDetailPreDefinit_PK 647 entries, 3 pages, 647 values
which has 1 column, fixed, unique, primary-key, no-nulls
Table 'tblDetailPreDefinitLinguistique'
Using index 'tblDetailPreDefinitNonLinguistique1_PK'
Having Indexes:
tblDetailPreDefinitNonLinguistique1_PK 647 entries, 3 pages, 647 values
which has 1 column, fixed, unique, primary-key, no-nulls
- End inputs to Query -
01) Restrict rows of table tblTaches
by scanning
testing expression "Not tblTaches.CodeService Is Null"
02) Inner Join result of '01)' to table 'tblAffectations'
using index 'tblAffectations!tblTaches_tblAffectations_FK1'
join expression "tblTaches.CodeTache=tblAffectations.CodeTache"
03) Group result of '02)'
04) Inner Join result of '03)' to table 'tblDetailPreDefinit'
using index 'tblDetailPreDefinit!tblDetailPreDefinit_PK'
join expression
"reqNbMotsAffectesParDetailParService.CodeDetail=reqNbMotsParDetailParService.CodeDetail"
then test expression "tblDetailPreDefinit.CodeUnite=1 And
IIf([SommeDeNbMots]>[Quantite],([SommeDeNbMots]-[Quantite])/[Quantite],([Quantite]-[SommeDeNbMots])/[SommeDeNbMots])>0"
05) Inner Join result of '04)' to table 'tblDetailPreDefinitLinguistique'
using index
'tblDetailPreDefinitLinguistique!tblDetailPreDefinitNonLinguistique1_PK'
join expression
"tblDetailPreDefinit.CodeDetail=tblDetailPreDefinitLinguistique.CodeDetail"
then test expression
"reqNbMotsAffectesParDetailParService.CodeService=reqNbMotsParDetailParService.CodeService"
</code>