UNION statement? 1

[vb89]

I'm trying to add a union statement(my statement is towards the bottom of the page )to the following cursor, but when i compile it says "query block has incorrect number of result columns." I understand why this is, but how do i go about solving the problem.

  select 
      ytd.player_id,
      ytd.player_id_1032,
      ytd.moniker,
      ytd.last_name,
      rost.rookie_year,
      rost.team_id,
      sum(ytd.games_played) as tot_games_played,
      1 as consecutive_games,
      sum(ytd.home_runs) as tot_home_runs,
      sum(ytd.rbis) as tot_rbis,
      sum(ytd.runs) as tot_runs,
      sum(ytd.hits) as tot_hits,
      sum(ytd.at_bats) as tot_at_bats,
      sum(ytd.walks) as tot_walks,
      sum(ytd.hit_by_pitch) as tot_hbp,
      sum(ytd.stolen_bases) as tot_stolen_bases,
      trim(decode(to_char( baseball.bb_slug_pct(sum(ytd.at_bats), sum(ytd.hits), sum(ytd.doubles), sum(ytd.triples), sum(ytd.home_runs)) , '9d000'), 0, '-', null, '-', to_char( baseball.bb_slug_pct(sum(ytd.at_bats), sum(ytd.hits), sum(ytd.doubles), sum(ytd.triples), sum(ytd.home_runs)) , '9d000'))) as slugging_pct,
      sum(ytd.doubles) as tot_doubles,
      sum(ytd.triples) as tot_triples,
      sum(ytd.sacrifice_bunts) as tot_sac_bunts,
      sum(ytd.sacrifice_flies) as tot_sac_flies,
      sum(ytd.strikeouts) as tot_strikeouts,
      1 as walk_off_home_runs,
      sum(ytd.grand_slams) as tot_grand_slams,
      sum(ytd.pinch_hr) as tot_pinch_hrs,
      1 as leadoff_home_runs
   from customer_data.cd_baseball_bat_ytd_stats ytd,
      customer_data.cd_baseball_roster rost
   where ytd.player_id in (select player_id
                           from customer_data.cd_baseball_bat_ytd_stats
                           where season_id = iSeasonID
                             and game_type_id = 1
                             and split_number = -3
                             and active_record != 'R')
     and ytd.split_number = -3
     and ytd.game_type_id = 1
     and ytd.sequence = 0
     and rost.player_id = ytd.player_id
     and rost.league_id = iLeagueID
     and ytd.season_id <= iSeasonId
     and active_record in ('Y', 'N')
      and rost.year_last = 2008
                                   -- Union Statement BEGINS
     union 
     select 
      player_id,
      player_id_1032,
      moniker,
      last_name,
      rookie_year,
      team_id
      from customer_data.cd_baseball_roster 
     where year_last = 2008
     group by 1,2,3,4,5,6
   --group by ytd.player_id, ytd.player_id_1032, ytd.moniker, ytd.last_name, rost.rookie_year, rost.team_id
   order by ytd.player_id_1032;

 

[SantaMufasa]

Simply place NULL as place holders for the expressions that are missing.

Let us know your results.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[vb89]

I kept everything the same except for the suggestion that you recommended and when i ran the query it threw an error..."not a single-group group function" Any ideas?

 

[commatom]

You need to have a group by on each query.

It should be like

SELECT ....
FROM ...
WHERE ...
GROUP BY...
UNION
SELECT ...
FROM ...
WHERE ...
GROUP BY ...

Good luck


Also know that the union will find distinct values and can sometimes take longer to run. If you are not worried about duplicate results, you can use UNION ALL, it will be much faster.

 

[vb89]

that seemed to have fixed it...thanks

 

[commatom]

Great, now fix my problem