Using UNION In Framework Manager 10.2.1 but Naming each Row Differently

[gosoccer]

Hi folks,
Thank you in advance for your time on this.
I have the following issue:
The # of columns has to be the same in Union as you know.
So, I have three tables as following:

select
a.car_id,
a.car_type
a.car_name
from FORD

UNION
select
a.car_id,
a.car_type
a.car_name
from LEXUS

UNION

select
a.car_id,
a.car_type
a.car_name
from HONDA

Now, I need to name every column differently so they are don't show as a.car_id, a.car_type, a.car_name. i.e for
HONDA, I'm trying to do something like

select
a.car_id AS HONDA.car_id,
a.car_type AS HONDA.car_type,
a.car_name AS HONDA.car_name,
from HONDA

But after I add AS xxxxxx still the first Union SQL Column names takes over when I run it in Framework Model - TEST,
Please let me know if you have any recommendations.

Thx :) :)

[MFGF]

Hi folks,
Thank you in advance for your time on this.
I have the following issue:
The # of columns has to be the same in Union as you know.
So, I have three tables as following:

select
a.car_id,
a.car_type
a.car_name
from FORD

UNION
select
a.car_id,
a.car_type
a.car_name
from LEXUS

UNION

select
a.car_id,
a.car_type
a.car_name
from HONDA

Now, I need to name every column differently so they are don't show as a.car_id, a.car_type, a.car_name. i.e for
HONDA, I'm trying to do something like

select
a.car_id AS HONDA.car_id,
a.car_type AS HONDA.car_type,
a.car_name AS HONDA.car_name,
from HONDA

But after I add AS xxxxxx still the first Union SQL Column names takes over when I run it in Framework Model - TEST,
Please let me know if you have any recommendations.

Thx :) :)

Hi,

With a union you're appending sets of rows together within a fixed set of columns - ie you will only have three resulting columns: one for car_id, one for car_type and one for car_name. I'm not clear on where the requirement to have the column labels specific to a manufacturer comes in, when the rows within the data set will be for three different manufacturers (FORD, LEXUS and HONDA)? Can you explain? You can't name every column differently since a Union delivers the same three columns for the entire result set. Do you need a join rather than a Union? Can you explain?

MF.

[gosoccer]

yes, I need a join. Doesn't make sense. I'm building a join so each table have it's own independent column name.
Thx for confirming.
  8) 8)

[MFGF]

yes, I need a join. Doesn't make sense. I'm building a join so each table have it's own independent column name.
Thx for confirming.
  8) 8)

Do the three sets of data have common keys you can join them on?

MF.

[gosoccer]

Yeap! I end up using the FM Model 0-n Relationship and create the subject Q without using the UNION. Getting the same count as my UNION based Subject Q now. Thx so much!!