CODEWITHSUNDEEP
sqloracle10g
user1325466
user1325466

Reputation: 1

Oracle SQL syntax error

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');

Upvotes: 0

Views: 179

Answers (1)

Mike Ryan
Mike Ryan

Reputation: 4374

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.)

Upvotes: 4

Related Questions