#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    4
    Rep Power
    0

    Syntax Error Missing Operator


    I have the following code meant to give me the counts of certain data codes and I am using a simple CASE WHEN to do the counts but I keep getting a syntax error Near the word THEN and I am embarassed I cannot figure it out. THe code is

    SELECT ID_NR, DEG_CD, CASE WHEN( DEG_CD IN ('20','25') THEN 1 ELSE 0 END) AS Associates
    FROM dbo.ACCT
    GROUP BY ID_NR, DEG_CD
  2. #2
  3. Did you steal it?
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    13,965
    Rep Power
    9397
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    4
    Rep Power
    0
    Originally Posted by requinix
    Look at your parentheses.
    What about them
  6. #4
  7. Did you steal it?
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    13,965
    Rep Power
    9397
    Here is the syntax for a CASE expression.
    Code:
    CASE value WHEN [compare_value] THEN result [WHEN [compare_value] THEN result ...] [ELSE result] END
    Note that it doesn't include any parentheses. That means yours have no additional meaning beyond the normal "group these things together".


    Code:
    CASE WHEN( DEG_CD IN ('20','25') THEN 1 ELSE 0 END)
    After the WHEN is the condition. You used parentheses so MySQL sees it as
    Code:
    CASE WHEN(condition)
    That doesn't obey the syntax. As for the condition itself,
    Code:
    DEG_CD IN ('20','25') THEN 1 ELSE 0 END
    that doesn't correspond to anything MySQL supports. Why is there a "THEN"? What are the "ELSE" and "END" doing? It doesn't know.

    Your parentheses are messing it up.
  8. #5
  9. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,120
    Rep Power
    4274
    Originally Posted by requinix
    As for the condition itself...
    ... that doesn't correspond to anything MySQL supports.
    great explanation of the parentheses messing it up, but you messed up on this point

    that CASE is perfectly valid

    THEN 1 ELSE 0 END obviously sets up the CASE expression as a binary tinyint to indicate which rows have either a 20 or 25 in DEG_CD

    all quite normal

    if i were to guess, i would say DEG_CD is some kind of degree code and people who have earned a degree code of 20 or 25 are called associates
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  10. #6
  11. Did you steal it?
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    13,965
    Rep Power
    9397
    Originally Posted by r937
    great explanation of the parentheses messing it up, but you messed up on this point

    that CASE is perfectly valid
    How? The CASE is missing a THEN...END and for the stuff inside there's a THEN in the middle of nowhere. Neither is valid.
  12. #7
  13. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,120
    Rep Power
    4274
    Originally Posted by requinix
    How? The CASE is missing a THEN...END and for the stuff inside there's a THEN in the middle of nowhere. Neither is valid.
    obviously, look at it without the parentheses!!
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  14. #8
  15. Did you steal it?
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    13,965
    Rep Power
    9397
    Originally Posted by r937
    obviously, look at it without the parentheses!!
    But you can't just ignore them! They're the problem!

    Okay yeah, what OP was trying to do is totally right, I see what you're getting at. It's just... it's not right until those parentheses go away. Which is easy to do, I know. Just saying.
    Yeah.
  16. #9
  17. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,120
    Rep Power
    4274
    Originally Posted by requinix
    Yeah.
    yeah, me too
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo