CODEWITHSUNDEEP
sqlmysql
Yunus Eren Güzel
Yunus Eren Güzel

Reputation: 3088

How to end a CASE statement?

SELECT E.id_employee,E.name,E.age,E.wage,D.name,
    CASE (SELECT COUNT(*) FROM manages M 
        WHERE M.id_employee=E.id_employee) 
        WHEN 1 THEN 'Chief' 
        WHEN 0 THEN '-'
    END CASE
FROM Employee E 
INNER JOIN work_in 
INNER JOIN Department D

it gives the error:

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 FROM Employee E INNER JOIN work_in INNER JOIN Department D' at line 5

What am I missing here?

Upvotes: 3

Views: 155

Answers (6)

Johan
Johan

Reputation: 76641

Change it to:

"SELECT E.id_employee,E.name,E.age,E.wage,D.name,
  CASE (SELECT COUNT(*) FROM manages M WHERE M.id_employee=E.id_employee)
    WHEN 1 THEN 'Chief'                     
    WHEN 0 THEN '-'                 
  END              
FROM Employee E 
INNER JOIN work_in w ON (w.id = e.work_id)
INNER JOIN Department D ON (D.id = e.department_id)"

Warning
If you do an inner join with no condition(s), it will actually transform into a full outer join!
This means if you run the following query:

SELECT * FROM a
INNER JOIN b
INNER JOIN c

where table a, b and c each have 100 rows, you will get a grand total of 1,000,000 rows in your resultset, listing every possible combination of a, b and c.
In 99,999% of the cases this is definitely not what you'd want.

Upvotes: 1

Stefan Gehrig
Stefan Gehrig

Reputation: 83632

You're mixing up the syntax of the CASE Statement and the CASE expression. While the first one is used in stored procedures, the second one is the one used in SQL statements.

CASE expression

SELECT CASE 1 WHEN 1 THEN 'one' WHEN 2 THEN 'two' ELSE 'more' END;

CASE statement

CASE case_value
    WHEN when_value THEN statement_list
    [WHEN when_value THEN statement_list] ...
    [ELSE statement_list]
END CASE

Or as the manual puts it

Note

The syntax of the CASE statement used inside stored programs differs slightly from that of the SQL CASE expression described in Section 11.4, “Control Flow Functions”. The CASE statement cannot have an ELSE NULL clause, and it is terminated with END CASE instead of END.

So in your case there is no CASE after the END.

Upvotes: 2

dawebber
dawebber

Reputation: 3553

You are missing ON condition for the inner join or USING() clause. 

SELECT E.id_employee,E.name,E.age,E.wage,D.name,
                CASE (SELECT COUNT(*) FROM manages M WHERE M.id_employee=E.id_employee) 
                    WHEN 1 THEN 'Chief' 
                    WHEN 0 THEN '-'
                END
            FROM Employee E 
               INNER JOIN work_in w
                  ON e.id_employee=w.id.employee
               INNER JOIN Department D
                  ON w.id_department=D.id_department

Upvotes: 1

Ron Weston
Ron Weston

Reputation: 290

Remove the last CASE keyword, after the END

Upvotes: 4

g.d.d.c
g.d.d.c

Reputation: 47988

Remove the word CASE from after END.

Upvotes: 3

Cyril Gandon
Cyril Gandon

Reputation: 17058

There is no 'CASE' after 'END'

SELECT E.id_employee,E.name,E.age,E.wage,D.name,
                CASE (SELECT COUNT(*) FROM manages M WHERE M.id_employee=E.id_employee) 
                    WHEN 1 THEN 'Chief' 
                    WHEN 0 THEN '-'
                END
FROM Employee E INNER JOIN work_in INNER JOIN Department D

Upvotes: 3

Related Questions