CODEWITHSUNDEEP
sqloracle-databaseexpression
Barry Robinholt
Barry Robinholt

Reputation: 19

Missing Expression in oracle struggling

CREATE TABLE OT_PAY (
    PAB_ITEM_ID                CHAR(5 BYTE)               NOT NULL,
    HOLIDAY_MULTIPLIER         NUMBER(4,2)                NOT NULL,
    CONSTRAINT                   OT_PAY_PK          PRIMARY KEY(PAB_ITEM_ID),
  CONSTRAINT                     OT_PAY_FK1      
    FOREIGN KEY(PAB_ITEM_ID)
    REFERENCES OT_PAY(PAB_ITEM_ID),
  CONSTRAINT HOLIDAY_MULTIPLIER CHECK (HOLIDAY_MULTIPLIER <='1.00' and >='3.50')
    );


Error starting at line : 77 in command -
CREATE TABLE OT_PAY (
    PAB_ITEM_ID                CHAR(5 BYTE)               NOT NULL,
    HOLIDAY_MULTIPLIER         NUMBER(4,2)                NOT NULL,
    CONSTRAINT                   OT_PAY_PK          PRIMARY KEY(PAB_ITEM_ID),
  CONSTRAINT                     OT_PAY_FK1      
    FOREIGN KEY(PAB_ITEM_ID)
    REFERENCES OT_PAY(PAB_ITEM_ID),
  CONSTRAINT HOLIDAY_MULTIPLIER CHECK (HOLIDAY_MULTIPLIER <='1.00' and >='3.50')
    )
Error report -
SQL Error: ORA-00936: missing expression
00936. 00000 -  "missing expression"
*Cause:    
*Action:

Upvotes: 0

Views: 65

Answers (2)

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48207

Ok, there are several issues with that check.

1- You shouldnt compare number with strings so

  HOLIDAY_MULTIPLIER <='1.00' and >='3.50'

Should be:

  HOLIDAY_MULTIPLIER <=1.00 and  >=3.50

2- Sintaxis error, you cant write conditional that way, you need:

  HOLIDAY_MULTIPLIER <=1.00 and HOLIDAY_MULTIPLIER >=3.50

3 - The range doesnt make sense because HOLIDAY_MULTIPLIER cant be less than 1 and greater then 3.50 at the same time so yo need invert the <= or use OR

  HOLIDAY_MULTIPLIER >=1.00 and  HOLIDAY_MULTIPLIER <=3.50

Finally you can use between instead

  HOLIDAY_MULTIPLIER BETWEEN 1.00 and 3.50

Upvotes: 4

Husqvik
Husqvik

Reputation: 5809

HOLIDAY_MULTIPLIER <='1.00' and >='3.50' => HOLIDAY_MULTIPLIER <='1.00' and HOLIDAY_MULTIPLIER >='3.50'

Use number literals instead of strings - HOLIDAY_MULTIPLIER <= 1 and HOLIDAY_MULTIPLIER >=3.5

Upvotes: 2

Related Questions