Union Issue

[Guest]

Hope someone might be able to help me. I have two queries that generate
"identical" data from two different sources. When I look at each of the query
outputs, it has exactly what I want in each. Each query has identical column
names, and the number of elements are exactly the same in each.

The next step I do is a union query between the two queries. My intent is
to basically "merge" the two queries into one (I later project a table from
them, but my problem is before this point).

Problem: When I execute the union, some of the columns that had data in
them, from the "second" of the two queries in the union, no longer have data
in them, after the union (they are null/blank). I can see the data is there
"going in" but it is not there, post union.

Example:
Query 1:

Column1 Column2 Column3 Column4 Column5
D V C Null Null
F C B Null Null
(columns 4/5 in query 1 are always contain null value)

Query 2:
Column1 Column2 Column3 Column4 Column5
A B C 1 0
A C 1 0 1
(columns 4/5 in query2 always contain a numeric value, even if zero)

Here is my Union statement:

SELECT * FROM [qry 1000 Transform Closed Tickets] WHERE [qry 1000 Transform
Closed Tickets.Support Status]="Supported"
UNION SELECT * FROM [qry 1000 Transform Open Tickets] WHERE [qry 1000
Transform Open Tickets.Support Status]="Supported";

qry 1000 Transformed CLosed tickets = query 1 in my example.
qry 1000 Transform Open tickets = query 2 in my example.

I am "guessing" that the data in the first table is "defining" what can be
in the output query, and thus, since the default values are not numeric, it
is causing the second query data not to load, because it IS numeric. This
make any sense? Any other ideas?

Thanks!

Patk

 

[Guest]

That is my guess so just reverse one and two in the union.
Another way is to define the fields like this --
SELECT Column1, Column2, Column3, 0 AS Column4, 0 AS Column5
FROM [qry 1000 Transform Closed Tickets] WHERE [qry 1000 Transform
Closed Tickets.Support Status]="Supported"
UNION SELECT * FROM [qry 1000 Transform Open Tickets] WHERE [qry 1000
Transform Open Tickets.Support Status]="Supported";

 

[Guest]

That was the problem. When I reversed them, it took care of the issue with
these fields. Problem is, I have the "reverse" issue with the other data,
and now I have fields from Query 1 not being defined. What I have done, tho,
is to default the values of the fields in query 1 from being Null, to be
zeros, and I think that will take care of it. It is ok for them to be either
0 or null, but since zeroing them solves the problem with the second query, I
will make that change and see if it works.

Thanks, Karl!

Patk

That is my guess so just reverse one and two in the union.
Another way is to define the fields like this --
SELECT Column1, Column2, Column3, 0 AS Column4, 0 AS Column5
FROM [qry 1000 Transform Closed Tickets] WHERE [qry 1000 Transform
Closed Tickets.Support Status]="Supported"
UNION SELECT * FROM [qry 1000 Transform Open Tickets] WHERE [qry 1000
Transform Open Tickets.Support Status]="Supported";

--
KARL DEWEY
Build a little - Test a little

Hope someone might be able to help me. I have two queries that generate
"identical" data from two different sources. When I look at each of the query
outputs, it has exactly what I want in each. Each query has identical column
names, and the number of elements are exactly the same in each.

The next step I do is a union query between the two queries. My intent is
to basically "merge" the two queries into one (I later project a table from
them, but my problem is before this point).

Problem: When I execute the union, some of the columns that had data in
them, from the "second" of the two queries in the union, no longer have data
in them, after the union (they are null/blank). I can see the data is there
"going in" but it is not there, post union.

Example:
Query 1:

Column1 Column2 Column3 Column4 Column5
D V C Null Null
F C B Null Null
(columns 4/5 in query 1 are always contain null value)

Query 2:
Column1 Column2 Column3 Column4 Column5
A B C 1 0
A C 1 0 1
(columns 4/5 in query2 always contain a numeric value, even if zero)

Here is my Union statement:

SELECT * FROM [qry 1000 Transform Closed Tickets] WHERE [qry 1000 Transform
Closed Tickets.Support Status]="Supported"
UNION SELECT * FROM [qry 1000 Transform Open Tickets] WHERE [qry 1000
Transform Open Tickets.Support Status]="Supported";

qry 1000 Transformed CLosed tickets = query 1 in my example.
qry 1000 Transform Open tickets = query 2 in my example.

I am "guessing" that the data in the first table is "defining" what can be
in the output query, and thus, since the default values are not numeric, it
is causing the second query data not to load, because it IS numeric. This
make any sense? Any other ideas?

Thanks!

Patk

 

[Guest]

If you have a text field instead of a number the just use --
"" AS FieldName
This gives a zero lenght string.
--
KARL DEWEY
Build a little - Test a little

That was the problem. When I reversed them, it took care of the issue with
these fields. Problem is, I have the "reverse" issue with the other data,
and now I have fields from Query 1 not being defined. What I have done, tho,
is to default the values of the fields in query 1 from being Null, to be
zeros, and I think that will take care of it. It is ok for them to be either
0 or null, but since zeroing them solves the problem with the second query, I
will make that change and see if it works.

Thanks, Karl!

Patk

That is my guess so just reverse one and two in the union.
Another way is to define the fields like this --
SELECT Column1, Column2, Column3, 0 AS Column4, 0 AS Column5
FROM [qry 1000 Transform Closed Tickets] WHERE [qry 1000 Transform
Closed Tickets.Support Status]="Supported"
UNION SELECT * FROM [qry 1000 Transform Open Tickets] WHERE [qry 1000
Transform Open Tickets.Support Status]="Supported";

--
KARL DEWEY
Build a little - Test a little

Hope someone might be able to help me. I have two queries that generate
"identical" data from two different sources. When I look at each of the query
outputs, it has exactly what I want in each. Each query has identical column
names, and the number of elements are exactly the same in each.

The next step I do is a union query between the two queries. My intent is
to basically "merge" the two queries into one (I later project a table from
them, but my problem is before this point).

Problem: When I execute the union, some of the columns that had data in
them, from the "second" of the two queries in the union, no longer have data
in them, after the union (they are null/blank). I can see the data is there
"going in" but it is not there, post union.

Example:
Query 1:

Column1 Column2 Column3 Column4 Column5
D V C Null Null
F C B Null Null
(columns 4/5 in query 1 are always contain null value)

Query 2:
Column1 Column2 Column3 Column4 Column5
A B C 1 0
A C 1 0 1
(columns 4/5 in query2 always contain a numeric value, even if zero)

Here is my Union statement:

SELECT * FROM [qry 1000 Transform Closed Tickets] WHERE [qry 1000 Transform
Closed Tickets.Support Status]="Supported"
UNION SELECT * FROM [qry 1000 Transform Open Tickets] WHERE [qry 1000
Transform Open Tickets.Support Status]="Supported";

qry 1000 Transformed CLosed tickets = query 1 in my example.
qry 1000 Transform Open tickets = query 2 in my example.

I am "guessing" that the data in the first table is "defining" what can be
in the output query, and thus, since the default values are not numeric, it
is causing the second query data not to load, because it IS numeric. This
make any sense? Any other ideas?

Thanks!

Patk