Never ending loop...not sure why?

[vb89]

When I run my current procedure, it seems to be getting caught in a never ending loop, not entirley sure what the problem is...? I'm not quite the expert so if possible, the more detail in the explaniation the better...thanks in advanced!!

--The cursors

cursor konami_mlb_pitchers_crsr is
   select player_id_1032
   from customer_data.cd_baseball_pit_ytd_stats
   where split_number = -3
     and game_type_id = 1
     and sequence = 0
     and season_id = iSeasonID
   order by player_id_1032;



cursor konami_mlb_career_pit_crsr is
   select ytd.player_id_1032,
      ytd.player_id,
      ytd.moniker,
      ytd.last_name,
      sum(ytd.games_played),
      trunc(sum(ytd.outs_pitched) / 3) || '.' || mod(sum(ytd.outs_pitched), 3) as innings_pitched,
      sum(ytd.complete_games),
      sum(ytd.shut_outs),
      sum(ytd.wins),
      sum(ytd.losses),
      to_char((sum(ytd.wins)) / decode(sum(ytd.wins) + sum(ytd.losses), 0, 1, null, 1, sum(ytd.wins) + sum(ytd.losses)), '0d000') as winning_pct,
      trim(to_char( (9 * sum(ytd.earned_runs)) / (sum(ytd.outs_pitched) / 3) , '990d00')) as era,
      sum(ytd.strikeouts),
      sum(ytd.saves),
      sum(ytd.hits),
      sum(ytd.home_runs),
      sum(ytd.walks),
      sum(ytd.hit_batters),
      sum(ytd.earned_runs),
      sum(ytd.runs)
   from customer_data.cd_baseball_pit_ytd_stats ytd,  customer_data.cd_baseball_roster rost
   where 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 ytd.active_record != 'R'
     and ytd.player_id_1032 in
     (
    --Includes Players that were on the DL for the entire season                                           
      select player_id_1032
     from customer_data.cd_baseball_roster r
     where r.year_last = 2008
     and r.league_id = iLeagueID
     and r.player_id_1032 is not NULL
     )
     group by ytd.player_id_1032,ytd.player_id, ytd.moniker,  ytd.last_name;

--Where the cursors get opened

 open konami_mlb_pitchers_crsr;
   loop
      fetch konami_mlb_pitchers_crsr into pitcher;
      exit when konami_mlb_pitchers_crsr%notfound;
      
      open konami_mlb_career_pit_crsr;
      loop
         fetch konami_mlb_career_pit_crsr into pitcher_record;

         exit when konami_mlb_career_pit_crsr%notfound;
         
         utl_file.put_line(file_handle, pitcher_record.first_name || ',' ||
                                        pitcher_record.last_name || ',' ||
                                        pitcher_record.id || ',' ||
                                        pitcher_record.games_played || ',' ||
                                        pitcher_record.innings_pitched || ',' ||
                                        pitcher_record.complete_games || ',' ||
                                        pitcher_record.shutouts || ',' ||
                                        pitcher_record.wins || ',' ||
                                        pitcher_record.losses || ',' ||
                                        pitcher_record.winning_pct || ',' ||
                                        pitcher_record.era || ',' ||
                                        pitcher_record.strikeouts || ',' ||
                                        pitcher_record.saves || ',' ||
                                        pitcher_record.hits || ',' ||
                                        pitcher_record.homeruns || ',' ||
                                        pitcher_record.walks || ',' ||
                                        pitcher_record.hit_batters || ',' ||
                                        pitcher_record.earned_runs || ',' ||
                                        pitcher_record.runs);
      end loop; -- end loop through the stats for each pitcher
      close konami_mlb_career_pit_crsr;
   end loop; -- end loop through all the pitchers
   close konami_mlb_pitchers_crsr;

 

[LKBrwnDBA]

Bad idea to have cursor within a LOOP, but if you want it that way, you have to provide a parameter (value) from the outer loop to restrict the number of rows returned by inner cursor (maybe the player id?).



----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[vb89]

I'm not entirley sure if that's exactly what i want, is there another fix to this problem so that the proc outputs the correct number of rows without hardcoding a maximum value?

 

[jaxtell]

I'm guessing its not an infinite loop, just a very large loop. It looks like for every pitcher, display all stats for every pitcher. I think you want something for like for each pitcher, display that pitcher's stats. If thats the case, you can probably use one cursor instead of two. This can probably be cleaned up but it might work...

cursor konami_mlb_career_pit_crsr is
   select ytd.player_id_1032,
      ytd.player_id,
      ytd.moniker,
      ytd.last_name,
      sum(ytd.games_played),
      trunc(sum(ytd.outs_pitched) / 3) || '.' || mod(sum(ytd.outs_pitched), 3) as innings_pitched,
      sum(ytd.complete_games),
      sum(ytd.shut_outs),
      sum(ytd.wins),
      sum(ytd.losses),
      to_char((sum(ytd.wins)) / decode(sum(ytd.wins) + sum(ytd.losses), 0, 1, null, 1, sum(ytd.wins) + sum(ytd.losses)), '0d000') as winning_pct,
      trim(to_char( (9 * sum(ytd.earned_runs)) / (sum(ytd.outs_pitched) / 3) , '990d00')) as era,
      sum(ytd.strikeouts),
      sum(ytd.saves),
      sum(ytd.hits),
      sum(ytd.home_runs),
      sum(ytd.walks),
      sum(ytd.hit_batters),
      sum(ytd.earned_runs),
      sum(ytd.runs)
   from customer_data.cd_baseball_pit_ytd_stats ytd,  customer_data.cd_baseball_roster rost
   where 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 ytd.active_record != 'R'
     and ytd.player_id_1032 in
     (
    --Includes Players that were on the DL for the entire season                                           
      select player_id_1032
     from customer_data.cd_baseball_roster r
     where r.year_last = 2008
     and r.league_id = iLeagueID
     and r.player_id_1032 is not NULL
     )
     and exists (select player_id_1032
   from customer_data.cd_baseball_pit_ytd_stats table_a
   where split_number = -3
     and game_type_id = 1
     and sequence = 0
     and season_id = iSeasonID
     and table_a.player_id_1032 = ytd.player_id)
     group by ytd.player_id_1032,ytd.player_id, ytd.moniker,  ytd.last_name;

-----------------------------------------
I cannot be bought. Find leasing information at

http://www.joshaxtell.com/