invalid number of result columns for set operator input branches

BlackBranded 0
Viewed 44

I m trying union all in my query but it's giving me an error. there more then one tables whch I join and union them all there is one table where some columns names are different so i need help in it please

SQL compilation error: invalid number of result columns for set operator input branches, expected 17, got 18 in branch 2

here is my query

    select case
  when "PROFILE" = 44399579 then 'United States'
  when "PROFILE" = 36472271 then 'New Zealand'
  when "PROFILE" = 41751607 then 'Australia'
  when "PROFILE" = 112008453 then 'Hong Kong'
  when "PROFILE" = 42279088 then 'Japan'
  when "PROFILE" = 243780788 then 'Europe'
  when "PROFILE" = 243786823 then 'United Kingdom'
  else null
  end COUNTRY, CASE
  WHEN CHANNEL_GROUPING='ODLA Paid Search' THEN 'Paid Search'
  WHEN CHANNEL_GROUPING='ODLA Social' THEN 'Social'
  WHEN CHANNEL_GROUPING='Paid Partnership' THEN 'Affiliates'
 WHEN CHANNEL_GROUPING='Landing Pages' THEN '(Other)'
  WHEN CHANNEL_GROUPING='Programmatic' THEN '(Other)'
  WHEN CHANNEL_GROUPING='Paid Social' THEN 'Social'
  ELSE CHANNEL_GROUPING
 END CHANNEL_GROUPING_final, ,SSource ,Date,MEDIUM,SOURCE_MEDIUM,NEW_USERS,AVG_SESSION_DURATION,TRANSACTIONS,case when SESSIONS = 0 then 1 else SESSIONS end SESSIONS ,TRANSACTION_REVENUE,PAGEVIEWS_PER_SESSION, CHANNEL_GROUPING  ,bounces ,BOUNCE_RATE
from (
select 'Comvita' as SSource   ,Date,MEDIUM,SOURCE_MEDIUM,NEW_USERS,SESSIONS,AVG_SESSION_DURATION,TRANSACTIONS,case when SESSIONS = 0 then 1 else SESSIONS end SESSIONS ,TRANSACTION_REVENUE,PAGEVIEWS_PER_SESSION
     ,CHANNEL_GROUPING  ,bounces ,BOUNCE_RATE
from ( select * from google_analytics_phase1.traffic_channel union all select 'Comvita' as SSource,* from google_analytics_phase2.traffic_channel union all select 'Comvita' as SSource,* from google_analytics_phase3.traffic_channel 
   union all

    select 'Olive Life Daily' as SSource ,Date,MEDIUM,SOURCE_MEDIUM,NEW_USERS,SESSIONS,AVERAGE_SESSION_DURATION,TRANSACTIONS,case when SESSIONS = 0 then 1 else SESSIONS end SESSIONS ,SCREEN_PAGE_VIEWS_PER_SESSION PAGEVIEWS_PER_SESSION
   ,TOTAL_REVENUE TRANSACTION_REVENUE  ,DEFAULT_CHANNEL_GROUPING CHANNEL_GROUPING   , case when BOUNCE_RATE is not null then 0 end bounces ,BOUNCE_RATE,COUNTRY
    ,
    CASE
 WHEN DEFAULT_CHANNEL_GROUPING='ODLA Paid Search' THEN 'Paid Search'
 WHEN DEFAULT_CHANNEL_GROUPING='ODLA Social' THEN 'Social'
 WHEN DEFAULT_CHANNEL_GROUPING='Paid Partnership' THEN 'Affiliates'
 WHEN DEFAULT_CHANNEL_GROUPING='Landing Pages' THEN '(Other)'
 WHEN DEFAULT_CHANNEL_GROUPING='Programmatic' THEN '(Other)'
 WHEN DEFAULT_CHANNEL_GROUPING='Paid Social' THEN 'Social'
 ELSE DEFAULT_CHANNEL_GROUPING
 END CHANNEL_GROUPING_final
    
    
    from "FIVETRAN_DATABASE_COMVITA"."GA4_OLIVELIFEDAILY"."TRAFFIC_CHANNEL")
    ) a
tsql sql
1 Answers

This is likely one of the problems:

Original:

select * from google_analytics_phase1.traffic_channel 
union all 
select 'Comvita' as SSource,* from google_analytics_phase2.traffic_channel 
union all 
select 'Comvita' as SSource,* from google_analytics_phase3.traffic_channel

If we assume that various tables have the same columns, then change it to:

select 'Something' as SSource,* from google_analytics_phase1.traffic_channel 
union all 
select 'Comvita' as SSource,* from google_analytics_phase2.traffic_channel 
union all 
select 'Comvita' as SSource,* from google_analytics_phase3.traffic_channel

But there could be other problems, too in later unions. Continue to look at the columns returned for all your UNIONs.

UPDATE: Look at the columns for Olive Life Daily and see if they match your prior unions. This one is clearly returning only 15 columns.

Isolated0
Related