Oracle SQL syntax error

Question

I created this table:

CREATE TABLE diagnosis(
  diag_id NUMBER(8),
  SCT_CODE CHAR(10),
  diag_date DATE NOT NULL,
  GP_ID CHAR(5),
  PHN CHAR(5),
  Rx CHAR(3),
  CONSTRAINT diagnosis_PK PRIMARY KEY (diag_id, SCT_CODE),
  CONSTRAINT diagnosis_FK4 FOREIGN KEY (SCT_CODE)
    REFERENCES symptom (SCT_CODE) ON DELETE CASCADE,
  CONSTRAINT diagnosis_FK1 FOREIGN KEY (GP_ID)
    REFERENCES GP (GP_ID) ON DELETE CASCADE,
  CONSTRAINT diagnosis_FK2 FOREIGN KEY (PHN)
    REFERENCES Patient (PHN) ON DELETE CASCADE,
  CONSTRAINT diagnosis_FK3 FOREIGN KEY (Rx)
    REFERENCES drug (Rx)
  ON DELETE CASCADE);

I have tried both of these methods to populate the tables and it is not working. Please help what is wrong with my syntax

INSERT ALL
  INTO DIAGNOSIS VALUES ('10000001', '38341003', '04-APR-12', ‘GP001’, ‘p1001’, ‘Rx1’)
  INTO DIAGNOSIS VALUES ('10000002', '423430007', '07-APR-12', ‘GP001’, ‘p1001’, ‘Rx1’)
  INTO DIAGNOSIS VALUES ('10000003', '371032004', '05-APR-12', ‘GP002’, ‘p1002’, ‘Rx2’)
  INTO DIAGNOSIS VALUES ('10000004', '38341003', '08-APR-12', ‘GP003’, ‘p1003’, ‘Rx1’)
  INTO DIAGNOSIS VALUES ('10000005', '73211009', '07-APR-12', ‘GP003’, ‘p1003’, ‘Rx3’)
  INTO DIAGNOSIS VALUES ('10000006', '73211009', '06-APR-12', ‘GP004’, ‘p1004’, ‘Rx3’)
  INTO DIAGNOSIS VALUES ('10000007', '38341003', '10-APR-12', ‘GP001’, ‘p1005’, ‘Rx1’)
  SELECT * FROM DUAL;

/* Populate Diagnosis Table */

INSERT INTO Diagnosis(diag_id, SCT_CODE, diag_date, GP_ID, PHN, Rx)
  VALUES ('10000001', ‘38341003’, '04-Apr-12', 'GP001', 'p1001', 'Rx1');
INSERT INTO Diagnosis(diag_id, SCT_CODE, diag_date, GP_ID, PHN, Rx)
  VALUES ('10000002', ‘423430007’, '07-Apr-12', 'GP001', 'p1001', 'Rx1');
INSERT INTO Diagnosis(diag_id, SCT_CODE, diag_date, GP_ID, PHN, Rx)
  VALUES ('10000003', ‘371032004’, '05-Apr-12', 'GP003', 'p1002', 'Rx2');
INSERT INTO Diagnosis(diag_id, SCT_CODE, diag_date, GP_ID, PHN, Rx)
  VALUES ('10000004', ‘38341003’, '08-Apr-12', 'GP003', 'p1003', 'Rx1');
INSERT INTO Diagnosis(diag_id, SCT_CODE, diag_date, GP_ID, PHN, Rx)
  VALUES ('10000005’, ‘73211009’, '07-Apr-12', 'GP003', 'p1003', 'Rx3');
INSERT INTO Diagnosis(diag_id, SCT_CODE, diag_date, GP_ID, PHN, Rx)
  VALUES ('10000006', ‘73211009’, '06-Apr-12', 'GP004', 'p1004', 'Rx3');
INSERT INTO Diagnosis(diag_id, SCT_CODE, diag_date, GP_ID, PHN, Rx)
  VALUES ('10000006', ‘38341003’, '10-Apr-12', 'GP001', 'p1005', 'Rx1');

Welcome to StackOverflow. Please don't use words like "urgent" and "immediate" in your questions. Your question, while it's very important to you, is no more urgent or important than those posted by others. If your question is urgent and can't wait, hire a consultant to get immediate attention. Otherwise, ask it here without the drama and pleading, and people will help you as soon as they can. Also, if you want help, you need to clearly state the problem. You said "syntax help", but don't provide any error messages that would help people help you, wasting time while people ask you for details. — Ken White
– Ken White, Commented Apr 11, 2012 at 2:26
If this was a cut and paste (before the formatting), then note the wrong quote characters messing things up (ex: ‘GP001’ rather than 'GP001') — Glenn
– Glenn, Commented Apr 11, 2012 at 2:27

Mike Ryan · Accepted Answer · 2012-04-11 13:21:45Z

4

As commenter (@Glenn) says, your problem is most likely a very inconsistent use of single quotes.

Both of these work:

INSERT ALL
  INTO DIAGNOSIS VALUES 
      ('10000001', '38341003', '04-APR-12', 'GP001', 'p1001', 'Rx1')
  INTO DIAGNOSIS VALUES 
      ('10000002', '423430007', '07-APR-12', 'GP001', 'p1001', 'Rx1')
SELECT * FROM DUAL;

INSERT INTO Diagnosis(diag_id, SCT_CODE, diag_date, GP_ID, PHN, Rx)
  VALUES ('10000003', '371032004', '05-Apr-12', 'GP003', 'p1002', 'Rx2');
INSERT INTO Diagnosis(diag_id, SCT_CODE, diag_date, GP_ID, PHN, Rx)
  VALUES ('10000004', '38341003', '08-Apr-12', 'GP003', 'p1003', 'Rx1');

where all I've done and gone through and made sure that ' is always used.

See this sqlfiddle: http://sqlfiddle.com/#!4/c824d/1 (where I've removed the constraints because I don't want to type all that.)

Though, personally I've always disliked the first method because that select * from dual seems so arbitrary (yes, I know it works -- it's more of an aesthetic issue.)

edited Apr 11, 2012 at 13:21

answered Apr 11, 2012 at 3:17

Mike Ryan

4,3841 gold badge21 silver badges23 bronze badges

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

2 Comments

user1325466 Over a year ago

Thanks for the help it was a missing quote.

Mike Ryan Over a year ago

You're welcome -- now, in StackOverflow, the way to say thanks is to mark the answer as helpful and/or accept the answer. (Which, if I could share w/ @Glenn I would.)

Collectives™ on Stack Overflow

Oracle SQL syntax error

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related