Bug? With Access ODBC to both SQL and FoxPro - Select query error - Index does not accept NULL (1 Viewer)

[InstructionWhich7142]

I have a select query, it has 10 tables in, some are local, some are linked to FoxPro DBF via ODBC, some with inner joins, some with right, I'm expecting null values in some fields, however I get

ODBC--call failed.

[Microsoft][ODBC Visual FoxPro Driver]Index does not accept NULL. (#886)

I had a very similar issue with the SQL ODBC driver but I managed to make enough extra totals tables etc to work around that but it was annoying,

I'm on access365 but 2010 gives the same errors

Any tips? Thanks

 

[The_Doc_Man]

Don't know if this will work for FoxPro, but...

Index.IgnoreNulls property (DAO)

Office developer client VBA reference documentation

learn.microsoft.com

As to the original problem,

Index or primary key cannot contain a Null value (Error 3058)

Office VBA reference topic

learn.microsoft.com

You can expect all the nulls you like, but in general indexed fields don't like nulls because of the rules that apply to operating with them. For example, if you try to bring in a null field that eventually has to be indexed, you would compare the field value to values in the index itself. But if you ask "IS NULL > {some index value}" the answer is NULL, i.e. it is neither YES nor NO. NULL propagates through any comparison expression thus making itself like an unwelcome house guest by taking over its surroundings.

Tips? Don't allow NULL to be the value of an indexed field. OR see if your .IgnoreNulls property for DAO recordsets will work for your case. By the way, if you can assert .IgnoreNulls = TRUE then what you get with a NULL index is a record that is not in the index at all. Making it hard as heck to find, since you wouldn't have indexed it if you weren't planning to search on that field.

 

[InstructionWhich7142]

The funny thing is there aren't any NULL values in any of the tables themselves, I've checked all the indexed columns.

The only NULL is created when the SELECT query does RIGHT JOIN from tables with every item ID as a primary key, to a table with only some items Primary keys and other values stored against them, giving you the normal "All values from Table1 and only those values from Table2 where they match" or however that little JOIN selector box is worded

 

[sonic8]

[...] but I managed to make enough extra totals tables etc to work around [...]

This sounds pretty bizarre.

The error you encounter is caused by FoxPro('s ODBC driver). It might be more promising to ask this question in a FoxProx focused community.

In general I recommend you try to isolate the cause of the problem by reducing your query to the minimal scenario that still triggers the error. This will help with finding a solution or workaround, regardless where you ask.

 

[The_Doc_Man]

The funny thing is there aren't any NULL values in any of the tables themselves, I've checked all the indexed columns.

The only NULL is created when the SELECT query does RIGHT JOIN from tables with every item ID as a primary key, to a table with only some items Primary keys and other values stored against them, giving you the normal "All values from Table1 and only those values from Table2 where they match" or however that little JOIN selector box is worded

The flaw isn't necessarily in the RIGHT JOIN but in having the resultant unmatched (therefore NULL) record fragment align with an indexed field in a way that uses normal filtration or WHERE-clause matching. You can't use "WHERE x = NULL" because that is NEVER true even if x actually IS a NULL. You have to use something more complex like "WHERE x = {some value} OR X IS NULL" or some other syntax. The NZ function is another option in some cases. NULL handling is one of the more complex aspects of LEFT/RIGHT joining.

 

[InstructionWhich7142]

The flaw isn't necessarily in the RIGHT JOIN but in having the resultant unmatched (therefore NULL) record fragment align with an indexed field in a way that uses normal filtration or WHERE-clause matching. You can't use "WHERE x = NULL" because that is NEVER true even if x actually IS a NULL. You have to use something more complex like "WHERE x = {some value} OR X IS NULL" or some other syntax. The NZ function is another option in some cases. NULL handling is one of the more complex aspects of LEFT/RIGHT joining.

I have the below , I tried NZ on every field but still get the error, removing the WHERE resolves it so you're spot on with that, I still don't really understand exactly why it doesn't like it? is it to do with how queries are almost processed "server side" and the results returned? and because that pre-process tries to combine the null with a normal field it freaks out?

WHERE (((table1.field1) Is Null) AND ((table1.field2) Is Null)) OR (((table1.field3)="") AND ((table1.field4)=""))

 

[The_Doc_Man]

The question is, if your RIGHT or LEFT JOIN results in some unmatched records, thus producing nulls to align with a non-null field, what do you want that to mean? Or do you want to prevent the non-null alignment from happening? (In which case the choice of OUTER vs. INNER JOIN becomes a part of the question.) Can you at least figure out WHICH field is producing the error? From the error message, I would think it has to be that one of the members of a JOIN can sometimes catch a null because of being unmatched. If that isn't what is going on, this is going to be harder to decipher.

 

[InstructionWhich7142]

yes the data between the two tables can be in 3 or 4 states, I want to know the 3 where at least something doesn't match if that makes sense, something like
A=B (I don't care)
A not B
B not A
not B not A

it's a query to identify records that lack corresponding information in another table (another system) how do I make it work so it'll put up with those nulls aligning with data?

 

[The_Doc_Man]

As long as you aren't JOINing on a field that can align with a NULL you should be OK. Having a JOIN implies certain indexing requirements that can trip you up real fast.

 

[InstructionWhich7142]

ah that may well be the issue, if the corresponding information isn't in that system then all the fields are effectively NULL, so the primary key (ID field) I'm joining on is also NULL, I don't see why you would use a LEFT join for anything else?

 

[The_Doc_Man]

The problem is not that there is a LEFT JOIN (or, for that matter, a RIGHT JOIN) leading to a null matching field, but HOW you attempt the match-up. A JOIN is really sensitive to how you match it to another field. A WHERE clause allows you to do a more resilient comparison AND allows you to eliminate potentially troublesome expressions where a null would become a problem.

 

[InstructionWhich7142]

Edit: fixed - Thanks! I do appreciate your help, by poking around trying to find the issue this time round it dawned on me to split the two separate Left Join routes with Table B & C in the original query and do Table A in the subsequent query, elegant enough,

Just for speed etc I've ended up 6 or 7 queries deep before, or a full screen of tables and joins! The joy of learning all this myself and through experimentation on the query builder and google :-D

I'm back on this problem as it still seems silly to me and having to bodge with a temp-table that I keep re-creating is having it's own down stream issues

I've re read your posts a lot over the past months and I really don't follow the issue with joins and null's to be honest, I've made a ton of left joins with nulls to nulls on fields that fairly definitely NULL over the years, they just work like joins work, all the time, except now

edit: this got confusing, I tried to clarify my data but the issue seems to be I have two left join trees and they don't like coexisting, I've basically got my block of inner join tables where all batches exist, but then additional info about those batches comes either from Table A or Tables B&C (table A holds a docID and Customer) table B holds DocID and CustID so then Table C is needed to get Customer from customer ID

so

Inner Joins -> LEft Join Table A
Inner Joins -> left join table B -> left join Table C


edit2 - to be specific:

As long as you aren't JOINing on a field that can align with a NULL you should be OK. Having a JOIN implies certain indexing requirements that can trip you up real fast.

I don't actually understand what you mean "JOINing on a field that can align with a NULL" or in your other post "but HOW you attempt the match-up. A JOIN is really sensitive to how you match it to another field."

you either drag and drop the arrow in designer or say "Left join tbl1.fldA = tbl2.fldA" I don't get "align" or "match-up" as you must have some deeper meaning here I'm missing?

sorry and the Where clause bit confuses me more, a Join is not a Where, I understand they're entirely different things, I've used a few unjoined table pairs to do cartesian product or theta joins on a few rare occasions

 

[The_Doc_Man]

I don't actually understand what you mean "JOINing on a field that can align with a NULL"

OK, if you have more than one relationship in the JOIN because you are matching TWO fields at the same time (which is perfectly legal), if they are not dependent on each other then it is possible for one of the OUTER JOINs to bring in a table for which the other JOIN would end up aligned with a NULL.

the Where clause bit confuses me more, a Join is not a Where

If you look up "ORDER OF OPERATIONS FOR SQL" you would find that a JOIN clause is evaluated early but a WHERE clause is (in theory) evaluated later. Though actual order DOES depend on specific implementation and I have had disagreements here on that topic. Specifically for Access, you can think of a WHERE as a late-occurring JOIN. Other back-end SQL cases might differ.

SELECT T1.A, T1.B, T1.C, T1.D, T2.E, T2.F FROM TBL1 AS T1 INNER JOIN TBL2 AS T2 ON T1.G = T2.G ;
SELECT T1.A, T1.B, T1.C, T1.D, T2.E, T2.F FROM TBL1 AS T1, TBL2 AS T2 WHERE T1.G = T2.G ;

Those two queries should produce identical results. One is a JOIN, the other achieves its goals via WHERE. Note that the 2nd one, if you left out the WHERE, would represent a Cartesian JOIN which is a really undesirable (and very much larger) recordset most of the time.