quick question
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 3 123 LastLast
Results 1 to 10 of 26

Thread: quick question

  1. #1
    Join Date
    Feb 2005
    Posts
    7

    quick question

    This is my code for the table Frame

    CREATE TABLE Frame(
    FrNo NUMBER CONSTRAINT pkFrameNo PRIMARY KEY,
    event VARCHAR NOT NULL,
    FrMatch VARCHAR NOT NULL,
    Duration NUMBER NOT NULL,
    Score1 NUMBER(147) NOT NULL CONSTRAINT FrCheckScore CHECK ((Score1 > 0) & (Score1 < 300)),
    Score2 NUMBER(147) NOT NULL CONSTRAINT FrCheckScore CHECK ((Score2 > 0) & (Score2 < 300)),
    Report char(1000),
    );


    says it missing left parenthisis, cant see why
    but the Report field also needs to have the words OK or INCIDENT. How would i code this?

    Thanks p
    Paul

  2. #2
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Several things:
    - Oracle recommends use VARCHAR2 not VARCHAR
    - Max size number is NUMBER(38), you might mean NUMBER(38,109)? - obviously not if max val is 300 so NUMBER(3) would do.
    - You have two constraints with the same name "FrCheckScore"
    - I'm pretty sure you can't use "&" for "AND" in a condition (might be wrong on that)
    - I'd recommend VARCHAR2 instead of CHAR

    Report: Like '%OK%' or Like '%INCIDENT%' ? ? ? ?
    Last edited by DaPi; 02-16-2005 at 10:06 AM.

  3. #3
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Are you storing results of snooker matches?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  4. #4
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Originally posted by slimdave
    Are you storing results of snooker matches?
    300 is a lot of "4 away" and "score > 0" wouldn't allow for my standard of play!

    I'm wondering if it's 10-pin and the conditions should be >= <=.

  5. #5
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    I missed one:
    - you MUST specify a length for a VARCHAR or VARCHAR2

  6. #6
    Join Date
    Feb 2003
    Location
    London
    Posts
    170
    For starters, was this coded for an oracle database?
    (i)We use data type varchar2() and not just varchar when defining the data type for a column.
    (ii) The data type NUMBER has a range from 1-38, so your number(147) will not work here.
    (iii) your check constraint (Score1 < 300) .

    Hence,
    Code:
    CREATE TABLE Frame(
    FrNo NUMBER(38) CONSTRAINT pkFrameNo PRIMARY KEY,
    event VARCHAR2(100) NOT NULL,
    FrMatch VARCHAR2(100) NOT NULL,
    Duration NUMBER(38) NOT NULL,
    Score1 NUMBER(38) NOT NULL CONSTRAINT FrCheckScore1 CHECK (Score1 < 300), 
    Score2 NUMBER(38) NOT NULL CONSTRAINT FrCheckScore2 CHECK (Score2 < 300), 
    Report varchar2(1000) NOT NULL CONSTRAINT FrCheckreport CHECK (report in ('OK','INCIDENT'))
    );

  7. #7
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Originally posted by DaPi
    300 is a lot of "4 away" and "score > 0" wouldn't allow for my standard of play!

    I'm wondering if it's 10-pin and the conditions should be >= <=.
    Good catch. For my own standard of bowling, I'd sure like to see that constraint changed to "...>=0".
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  8. #8
    Join Date
    Feb 2005
    Posts
    7

    Follow Up - Quick Question

    I have modified the code, however still has the Error: ORA-00907: missing right parenthesis.



    CREATE TABLE Frame(
    FrNo NUMBER(3) CONSTRAINT pkFrameNo PRIMARY KEY,
    event VARCHAR2(3) NOT NULL,
    FrMatch VARCHAR2(3) CONSTRAINT FRcheckFrame CHECK ((FrNo >= 3) & (FrNo <= 35)),
    Duration NUMBER(3) NOT NULL,
    Score1 NUMBER(300) NOT NULL CONSTRAINT FrCheckScore1 CHECK ((Score1 > 0) AND (Score1 < 300)),
    Score2 NUMBER(300) NOT NULL CONSTRAINT FrCheckScore2 CHECK ((Score2 > 0) AND (Score2 < 300)),
    Report VARCHAR2(1000) CONSTRAINT CHECK ((INSTR(Report, 'OK') > 0)OR((INSTR(Report, 'INCIDENT') > 0))),
    );

    I am storing information for a snooker game, It is one of the projects i am working on. Could anyone give me some more help?
    Paul

  9. #9
    Join Date
    Jul 2002
    Location
    Northampton, England
    Posts
    612
    Do I get free tickets to the World Championships if I sort it out?

  10. #10
    Join Date
    Jul 2002
    Location
    Northampton, England
    Posts
    612
    Whats going on with the comma at the end:

    Code:
    Report VARCHAR2(1000) CONSTRAINT CHECK ((INSTR(Report, 'OK') > 0)OR((INSTR(Report, 'INCIDENT') > 0))), 
    );

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width