Union of two works, three does not

[pdanes]

I have the following query - three separate queries joined with Union. Each query works independently, and the Union works properly with any two of the three, but when I try to use all three together, I get an error dialog saying my data types are incompatible. I have tried adding Union ALL, and removing the surrounding query (which exists only to sort the result set). When I do that, it initially shows a result set, but after a few seconds the same dialog pops up, and when I click Ok, the data disappears and is replaced by #NAME? in every field.

Select Rod, Druh, RodAutoID, DruhAutoID, cntZaz, cntLit FROM (

SELECT Rody.Druh AS Rod, Null AS Druh, Rody.DruhAutoID AS RodAutoID, Null AS DruhAutoID, Null AS cntZaz, ToD_LiteraturaPocet.cntLit, Rody.Druh AS Poradi
FROM TableOfDruhs AS Rody LEFT JOIN ToD_LiteraturaPocet ON Rody.DruhAutoID = ToD_LiteraturaPocet.DruhAutoID
Where (Rody.Druh Not Like '* *')
AND (Rody.UpTaxonAutoID Is Null)
AND (Rody.Druh Not Like '?')
AND (Rody.Druh Not Like '??')


UNION


SELECT Rody.Druh AS Rod, Druhy.Druh, Rody.DruhAutoID AS RodAutoID, Druhy.DruhAutoID, Null AS cntZaz, ToD_LiteraturaPocet.cntLit, Druhy.Druh AS Poradi
FROM (TableOfDruhs AS Druhy INNER JOIN TableOfDruhs AS Rody ON Druhy.UpTaxonAutoID = Rody.DruhAutoID) LEFT JOIN ToD_LiteraturaPocet ON Druhy.DruhAutoID = ToD_LiteraturaPocet.DruhAutoID
WHERE (Rody.Druh Not Like '* *')
And (Rody.Druh Not Like '?')
And (Rody.Druh Not Like '??')
AND (Rody.UpTaxonAutoID Is Null)


UNION


SELECT Null AS Rod, Druhy.Druh, Null AS RodAutoID, Druhy.DruhAutoID, Null AS cntZaz, ToD_LiteraturaPocet.cntLit, Druhy.Druh AS Poradi
FROM TableOfDruhs AS Druhy LEFT JOIN ToD_LiteraturaPocet ON Druhy.DruhAutoID = ToD_LiteraturaPocet.DruhAutoID
WHERE (Druhy.Druh Like '* *')
AND (Druhy.UpTaxonAutoID Is Null)


)
Order By Poradi

[Minty]

Where you have something like Null as Rod try replacing it with

'' As Rod

It may be interpreting the Null's as the wrong data type.

[pdanes]

That works, thank you. It's rather irritating, though - Nulls should not badger me about data types. And a good bit of subsequent manipulation relies on Nulls in these places. I will have to either modify the code to deal with empty strings instead of Nulls, or pack the surrounding query with Iif function calls to change the empty strings back to Nulls.

But at least I have a functioning query to start from - appreciate the tip.

Do you have any idea what exactly could be causing problem, when any two of the queries work together without issue, and only the three together have difficulty?

[Minty]

It's probably to do with inferred typing of datatypes, I know in SQL server you have to be cognisant of it, but I'm not sure what the effects or inferences are in Access.

Where you want a null you could put in an obvious dummy value and then replace them afterwards.

[June7]

I don't have issue with Null AS on first SELECT and I have built UNION with way more than 3 SELECT.

You could provide sample data, even attach db, for analysis.

[pdanes]

It's probably to do with inferred typing of datatypes, I know in SQL server you have to be cognisant of it, but I'm not sure what the effects or inferences are in Access.Where you want a null you could put in an obvious dummy value and then replace them afterwards.

Seems logical - I've run into similar problems with nulls in other places. I think it's wrong - a Null means unknown, undefined - no value of any sort, and that should include the type of data. If the value does not exist, it is meaningless to talk about the type of the value. Null should go along with absolutely everything.The dummy value will be the empty string - the text fields are all configured with zero length prohibited, so an empty string will mean there was supposed to be a Null. I think it will be cleaner to put Iif(Druh='',Null,Druh) in the surrounding query than to change all the downstream code that expects Nulls.

[pdanes]

I don't have issue with Null AS on first SELECT and I have built UNION with way more than 3 SELECT.You could provide sample data, even attach db, for analysis.

Thank you for the offer, I will consider that. First I will try one place at a time, switching between Null and empty string, and see if that sheds some light on the situation for me. If I'm still stuck, I will put together a small sample database that shows my problem and post it here.

[pdanes]

Okay, some more poking at it answered some questions, but raised others.

The key field was Null AS DruhAutoID in the first subquery. I can leave everything else Null, but that one field borks the entire thing. If I define it as a string ('' AS DruhAutoID), all works fine, if I define it as a number (0 AS DruhAutoID), all works fine, but if I leave it as a Null, the query bombs, and ONLY when I incorporate all three subqueries together. If I only use any one or two of the subqueries, it also works fine.

Putting an Iif(DruhAutoID=0,Null,DruhAutoID) or Iif(DruhAutoID='',Null,DruhAutoID) call in the surrounding query correctly changes my dummy value back to a Null in the final result set.

I then tried putting in a fourth subquery - I added the following subquery as the first in a Union of four subqueries, and that also works, so I think that is what I will use. My main reason for using that method in the end is that I think it is faster than calling the Iif function for every record, and it allows me to clearly define the interior field names right off the bat, thereby not needing to specify field names for my Nulls further down, where the subqueries are more complex.

Select '' AS Rod, '' AS Druh, 0 AS RodAutoID, 0 AS DruhAutoID, 0 AS cntZaz, 0 As cntLit, '' AS Poradi From Dummy Where False
 
Union
. 
.
.

I'm still a bit irked at the original not working, though. If I define it as either text or number, it works, but leaving it undefined as Null breaks something. Jet SQL does not have a Cast function, as far as I know, so I can't force my Null into a specific data type. Does anyone have any ideas on what may be happening? Specifically, what data type is that initial undefined Null assuming that collides with subsequent subqueries, when both text and number do NOT collide with the subsequent real data, and why does the combination of three subqueries break when any one or two work properly?

[NoellaG]

If you have a NULL as first value, the query auto determines the data type, using the first values. My guess is that this works when the union query is simple enough, but once it gets a bit more complicated it stops working. Hence the query returns the correct result with one union but fails with more union statements.

[June7]

I have a UNION where in some fields the first 10 SELECT have Null and the 11th line has numeric data. In a couple of fields the first 3 SELECT have Null then field has an IIf() calc - data is numeric but result returns as text.

[NoellaG]

I Don't know how the Access engine handles values and NULLS in queries, but in SQL server it's not because you see the NULLS in the first lines that the SQLOS sees the same. Remember, sorting is the last operation in the evaluation and parsing process. How the dataset is build depends on wether it's a UNION or UNION ALL, the indexes it can use and the statistics. In SQL server I would look at the query plan that is generated. I don't know if there is a similar possibility in Access, but vaguely remember there existed an external tool for Access that could do that.