7 Replies - 3215 Views - Last Post: 26 January 2012 - 11:54 AM

#1 antikythera  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 3
  • Joined: 23-January 12

Why am I missing a right parenthesis (trying to make check it with cur

Posted 24 January 2012 - 12:50 AM

Hey I'm new to the site and I would first, before I ask for any help is to thank everyone in advance. You have my word that once I finish my schooling and become more proficient I will answer as many questions as possible to help the people who need it and to the people who need a break from question answering. I'll get to my question;

CREATE TABLE Booking
(hotelNo	CHAR(4) NOT NULL
,guestNo	CHAR(8)	NOT NULL
,dateFrom	DATE	NOT NULL
,dateTo	 DATE	NOT NULL
,roomNo	 CHAR(3) NOT NULL
,CONSTRAINT chk_roomNO
CHECK (roomNo BETWEEN 1 AND 100)
,CONSTRAINT chk_dateFrom
CHECK	(dateTo > SYSDATE())
,CONSTRAINT chk_dateTo
CHECK (dateTo < dateFrom)
);



why am I getting an error that im missing a right parenthesis (CHECK (dateTo > SYSDATE())).

Is This A Good Question/Topic? 0
  • +

Replies To: Why am I missing a right parenthesis (trying to make check it with cur

#2 nK0de  Icon User is offline

  • Catch me As Exception
  • member icon

Reputation: 205
  • View blog
  • Posts: 823
  • Joined: 21-December 11

Re: Why am I missing a right parenthesis (trying to make check it with cur

Posted 24 January 2012 - 02:07 AM

what's the DBMS you're using?
I checked your query with getdate() since I'm using SQL Server. no error was thrown..
Was This Post Helpful? 0
  • +
  • -

#3 antikythera  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 3
  • Joined: 23-January 12

Re: Why am I missing a right parenthesis (trying to make check it with cur

Posted 24 January 2012 - 09:54 PM

im using oracle data base 10 g express edition, i think its running sqlplus. I tried switching in Getdate(), insted of sysdat() and i get an error.
Was This Post Helpful? 0
  • +
  • -

#4 nK0de  Icon User is offline

  • Catch me As Exception
  • member icon

Reputation: 205
  • View blog
  • Posts: 823
  • Joined: 21-December 11

Re: Why am I missing a right parenthesis (trying to make check it with cur

Posted 24 January 2012 - 10:57 PM

I'm not familiar with Oracle server and PL/SQL. But I just googled and found out that both sysdate() and getdate() are valid in Oracle too.
What's the error you're getting this time? Is it the same error about missing a paran??
Was This Post Helpful? 0
  • +
  • -

#5 antikythera  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 3
  • Joined: 23-January 12

Re: Why am I missing a right parenthesis (trying to make check it with cur

Posted 25 January 2012 - 12:32 AM

im getting " a date or system variable wrongfully specified in check constraint.
Was This Post Helpful? 0
  • +
  • -

#6 nK0de  Icon User is offline

  • Catch me As Exception
  • member icon

Reputation: 205
  • View blog
  • Posts: 823
  • Joined: 21-December 11

Re: Why am I missing a right parenthesis (trying to make check it with cur

Posted 25 January 2012 - 12:42 AM

apparently u cannot use sysdate() in a check constraint it seems. see here and this answer too..
Was This Post Helpful? 0
  • +
  • -

#7 JackOfAllTrades  Icon User is offline

  • Saucy!
  • member icon

Reputation: 6075
  • View blog
  • Posts: 23,542
  • Joined: 23-August 08

Re: Why am I missing a right parenthesis (trying to make check it with cur

Posted 26 January 2012 - 09:20 AM

Moved to Oracle
Was This Post Helpful? 0
  • +
  • -

#8 baavgai  Icon User is online

  • Dreaming Coder
  • member icon

Reputation: 5875
  • View blog
  • Posts: 12,757
  • Joined: 16-October 07

Re: Why am I missing a right parenthesis (trying to make check it with cur

Posted 26 January 2012 - 11:54 AM

Sysdate isn't called with (), so that's one error. Unfortunately, you don't get to use sysdate in check constraints at all. You'll need a trigger for that kind of check.

Also, you chk_roomNO constraint doesn't make much sense, because your roomNo is char(3). Perhaps you wanted a number? There are a few places where it looks like you'd want number instead of char.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1