Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2013
    Posts
    2

    Question Unanswered: Mysql Query with Case

    Hello guys,

    look I'm a beginner in the world of DBS, that means I'm an student so I'm dealing with a query which is giving me an error so I'd like some help in order to see what my mistake is at, here is the query and the result when I execute it:

    [SQL]SELECT
    C.CompanyName AS Cliente,
    C.City AS Ciudad,
    P.ProductName AS Producto,
    C.Country AS Pais,
    Sum(OD.Quantity*OD.UnitPrice) AS Compra,
    CASE
    WHEN SUM(OD.Quantity*OD.UnitPrice)>=300 THEN "Fiel"
    WHEN SUM(OD.Quantity*OD.UnitPrice)>=100 THEN "Potencial"
    WHEN SUM(OD.Quantity*OD.UnitPrice)<100 THEN "Simpatizante"
    END CASE AS Cliente_tipo
    FROM
    customers AS C
    INNER JOIN orders AS O ON O.CustomerID = C.CustomerID
    INNER JOIN `order details` AS OD ON OD.OrderID = O.OrderID
    INNER JOIN products AS P ON OD.ProductID = P.ProductID
    WHERE
    C.Country IN ('Mexico', 'Argentina', 'Canada', 'Brazil', 'USA', 'Venezuela')
    GROUP BY
    C.CompanyName,
    C.City,
    P.ProductName,
    C.Country

    [Err] 1064 - 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 'CASE AS Cliente_tipo
    FROM
    customers AS C
    INNER JOIN orders AS O ON O.Customer' at line 11
    Any help would be really appreciated!

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Change "END CASE" to "END"
    George
    Home | Blog

  3. #3
    Join Date
    Aug 2013
    Posts
    2
    Quote Originally Posted by gvee View Post
    Change "END CASE" to "END"
    nice!! it worked! many thanks!
    Could you please explain me why? (so I can understand what I was doing wrong and won't do the same in the near future.

    Thanks a lot.

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I think that you may confuse the CASE expression and the CASE statement.

    MySQL :: MySQL 5.6 Reference Manual :: 12.4 Control Flow Functions

    Please see Note in 12.4 Control Flow Functions
    Note
    The syntax of the CASE expression shown here differs slightly from that of the SQL CASE statement described in
    Section 13.6.5.1, “CASE Syntax”, for use inside stored programs.
    The CASE statement cannot have an ELSE NULL clause, and it is terminated with END CASE instead of END.
    MySQL :: MySQL 5.6 Reference Manual :: 13.6.5.1 CASE Syntax

    Also see Note in 13.6.5.1 CASE Syntax
    Note
    There is also a CASE expression, which differs from the CASE statement described here.
    See Section 12.4, “Control Flow Functions”.
    The CASE statement cannot have an ELSE NULL clause, and it is terminated with END CASE instead of END.
    Last edited by tonkuma; 08-03-13 at 16:48.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •