Inserting values into a table 1

[Nixx123]

Hi
Please can get a pointer on the following error message
when executing this SQL Query on Pervasive SQL 2000I

INSERT INTO "tblPCW"
(PCW,PCW DESCRIPTION,PCW Cat)
VALUES (0,BANK,' ',10,FX TRADING,20,LOCAL MM,30,CASH,)
________________________________________________________

The error message received is as follows :
//ODBC Error: SQLSTATE = 37000, Native error code = 0
Syntax Error: INSERT INTO tblPCW
(PCW,PCW DESCRIPTION<< ??? >>,PCW Cat)
VALUES (0,BANK,' ',10,FX TRADING,20,LOCAL MM,30,CASH,)
Some error(s) encountered while executing SQL statement(script).//

I am not sure if this is CONVERSION error ,Doubt it since i am not trying to convert anything

The Data Types for these Columns are
numeric , longvarchar & longvarchar
Default Size for the 2 longvarchar data types
The numeric data type column has a Prec(Precision) of 15 &Scale of 8
Am i perhaps missing something ??
Thanks in Advance !

 

[mirtheil]

I see several things that might cause problems.
First, if the column name is "PCW DESCRIPTION", it needs to be in double quotes because of the space.
Second, you've got more values than columns in your INSERT.
Third, you've got a string value not enclosed in single quotes.
Fourth, you've got a comma but no value following it.
Here's a statement that should work:

INSERT INTO "tblPCW"
(PCW,"PCW DESCRIPTION","PCW Cat")
VALUES (0,'description','cat')

Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician

http://www.mirtheil.com

 

[Nixx123]

Thanks Noted

Question though the reason that there are more values than Columns is because i am trying to run a multiple insert on the table

So syntax should read follows :

INSERT INTO "tblPCW"
(PCW,"PCW DESCRIPTION","PCW Cat")
VALUES ('0','BANK',' ',
'10','FX TRADING',''
,'20','LOCAL MM', ' '
,'30','CASH', ' ')
__________________________________________________________
The [,' ',] if you are wondering what is happening here is i am trying to insert a Null Value because the column should be empty and able to accept Null Values

 

[mirtheil]

I'm not sure PSQL supports multiple values on insert. Especially PSQL 2000i. I can't find any documentation that says that it does and a quick test returns an error anout the insert list not matching.
Also, [''] (an empty string) and null are two different things. Some older versions of PSQL didn't fully support null and used an empty string as null so be careful.

Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician

http://www.mirtheil.com

 

[Nixx123]

Thanks !!
Noted

I will create separate *.SQL files then,i used your corrected syntax for my query and it worked , also i appreciate the mistakes you pointed out in my code , i definitely will watch the Spaces in the Column Names

Kind regards