#Error in SELECT query with OUTER JOIN

[Guest]

Context:
Access 2003, MDB file

Setup:
I have a query defined (schematically) as "SubqueryA LEFT JOIN SubqueryB".
The query returns some rows from SubqueryA that have no match in SubqueryB.
My question is about the way SubqueryB fields are handled when there is no
match to a row in Subquery A. In that situation: (a) text and numeric and
date fields in SubqueryB appear as Null in the result, which is expected; but
(b) a calculated field in SubqueryB that returns a date (using a user-defined
function) appears as #Error rather than Null.

Reaction:
I am surprised by this and want to know why Access works this way. I would
have thought that, when a row in SubqueryA is unmatched in SubqueryB, Access
would not inspect the innards of SubqueryB at all, and so would never notice
that a user-defined function is invoked. Obviously that expectation is wrong.

Question:
Can I change this query, or SubqueryB, in such a way as to avoid the #Error
for unmatched rows? I assume I can work around this by converting SubqueryB
into a MakeTable query instead of a SELECT query, but that takes longer.
Also, I want there to be a more elegant solution.

Thanks,
Wesley

P.S. I think nothing is added by furnishing the (complicated) SQL. I hope
the point is clear.

 

[Allen Browne]

Depends on what is causing the #Error.

Some expressions do not handle null. For example, if your calculated field
is:
DateAdd("d", [HowManyDays], Date())
and HowManyDays is Null, DateAdd() will error, since this number must be a
Double and a Double cannot be a Null

But you may have struck a case where JET just fails to recognise the data
type of the calculated column, so any attempt to do further (date)math on
the column results in #Error just as if you had attempted date math on a
string. In this case, the solution could be to wrap the expression in
CVDate() so Access knows the data type.

For calculated fields that you want Access to recognise as numeric, there
are a couple of options. One is to add zero, e.g.:
[Field1] + [Field2] + 0
Of course the result is Null if either Field1 or Field2 is null, but the
numeric component is enough to tell JET to treat the value as a number.

Another alternative is:
IIf(True, Null, 0)
The expression always returns Null (since True is always true), but the
presence of the alternative indicates the data type.

 

[Allen Browne]

Glad you found the cause, Wesley, and were able to work around it.

Regarding the VBA function, you always need to declare the function
arguments as Variant if you pass fields. Since fields can have the Null or
Error values, only Variants can cope. Sure, it means a bit more checking
within the function, but it's the only safe way to write your code (as you
found.)

Optimizing queries is a bit of a hit'n'miss affair in Access. In general,
JET is pretty good at choosing a suitable plan, but getting it to do
something different is - well - an exercise in frustration.

Just in case you are not aware of how to read the JET query plan, here's
some leads:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnacbk02/html/odc_4009c15.asp
http://builder.com.com/5100-6388-5064388.html
http://msdn.microsoft.com/archive/d...MicrosoftAccessMicrosoftJetDatabaseEngine.asp

(Hope they still work: they're links I collected some time back.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Allen,

Thanks, mate. I hope you enjoyed the second Ashes test from Adelaide,
which
I watched via satellite last night. Amazing stuff. They are coming to you
in
Perth next, I gather.

I tried all of your suggested ideas already, and a couple of others,
before
posting my question. But your first comment inspired a new idea. I found I
can stop the behavior I describe in my note by changing the function so
that
all of its inputs are variants. Superficially this does not seem necessary
but the engine's behavior in this instance shows that it is. Apparently
Jet
does look at the function in SubqueryB even when there is no row in
SubqueryB
to match a selected row in SubqueryA. Jet tries to evaluate the function
by
plugging Nulls in to all of the function's inputs. Making the inputs
variants
and handling the nulls in the function apparently keeps Jet happy.

My reaction to all this is that Jet ought not be evaluating a function
when
the containing field sits in a non-existent row (i.e. there is no row in
SubqueryB to match the selected row in SubqueryA). It is inefficient and
unnecessarily slows down query processing. Why does not query optimization
avoid this behavior? I can't think of a context in which it makes sense.
Is
it just an oversight in Jet design?

Wesley

Depends on what is causing the #Error.

Some expressions do not handle null. For example, if your calculated
field
is:
DateAdd("d", [HowManyDays], Date())
and HowManyDays is Null, DateAdd() will error, since this number must be
a
Double and a Double cannot be a Null

But you may have struck a case where JET just fails to recognise the data
type of the calculated column, so any attempt to do further (date)math on
the column results in #Error just as if you had attempted date math on a
string. In this case, the solution could be to wrap the expression in
CVDate() so Access knows the data type.

For calculated fields that you want Access to recognise as numeric, there
are a couple of options. One is to add zero, e.g.:
[Field1] + [Field2] + 0
Of course the result is Null if either Field1 or Field2 is null, but the
numeric component is enough to tell JET to treat the value as a number.

Another alternative is:
IIf(True, Null, 0)
The expression always returns Null (since True is always true), but the
presence of the alternative indicates the data type.

message

Context:
Access 2003, MDB file

Setup:
I have a query defined (schematically) as "SubqueryA LEFT JOIN
SubqueryB".
The query returns some rows from SubqueryA that have no match in
SubqueryB.
My question is about the way SubqueryB fields are handled when there is
no
match to a row in Subquery A. In that situation: (a) text and numeric
and
date fields in SubqueryB appear as Null in the result, which is
expected;
but
(b) a calculated field in SubqueryB that returns a date (using a
user-defined
function) appears as #Error rather than Null.

Reaction:
I am surprised by this and want to know why Access works this way. I
would
have thought that, when a row in SubqueryA is unmatched in SubqueryB,
Access
would not inspect the innards of SubqueryB at all, and so would never
notice
that a user-defined function is invoked. Obviously that expectation is
wrong.

Question:
Can I change this query, or SubqueryB, in such a way as to avoid the
#Error
for unmatched rows? I assume I can work around this by converting
SubqueryB
into a MakeTable query instead of a SELECT query, but that takes
longer.
Also, I want there to be a more elegant solution.

Thanks,
Wesley

P.S. I think nothing is added by furnishing the (complicated) SQL. I
hope
the point is clear.

 

[Guest]

Thanks, Allen. I'll be more careful with variants in the future. Your links
are still fine, by the way, and I have seen them before, perhaps in your own
past posts. But reading the JET query plan doesn't help me to convince the
Jet engine to adopt a different plan when it makes a bad decision! Wesley