On Thu, Jun 02, 2011 at 03:15:03PM +0200, Martin Kersten wrote:

On 6/2/11 3:02 PM, Lowterm wrote:

...

Hi,

what's the meaning of this syntax error?

Syntax error, unexpected CREATE, expecting SCOLON in: "create ..."

This suggests that the previous SQL statement in your .sql file was not properly terminated with a SemiCOLON ";".

...

I get this message with the "create table" if I load a Files with "sql>\< filename", which contains this statement. The create-statement works, if I insert it directly.

That is not correct, because the syntax is wrong (!) ON UPDATE CURRENT_TIMESTAMP is not supported by SQL2003.

see the reference manual: http://www.monetdb.org/Documentation/Manuals/SQLreference/Tables

...

The second error message ist:

syntax error, unexpected ON, expecting ')' or ',' in: "create ..." in the following create statement:

CREATE TABLE dim_warehouse ( is_current smallint NOT NULL, last_modified timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP )

but I can not see wath is wrong.

Thanks

------------------------------------------------------------------------------ Simplify data backup and recovery for your virtual environment with vRanger. Installation's a snap, and flexible recovery options mean your data is safe, secure and there when you need it. Data protection magic? Nope - It's vRanger. Get your free trial download today. http://p.sf.net/sfu/quest-sfdev2dev _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

-- | Stefan.Manegold @ CWI.nl | DB Architectures (INS1) | | http://CWI.nl/~manegold/ | Science Park 123 (L321) | | Tel.: +31 (0)20 592-4212 | 1098 XG Amsterdam (NL) |

On 6/2/11 4:37 PM, Lowterm wrote:

Hi Martin,

thanks for you reply. How I see, there are many SQL statements that are not supported by MonetDB. I am Student and I would like compare MonetDB with other databases, but I

Yes, the work of a student calls for reading the background material and not assuming that two SQL database systems are the same. http://www.monetdb.org/Documentation/Manuals/SQLreference/Features

have massive problems alone with changes the SQL statements. I have for example the following Greate Table statement:

CREATE TABLE dim_customer ( customer_key int NOT NULL, customer_id int NOT NULL DEFAULT '0', customer_first_name varchar(35) NOT NULL DEFAULT 'not applicable', customer_middle_initial char(1) DEFAULT '', customer_last_name varchar(30) NOT NULL DEFAULT 'not applicable', customer_address1 varchar(50) NOT NULL DEFAULT 'not applicable', customer_address2 varchar(50) DEFAULT 'not applicable', customer_postal_code varchar(10) NOT NULL DEFAULT 'N/A', customer_city_name varchar(64) NOT NULL DEFAULT 'not applicable', customer_region_code char(2) NOT NULL DEFAULT 'NA', customer_region_name varchar(50) NOT NULL DEFAULT 'not applicable', customer_country_code char(2) NOT NULL DEFAULT 'NA', customer_country_name varchar(50) NOT NULL DEFAULT 'not applicable', customer_email_address varchar(64) NOT NULL DEFAULT 'not applicable', customer_telephone_number char(12) NOT NULL DEFAULT 'N/A', customer_date_of_birth date NOT NULL DEFAULT '1900-01-01', customer_website_name varchar(50) NOT NULL DEFAULT 'not applicable', demography_key smallint NOT NULL DEFAULT '0', valid_from date NOT NULL DEFAULT '1900-01-01', valid_to date NOT NULL DEFAULT '2199-01-01', version int NOT NULL DEFAULT '0', current_record int NOT NULL DEFAULT '0',

last_modified timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

customer_date_registered date NOT NULL DEFAULT '1900-01-01', customer_date_unregistered date NOT NULL DEFAULT '2100-01-01', customer_balance decimal(6,2) NOT NULL DEFAULT '0.00', PRIMARY KEY (customer_key)

KEY date_registered (customer_date_registered,customer_website_name), KEY date_unregistered (customer_date_unregistered,customer_website_name), KEY demography (demography_key), KEY cust_id (customer_id) )

and I have problem with "ON UPDATE" and "KEY" statements and I can't find any good documentation about solution of such problems. I am grateful for your links, but it can't help me because such problems can not be treated there.

Thanks

Dear Lowterm, a final polite attempt from our side to provide some constructive help: As far as we know and Martin already pointed out, the SQL:2003 standard, and hence MonetDB, which in contrast to some other RDBMSs tries to closely follow the SQL:2003 standard, does neither support implicit trigger definitions nor "KEY" definitions other than primary and foreign keys. For explicit trigger definition see our documentation at http://www.monetdb.org/Documentation/Manuals/SQLreference/Triggers . For constraint definitions other than primary and foreign keys see our documentation at http://www.monetdb.org/Documentation/Manuals/SQLreference/TableConstraints In addition you are more than welcome to use your favorite documentation of the SQL:2003 standard. Kind regards, Stefan On Thu, Jun 02, 2011 at 07:59:35AM -0700, Lowterm wrote:

This is very interesting that you send me back again a link, instead of giving me a resonable response. I have no desire or time for solution of a problem, why you are there, to read for hours on your unhelpful documentation.

-- View this message in context: http://old.nabble.com/Problem-with-Create-Tables-tp31757411p31758355.html Sent from the monetdb-users mailing list archive at Nabble.com.

------------------------------------------------------------------------------ Simplify data backup and recovery for your virtual environment with vRanger. Installation's a snap, and flexible recovery options mean your data is safe, secure and there when you need it. Data protection magic? Nope - It's vRanger. Get your free trial download today. http://p.sf.net/sfu/quest-sfdev2dev _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

-- | Stefan.Manegold @ CWI.nl | DB Architectures (INS1) | | http://CWI.nl/~manegold/ | Science Park 123 (L321) | | Tel.: +31 (0)20 592-4212 | 1098 XG Amsterdam (NL) |