SQL- missing comma in insert statement line-CodePudding

CREATE TABLE suppliers
(
    supplier_ID      NUMBER not null,
    supplier_Name    varchar2 (50) not null,
    supplier_Addr    varchar2 (100),
    supplier_Town    varchar2 (30),
    supplier_State   varchar2 (30),
    supplier_Pcode   varchar2 (15) not null,
    supplier_Phone1  varchar2 (15),
    supplier_Phone2  varchar2 (15),
    supplier_Contact varchar2 (50),
    supplier_Fax     varchar2 (15),
    supplier_Email   varchar2 (40),
    supplier_Renew   DATE,
    supplier_Creditlimit NUMBER,
    supplier_Comments varchar2 (500),
    PRIMARY KEY (supplier_ID)
);

INSERT INTO suppliers (supplier_ID, supplier_Name, supplier_Addr, supplier_Town, supplier_State, supplier_Pcode, supplier_Phone1, supplier_Phone2, supplier_Contact, supplier_Fax, supplier_Email, supplier_Renew, supplier_Creditlimit,supplier_Comments)
VALUES (010203, 'DSHK COMPANY', 'G 7 JLN TUNKU HASSAN ', 'Seremban', 'Negeri Sembilan', 70000, 604-42449268, 09-4265050, 'DSHK CO', 04-2224568, '[email protected]', 2024-08-23, 1000, 'COMPANY WILL CALL BEFORE SENDING THE GOODS');

I keep getting missing comma error for insert statement. Please guide me, tq

CodePudding user response：

You need to make the phone numbers into strings as you have defined them as varchar.

70000,604-42449268,09-4265050

'70000', '604-42449268', '09-4265050'

I think you need to do tha same for date... that is 2024-08-23 -> '2024-08-23'

CodePudding user response：

Assuming that you are using an Oracle database since you are using VARCHAR2 data types then use string and date literals for VARCHAR2 and DATE data types respectively:

insert into suppliers (
  supplier_ID,
  supplier_Name,
  supplier_Addr,
  supplier_Town,
  supplier_State,
  supplier_Pcode,
  supplier_Phone1,
  supplier_Phone2,
  supplier_Contact,
  supplier_Fax,
  supplier_Email,
  supplier_Renew,
  supplier_Creditlimit,
  supplier_Comments
) values (
  010203,
  'DSHK COMPANY',
  'G 7 JLN TUNKU HASSAN ',
  'Seremban',
  'Negeri Sembilan',
  '70000',
  '604-42449268',
  '09-4265050',
  'DSHK CO',
  '04-2224568',
  '[email protected]',
  DATE '2024-08-23',
  1000,
  'COMPANY WILL CALL BEFORE SENDING THE GOODS'
);

If you don't then, for example, 604-42449268 will be parsed as 604 subtract 42449268 which gives the result −42448664.

Do not use a string literal such as '2024-08-23' for the date as Oracle will implicitly try to convert the string literal to a date using the NLS_DATE_FORMAT session paramter. The default Oracle DATE format depends on the territory you are in and for most territories that implicit conversion will fail. Either use a DATE literal or explicitly call TO_DATE('2024-08-23', 'YYYY-MM-DD') with a format model.

db<>fiddle here

CodePudding user response：

Regarding the create statement: NUMBER is not a valid type. You can use e.g. int(11) unsigned.

Also, instead of varchara(500) you can use text. So the create statement should be:

CREATE TABLE suppliers
(
    supplier_ID int(11) not null, 
    supplier_Name varchar (50) not null, 
    supplier_Addr varchar (100), 
    supplier_Town varchar (30), 
    supplier_State varchar (30), 
    supplier_Pcode varchar (15) not null, 
    supplier_Phone1 varchar (15), 
    supplier_Phone2 varchar (15), 
    supplier_Contact varchar (50), 
    supplier_Fax varchar (15), 
    supplier_Email varchar (40), 
    supplier_Renew DATE, 
    supplier_Creditlimit int(11), 
    supplier_Comments text, 
    PRIMARY KEY (supplier_ID) 
);

For your insert statement you must enclose all non-numerical values in quotes, like this:

INSERT INTO suppliers (supplier_ID, supplier_Name, supplier_Addr, supplier_Town, supplier_State, supplier_Pcode, supplier_Phone1, supplier_Phone2, supplier_Contact, supplier_Fax, supplier_Email, supplier_Renew, supplier_Creditlimit,supplier_Comments) 
VALUES (010203, 'DSHK COMPANY', 'G 7 JLN TUNKU HASSAN ', 'Seremban', 'Negeri Sembilan', 70000, '604-42449268', '09-4265050', 'DSHK CO', '04-2224568', '[email protected]', '2024-08-23', 1000, 'COMPANY WILL CALL BEFORE SENDING THE GOODS');