Error in MySQL Syntax for a Case Statement

91 views Asked by At

I'm working on a baseball projections system, and I'm trying to have two different formulas based on games started.

This is my current error:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(ROUND( COALESCE(IP1,0) * .5 + COALESCE(IP2,0) * .1 + 60 )) D( COALESCE(IP1,0) *' at line 6

And this is what my code looks like:

CREATE TABLE player_ip_2015
AS 
SELECT playerID
    ,CASE
        WHEN (b.GS > 5)
            THEN (ROUND( COALESCE(IP1,0) * .5 + COALESCE(IP2,0) * .1 + 60 ))
            ELSE (ROUND( COALESCE(IP1,0) * .5 + COALESCE(IP2,0) * .1 + 20 ))
            END
            AS IP
FROM (SELECT * FROM (SELECT DISTINCT playerID FROM pitching_pos) b
        LEFT JOIN (SELECT playerID AS playerID1, (IPOuts/3) AS IP1 FROM pitching_pos
                WHERE yearID = "2014") b1
                ON b.playerID = b1.playerID1
        LEFT JOIN (SELECT playerID AS playerID2, (IPOuts/3) AS IP2 FROM pitching_pos
                WHERE yearID = "2013") b2
                ON b.playerID = b2.playerID2) b
GROUP BY playerID
ORDER BY IP DESC;
2

There are 2 answers

0
Gonzalo.- On

You're opening a ( before Case and you're never closing it you should remove the ( anyway (or close it after End

5
AudioBubble On

What is the value of IP1? Secondly, your title to the question does not match the error you are reporting. Thirdly, you also have an extra ( in the FROM section of the statement.

Check your code.