2

I'm creating a SQL query with a some nested queries and I'm trying to use the CASE statement but it is behaving weirdly. I've been at it for at least 6 hours without any luck...

This is my query at the moment:

select t.fpl_id, t.team_name,
sum(pf.points)as gwpts,
(
    select sum(transfers_malus)
    from gameweeks
    where gameweeks.team_id = t.id and gameweeks.number = g.number
)as malus,
(
    select sum(points)
    from player_fixtures as pfix
    where gw_number = g.number and pfix.player_id = CASE WHEN minutes_played > 0 THEN g.captain_id ELSE g.vice_captain_id END
)
as cpts,
 (
    select max(web_name)
    from players
    join player_fixtures on players.id = player_fixtures.player_id and gw_number = g.number
    where players.id = CASE WHEN player_fixtures.minutes_played > 0 THEN g.captain_id ELSE g.vice_captain_id END
) as captain_name,
array_agg(p.id) as lineup
from teams as t
join gameweeks as g on t.id  = g.team_id
join gameweeks_players as gp on gp.gameweek_id = g.id
join players as p on gp.player_id = p.id
join player_fixtures as pf on p.id = pf.player_id  and pf.gw_number = g.number
where t.id = 1
group by t.id, g.id
order by g.number asc

he bit where I have a problem is when I'm doing the case statement to see if one of the player ( the captain ) hasn't played based on "minutes_played":

(select sum(points)
    from player_fixtures as pfix
    where gw_number = g.number and pfix.player_id = CASE WHEN pfix.minutes_played > 0 THEN g.captain_id ELSE g.vice_captain_id END
) as cpts

and

(
    select max(web_name)
    from players
    join player_fixtures on players.id = player_fixtures.player_id and gw_number = g.number
    where players.id = CASE WHEN player_fixtures.minutes_played > 0 THEN g.captain_id ELSE g.vice_captain_id END
) as captain_name,

My expected result would be that if minutes_played is greater than 0 then return the row that matches gw_number = g.number and player_id = g.captain_id, otherwise return the row that matches gw_number = g.number and player_id = g.vice_captain_id

When minutes_played is indeed greater than 0, everything works fine. However, when it is equal to 0 it doesn't return any row and thus I get a NULL in the row of my overall query.

The funny thing is that if I try with the following script on the player_fixtures where minutes_played = 0 my greater than evaluation works and I get the correct "BAD".

DO LANGUAGE plpgsql $$
    BEGIN
        IF (select minutes_played from player_fixtures where gw_number = 19 and player_id = 266 ) > 0 THEN 
            RAISE NOTICE 'GOOD';
        ELSE
            RAISE NOTICE 'BAD';
        END IF;
    END;
$$;

I'm pretty new at this whole DB thing so I'm probably making a rookie mistake but after trying to solve it myself for 6hours, I think it is time I ask for help. Could someone point me in the right direction?

Thanks!

EDIT with more info following @Mihai answer:

when I cover the 0 condition it still return 0 row. I tried with the following test data:

select *
from player_fixtures
where gw_number = 19 and player_id = case when player_fixtures.minutes_played > 0 then 266 when player_fixtures.minutes_played = 0 then 214 end

If I run

select *
from player_fixtures
where gw_number = 19 and player_id in (266,214)

I get 2 rows with the correct data so there must be a problem when I do the case statement...

6
  • Maybe try it with minutes_played IS NULL
    – Mihai
    Commented Jan 3, 2014 at 14:06
  • @Mihai doesn't work either...
    – Syl
    Commented Jan 3, 2014 at 14:08
  • Any way you could set up a table and test data on sqlfiddle.com? I'd love to play around with it.
    – Joe Love
    Commented Jan 3, 2014 at 15:03
  • when debugging problems like this, try moving the case statement out of the where clause and into the select list as a returned boolean field. the problem is probably in how your filtering the data
    – user32365
    Commented Jan 3, 2014 at 15:28
  • @JoeLove I tried but I keep getting a "Your schema ddl is too large (more than 8000 characters). Please submit a smaller DDL." error even though I'm trying to use the minimum amount of data to test it with just player_fixtures :/
    – Syl
    Commented Jan 3, 2014 at 16:23

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.