CODEWITHSUNDEEP
sqlunionteradata
bov25
bov25

Reputation: 121

Append two tables - Union not working in SQL (Teradata)

I'm a beginner with SQL. I'm using Teradata. We're trying to pull the data together to compare actuals versus forecast.

Here is my code:

select 
'Actuals' as fc_version,
A.tra_fiscal_year as fiscal_year,
A.tra_fiscal_week as fiscal_week,
A.tra_allocation_category_code as category,
A.tra_grand_pricing_sales_channel as grandparent_channel,
D.fc_prod_id,
coalesce(
case
when A.tra_ticket_product_code like 'A%' then 'Total'
else null
end,

case
when A.tra_grand_pricing_sales_channel = 'INMKT' Then 'In Market'
else 'All Other'
end )
as fc_sales_channel,

sum(A.tra_ticket_quantity) as units_sold,

from ((pd_plan_forecast_db.adm_rev_detail A
left outer join
pd_plan_forecast_db.adm_rev_prod_code_to_fc_id B
on (A.tra_ticket_product_code = B.product_code))
left outer join
pd_plan_forecast_db.adm_rev_ticket_code_to_fc_id C
on (A.tra_ticket_code = C.ticket_code)
left outer join
pd_plan_forecast_db.adm_rev_fc_prod_info D
on (coalesce(B.fc_prod_id, C.fc_prod_id) = D.fc_prod_id))

group by 1, 2, 3, 4, 5, 6, 7

union

select fc_version, fiscal_year, fiscal_week, category, null as grandparent_channel, fc_prod_id, fc_sales_channel, sum(units_sold) as units_sold
from pd_plan_forecast_db.adm_rev_fc
where fiscal_year = 2017 and fiscal_week = 1
group by 1,2,3,4,5,6,7

If you paste the first part before the union, it has the correct result. Likewise with the second part after the union.

But the entire code together gives the error: SELECT Failed. [3654] Corresponding select-list expressions are incompatible.

Here is a general idea of the tables I'm using. enter image description here

Upvotes: 2

Views: 4184

Answers (2)

ScaisEdge
ScaisEdge

Reputation: 133360

You should have the same number of columns in the tow select and (the number seems 8 in both the select ) but
in your union you should not use alias in the second select 

select 
'Actuals' as fc_version,
A.tra_fiscal_year as fiscal_year,
A.tra_fiscal_week as fiscal_week,
A.tra_allocation_category_code as category,
A.tra_grand_pricing_sales_channel as grandparent_channel,
D.fc_prod_id,
coalesce(
case
when A.tra_ticket_product_code like 'A%' then 'Total'
else null
end,

case
when A.tra_grand_pricing_sales_channel = 'INMKT' Then 'In Market'
else 'All Other'
end )
as fc_sales_channel,

sum(A.tra_ticket_quantity) as units_sold,

from ((pd_plan_forecast_db.adm_rev_detail A
left outer join
pd_plan_forecast_db.adm_rev_prod_code_to_fc_id B
on (A.tra_ticket_product_code = B.product_code))
left outer join
pd_plan_forecast_db.adm_rev_ticket_code_to_fc_id C
on (A.tra_ticket_code = C.ticket_code)
left outer join
pd_plan_forecast_db.adm_rev_fc_prod_info D
on (coalesce(B.fc_prod_id, C.fc_prod_id) = D.fc_prod_id))

group by 1, 2, 3, 4, 5, 6, 7

union

select 
    fc_version
    , fiscal_year
    , fiscal_week
    , category
    , null 
    , fc_prod_id
    , fc_sales_channel
    , sum(units_sold) 
from pd_plan_forecast_db.adm_rev_fc
where fiscal_year = 2017 and fiscal_week = 1
group by 1,2,3,4,5,6,7

Upvotes: 1

Alexey R.
Alexey R.

Reputation: 8676

You must have the same number of columns in both parts (top and bottom) as well as the same data type.

As far as I can count there are 9 columns in the top query and 8 in the bottom one.

UPD: the fix according to the author is convert null to empty string in the below query.

Upvotes: 3

Related Questions