I have the following query, every time i try to run it i get the following error: "missing keyword". The output doesn't really matter in this instance, I'm just curious to see if anyone can find a issue with my syntax. When I try to run the code it points to the line that I specify below (where the first case statement is)...
select substr(x, 1, instr(x, ',', -1) - 1) as game_stats,
game_code,
game_date
from
(
select
(case when event_code = 17 then
team_name || ': ' || (xmlagg(xmlelement(e, offensive_player_moniker || ' ' || offensive_player_last_name || ' (' || to_char(time) || ' pen), ')).extract('//text()')) x <--Error points to here when I try to run the query
when event_code = 11 then
team_name || ': ' || (xmlagg(xmlelement(e, offensive_player_moniker || ' ' || offensive_player_last_name || ' (' || to_char(time) || '), ')).extract('//text()')) x
end) as game_stats,
event_code,
game_code,
game_date,
time
from
(
select
(
case
when g.home_team_id = e.team_id then g.home_team_name
when g.away_team_id = e.team_id then g.away_team_name
end) as team_name,
e.offensive_player_moniker,
e.offensive_player_last_name,
e.time time,
e.game_code game_code,
g.game_date as game_date,
e.event_code_num as event_code
--Bulk Collect into player_results
from
customer_data.cd_soccer_events e,
customer_data.cd_soccer_games g
where g.season_id = 200839
-- and e.game_code = 823921
and e.event_code_num in (11,17)
and e.game_code = g.game_code
and e.season_id = g.season_id
and g.game_date >= sysdate - 5 --Delete later
group by
g.home_team_id,
g.away_team_id,
e.team_id ,
g.home_team_name,
g.away_team_name,
e.offensive_player_moniker,
e.offensive_player_last_name,
e.time,
e.game_code,
g.game_date,
e.event_code_num
order by time asc
)
group by team_name, game_code, game_date
--order by game_date
)
**Note: Before i inserted the first case statment, the one specified above the code worked fine, so that's where the issue has to be.