data typing in UNION query

Question

In trying to create a UNION of some tables with different fields and assigning Null to the missing field in separate SELECTs in the UNION--I'm having problems with the typing of the result set where the SELECTs that have the fields are returning the data that is present as type string regardless of the original type the SELECT alone would return without the UNION. (I'm trying to solve this same problem as was posed in this thread: http://social.msdn.microsoft.com/Forums/en-US/accessdev/thread/cf0f9002-60cb-4da3-bd72-03e6771fc539)

Example query I tried after the research, using a first query setup to force data types but return no data:

(SELECT CLng(0) AS CommonLong, CLng(0) AS ForeignKeyA, CLng(0) AS ForeignKeyB, CCur(0) as CurrencyType
FROM Table1 WHERE false)
UNION SELECT Table1.CommonLong, Table1.ForeignKeyA, Null AS ForeignKeyB, Null AS CurrencyType
FROM Table1
UNION SELECT Table2.CommonLong, Null AS ForeignKeyA, Table2.ForeignKeyB, Null AS CurrencyType
FROM Table2
UNION SELECT Table3.CommonLong, Null AS ForeignKeyA, Null AS ForeignKeyB, Table3.CurrencyType
FROM Table3;

This worked just fine in this testcase with the results being either NULL or of the correct original type. Applied to my real-world problem,

(SELECT CLng(0) AS ChangeID, CLng(0) AS PackageID, CLng(0) AS ChangeActivityID, CCur(0) AS Budget, CLng(0) AS REID
FROM PackageIDs WHERE False)
UNION SELECT PackagesChanges.*
FROM PackagesChanges
UNION SELECT PackageBudgetSourceChanges.*
FROM PackageBudgetSourceChanges
UNION SELECT PackageHierarchyChanges.*
FROM PackageHierarchyChanges
UNION SELECT PackageDetailsChanges.*
FROM PackageDetailsChanges
UNION SELECT PackageBudgetChanges.*
FROM PackageBudgetChanges
UNION SELECT REassignmentChanges.*
FROM REassignmentChanges;

it doesn't work at all. The fields that aren't shared are still getting typed as String and that breaks everything that tries to use the results expecting the long and currency type data.

Any other thoughts how to coerce the data typing in a UNION like this?

Answer 1

It sounds to me that you expected the columns/Fields of the resultant dataset to have the same data-types as Fields in the first component of the Union?

If so, it doesn't work that way.  The data-type of the resultant column is the "least" generous data-type that can accommodate the data items from the same column of all the components.  Thus, if the components are of mixed data-types of Text and Numeric, you end up with Text (or Variant?) data-type since number can be converted to Text but not the other way.

If you want to enforce the data-type in the Query, you can convert:

* Each component that requires type-casting.  Of course, there are Text values that cannot be convert to numeric values and you need to accommode these (check before trying to forrce type-casting).

* Use the Union Query as the datasource of an "outer" Query and perform the type-casting on the resultant columns (which are now "iinputs" to the "outer" Query).  Same problem I mentioned above applies.

Outside the Query, you can perform type casting when you actually use the value with VBA conversion functions.  The same problem still applies.

---

Van Dinh

Answer 2

Not sure I understand your comment.

I agree that the approach of casting it in a query of the UNION does work (ugly though it may be). (E.g., for those reading the archive: ForeignKeyAcast: IIf(IsNull([ForeignKeyA]),Null,CLng([ForeignKeyA])) ) So this is the solution I'm moving on with.

"The data-type of the resultant column is the 'least' generous data-type that can accommodate the data items from the same column of all the component."

In my real case, there is not mixed data type in any column. Either it's present in one of the SELECT elements of the UNION and of a given type--my two cases are Long in some columns and Currency in others--or it's a Null coming from one of the SELECTs that doesn't have the column. What I hoped would work is that the first SELECT ((SELECT CLng(0) AS ChangeID, CLng(0) ... FROM PackageIDs WHERE False)) would set the column typing for all that follow--it is the least generous type and can accomodate all items in the column. It just didn't work when I scaled up the problem from the simple testcase to the real case for unknown reason.

The issue with doing the cast outside the Query is that you cannot do Query things like joining the result to a column in another table that still has the correct type.

Answer 3

I was wondering what you meant by

"The fields that aren't shared are still getting typed as String"

in your first post which sounds to be equivalent to

"or it's a Null coming from one of the SELECTs that doesn't have the column"

in your second post?

Firstly, are [PackagesChanges] and similarly-named datasources used in the Union Table names or  Query names?

If they are Table names, then straight away, you need to have the same number of Fields with the use of the "All Fields"  placeholder in each component.  Since the first SELECT (the "dummy" SELECT for data-types) has 5 columns, this means that each Table must have exactly 5 Fields.

In light of the above paragraph, I can't understand what you meant by "The fields that aren't shared" or "one of the SELECTs that doesn't have the column" since each SELECT component must have exactly 5 columns as per your first post.

Perhaps I missed something from your posts? Please explain this now that you see the angle I look at the problem...

---

Van Dinh

Answer 4

Perhaps for shared I should have used common.

The [PackageChanges] and all the other FROMs in my real case are all queries.

They either return columns of form SELECT [table].[column] or form SELECT Null AS [column]. All return the same number of columns.

In my testcase, UNIONing the (SELECT CLng(0) AS [columnoftypelong] ... WHERE False) with the SELECT [table].[columnoftypelong] ... with the SELECT Null AS [columnoftypelong] ... yielded a column of type long as desired. That's what I expected the first SELECT to cast the whole UNION column as. I was so happy.

In my real case, I still got the column of type string. (Every row for that column either Null or a long value cast as a string.) And I was so unhappy.

Answer 5

Perhaps for shared I should have used common.

The [PackageChanges] and all the other FROMs in my real case are all queries.

They either return columns of form SELECT [table].[column] or form SELECT Null AS [column]. All return the same number of columns.

In my testcase, UNIONing the (SELECT CLng(0) AS [columnoftypelong] ... WHERE False) with the SELECT [table].[columnoftypelong] ... with the SELECT Null AS [columnoftypelong] ... yielded a column of type long as desired. That's what I expected the first SELECT to cast the whole UNION column as. I was so happy.

In my real case, I still got the column of type string. (Every row for that column either Null or a long value cast as a string.) And I was so unhappy.

I was guessing that they are Queries not Tables but need confirmation to proceed further.

The Null As Something in the individual component SELECT should return Variant data-type and since this data-type is "broader" than the Long data-type in the "dummy" column, the resultant column (of the Union) must have the "broader" data-type, i.e. Variant.  When this column is used subsequently, ACE/JET does not know the actual data-type for further processing and therefore, it guesses as the default String data-type.

In fact, if the "real" columns already have the correct data-types (which is so per your description), you shouldn't even need the "dummy" SELECT component.  You should force the data-type for the "out-of-thin-air" columns in the component SELECT clauses. Let's say you you have 4 real columns in Table1 and you want to create a Long out-of-thin-air 5th column for the UNION, IIRC, you can use the calculated column:

OutOfThinAirColumnAsLong: IIf(False, CLng(0), Null)

to return the 5th Column with Null value but the data-type is Long.

That leads to other improvements you can implement.  You don't need 6 Queries (5 component Queries + the Union Query) and you don't need the "dummy" SELECT component in the Union.  You could simply combine all into 1 UNION Query with SQL like:

SELECT T1.RealField1, ..., ..., ...,
  OutOfThinAirColumnAsAbove AS OOTAColumn5
FROM Table1 AS T1

UNION
SELECT T2.RealField1, ..., ...., OutOfThinAirColumnAsAbove AS OOTAColumn4,
  OutOfThinAirColumnAsAbove AS OOTAColumn5
FROM Table2 AS T2

UNION
SELECT ...

...

Basically, you can have as many out-of-thin-air columns with the correct data-types for the union as required.

Edit: The OOTA... aliases are not required in the subsequent SELECT components in real SQL but I added them for posting clarity.

---

Van Dinh

Answer 6

It might also be worth considering avoiding your select wildcard (Select <table>.*) and instead name all the columns one by one, including the full set of column names even if the specific select doesn't have that column.

i.e.

Table 1 has A, B, C

Table 2 has C, D

Table 3 has A

then

Select A, B, C, '' From 1

Union

Select '', '', C, D from 2

Union

Select A, '', '', '' from 3;

Cheers!

---

RunnerIE

Answer 7

Using the IIf(False, CLng(0), Null) style constructions in the "placeholder only" columns of the queries that are then unioned was the ticket! Perfect. Thx.

I know I could roll all the queries together--but since at lease two of these queries are, in turn, UNIONs, and the other ones are fairly complex in their own rights, doing it this way seemed way more maintainable. The query that flattened all of this out would be an absolute monster mother of all queries.

Now, IF the Access query editor could deal with UNIONs and nesting and Joins that use operators besides = and so on, THEN I might have a different answer here...

Answer 8

Now, IF the Access query editor could deal with UNIONs and nesting and Joins that use operators besides = and so on, THEN I might have a different answer here...

Sure can... In fact, the SQL View is much more powerful than the QBE view (Query By Example, i.e. the Query grid).  In SQL View, you can use joins that cannot be represented in QBE. For an example of a non-equi join:

SELECT Table1.AccountID, {OtherTable1FieldsAsRequired}, Table2.[Expenese Value]
FROM Table1 INNER JOIN Table2
ON     (Table1.Account_value >= Table2.Account_low_value)
  AND (Table1.Account_value <= Table2.Account_high_value)

(finding the applicable [Expense Value] from the "look-up" Table2 depending on the [Account_value] in Table1).

The above SQL can be done in SQL View but cannot be done in QBE.  In QBE, the same Query must use the WHERE conditions and not a Join.

IIRC, an SQL String can be up to 64K characters and some of the restrictions in the QBE (only equi-join or cross-join/Cartesian join, expression in a "cell" is 1 KB max {or 2 KB max - can't remember exactly}, there are only 32 rows for criteria, i.e. only 31 OR operations max) are not applicable in SQL View.  Thus there are a lot more scope to write complex SQLs.  The max of 32 Tables used still apply, though (but if the SQL gets any where close to the limit, it will probably take forever to process). 

---

Van Dinh

Answer 9

I'm well aware there are lots of things I can do in SQL View but not QBE. Non-equi-join (didn't know this had a term) being just one. Still, trying to get things like nesting correct in a 64k character query in QBE may be something you can do efficiently. Not me. IF I can use QBE, it's always the first choice.

Answer 10

>>Still, trying to get things like nesting correct in a 64k character query in QBE may be something you can do efficiently. Not me. IF I can use QBE, it's always the first choice.<<

In think you meant "in SQL View"???

I also use the QBE as much as I can then switch to SQL View to do the bits that the QBE can't handle. I also use short aliases for Tables/datasources so that I don't get confused by the long Table qualifiers used with Fields.

For the nesting, I use indents like VBA code (as per SQLs posted above) to see the nestings ... However, I can't handle a 64 KB SQL... I reckon about 5 KB SQL String is about the limit and above that I just get confused...

---

Van Dinh