Union query - Null value issue (1 Viewer)

[Tskutnik]

All,
Thanks in advance for the help.
Problem is: I have a union query that merges 3 separate prior queries. Each of the 3 has slightly different data (data type fields), so I have to create some null or blank fields for the mis-matched fields. Like this (shorthand using Null as an example):

Select
Account number,
Value1,
Null as Value2,
Null as Value3
From Table1;

Union all
Select
Account number,
Null as Value1,
Value2,
Null as Value3
From Table2;

Union All
Select
Account number,
Null as Value1,
Null as Value2,
Value3
From Table3;

Problem is: I need the value fields to remain in number format so I can add them in later queries. I cannot plug zeros in the missing fields (e.g. 0 as Value1) because a 0 would look like a real value and would corrupt the data in this project. I need more like a Null or "", but these give me data type mismatched errors in later steps when I try to sum the Value fields.

Thoughts?:banghead:

 

[Galaxiom]

Use datatype appropriate but at least one out of scope value in the first subquery. Then query the results with a Where clause that drops the out of scope record.

BTW The first subquery of a Union determines the field names so there is no point including field name aliases in the subsequent subqueries.

 

[Tskutnik]

Thanks - that's a great tip, but I'm getting a "Number of columns in the selected tables don't match.... " blah blah error. Here is the actual code. Did I do this the way you explained?

In the first subquery - the first 4 fields are text, and the last 5 are value types.

SELECT
[Portfolio ID],
EndDate,
[YYYYMM],
AssetClass,
[SumOfValue] as MV,
-999999 as Accrual,
-999999 as Fees,
-999999 as GTW,
-999999 as NTW
FROM MarketValueOnly_Grouped;

UNION ALL
SELECT
[Portfolio ID],
EndDate,
[YYYYMM],
AssetClass,
[SumOfValue] AS Accrual
FROM AccrualOnly_Grouped;

UNION
ALL SELECT

[Portfolio ID],
EndDate,
[YYYYMM],
AssetClass,
Value as Fees
FROM FeesOnly_Segregated;


UNION ALL
SELECT
[Portfolio ID],
EndDate,
[YYYYMM],
AssetClass,
[Value] AS GTW
FROM GTWOnly_Segregated;

UNION ALL SELECT

[Portfolio ID],
EndDate,
[YYYYMM],
AssetClass,
[Value] As NTW
FROM NTWOnly_Segregated;

 

[Galaxiom]

You still need to include the Nulls in the subsequent subqueries where there is no data so the number and order of the fields match across all. Just no need to include the names for them.

 

[Tskutnik]

This worked really well but in a different way - I added a new fake subquery (table) with just one fake record that had all the fields in the following 5 real subqueries, with appropriate value formats. Then when I added the other real 5 subqueries they followed the format of the fake one. I queried this result to exclude the one fake record and bang - success.
Thanks a lot for this help. It is really appreciated. I was stuck on this for a while.

 

[Galaxiom]

This worked really well but in a different way -

Love it when posters take an idea and run with it.