Weird Access bug?

[NeoPa]

Jim:
Does this make sense to anyone?

Yes. You're using no ORDER BY clause in your SQL so the TOP 5 predicate is equivalent to ANY 5. How the SQL engine processes your SQL depends on what you specify primarily, but outside of that it makes its best guess as to which approach will be most efficient. In a UNION query that guess may result in a completely different approach and therefore different records selected.

That said, I can only see this data coming out if the records selected all have empty string values. Not sure how likely that is in your data.

Nevertheless, I would always suggest an ORDER BY clause be used when using the TOP predicate unless you really don't care what the results are.

[jimatqsi]

In fact, Any 5 is just as good as Top 5 for me int this case. I was not particular about which 5 records I was getting. No matter which 5 records were chosen, there should have been either a "NoBin" or an actual bin returned. There are no other options.

And we can see that the result set of 10 is made up of the two smaller result sets combined. Except the union query has changed what is delivered for that one column because it never delivers a "NoBin" value.

This odd result was reflected also when I ran without the Top 5 limitation.

Jim

[NeoPa]

I guess the ItemCode values are the descriptions and they all look to match from one set to the other. Are they unique? You would expect so from the name including "Code", but then using variable-length alpha codes is very unusual in DB work so hard to be sure.

You're right that this appears to be weird. Can you run the following for me and post back the results :

SELECT TOP 5 
       [ItemCode]
     , [WarehouseCode]
     , Nz([T1].[BinLocation],'Null') AS [BinLocation]
     , CStr('') AS [VABin]
     , CStr(Nz([T1].[BinLocation],'NoBin')) AS [NJBin]
FROM   [dbo_IM_ItemWarehouse] AS [T1]
WHERE  ([WarehouseCode]='100')
UNION
SELECT TOP 5 
       [ItemCode]
     , [WarehouseCode]
     , Nz([T2].[BinLocation],'Null') AS [BinLocation]
     , CStr(Nz([T2].[BinLocation],'NoBin')) AS [VABin]
     , CStr('')  AS [NJBin]
FROM   [dbo_IM_ItemWarehouse] AS [T2]
WHERE  ([WarehouseCode]='000')

I'm not sure how this fits, but I know UNION queries can get confused about the type of data fields unless the first query is explicit, which is not the case for both fields here. See if any of these tweaks make a difference, or just give us some extra clues as to what's going on.

[jimatqsi]

Your code returns only real bin numbers and blanks, 'NoBin' never appears. So this made me suspicious. I have reexamined the first query and found it to be untruthful.

Here's what I did. I used Access to add a new row to the ItemWarehouse table. I filled in only the SKU and warehouse, and all numerics I set to zero. The SQL database allows Nulls, so BinLocation in this record will be Null.

Then I ran my 3 queries again and got a different result. Query 1 now returned BinNull_YN=-1 for 6100 rows. Query 2 now reports this new row as having a Null bin. Query 3 also reports 6100 rows.

So now I think there are no Nulls in the BinLocation field and the test in query 1

 IsNull([BinLocation]) AS BinNull_YN

returns true for Null and "" alike. This is a surprise to me.

This is a Sage database and I should have known from the beginning this was the case. I don't believe Sage ever leaves a field Null.

Thanks to everyone for your comments.

Jim

[NeoPa]

Hi Jim.

That just confirms the extreme importance of working with Nulls directly rather than trying to work around them. Not everyone can, or even wants to of course, but for someone like you, who I know to be clever enough to handle it, it makes good sense going forwards.

Great that you managed to resolve the problem anyway :-)