Error: ER_PARSE_ERROR: You have an error in your SQL syntax for a CREATE VIEW query

Question

I'm attempting to answer this question: Create a view of all patient details (patient, ward, drugs) for all patients who are 70 years old or older, excluding ward number and number of beds.

From this schema:

    Patient (patientNo, patName, patAddr, DOB)
    Ward (wardNo, wardName, wardType, noOfBeds)
    Contains (patientNo, wardNo, admissionDate)
    Drug (drugNo, drugName, costPerUnit)
    Prescribed (patientNo, drugNo, unitsPerDay, startDate, finishDate)

The query I have written is:

    CREATE VIEW [Patients Over Seventy] AS
    SELECT p.*, c.admissionDate, r.drugNo, r.unitsPerDay, r.startDate, r.finishDate
    FROM Patient p, Contain c, Prescribed r
    WHERE p.patientNo=c.patientNo AND p.DOB <=1952-05-31;

But I keep getting the error: "Query Error: Error: ER_PARSE_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 '[Patients Over Seventy] AS SELECT p.*, c.admissionDate, r.drugNo, r.unitsPerDay,' at line 1"

What am I getting wrong here?

Not sure about the syntax containing the [] characters, but try with CREATE VIEW Patients_Over_Seventy ... instead. — Paul T.
– Paul T., Commented Jun 2, 2022 at 2:24
@PaulT. Just gave that a go, and the same error is still coming up sadly — Chelsea
– Chelsea, Commented Jun 2, 2022 at 3:32
Try checking the SQL query itself runs without the CREATE VIEW name AS. If not try enclosing the field names with double quotes. — Keith John Hutchison
– Keith John Hutchison, Commented Jun 2, 2022 at 7:00

Aleksandr Latman · Accepted Answer · 2022-06-02 06:12:48Z

0

I see several issues.

Table name in the schema Contains, in the view - Contain
There is no condition to join Prescribed table to the other two tables
What is DOB data type? If it's char, the comparison should be p.DOB <='1952-05-31', if it's date, you need to use SQL CONVERT function

edited Jun 2, 2022 at 6:12

answered Jun 2, 2022 at 6:12

Aleksandr Latman

13 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Error: ER_PARSE_ERROR: You have an error in your SQL syntax for a CREATE VIEW query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related