alter table to add contraint using a case statement?

Question

I'm working in APEX making a form for users to reserve a building. I'm trying to add ckecks to the number_guests column so that, based on the date and building number, users can only enter a certain amount of guests (10 between Memorial Day and Labor day and 20 any other time for this particular building). The case statement is working fine, just not when I put it with the check statement. I keep getting a "missing expression" error message. Please Help! Here is what I have:

    SELECT 
    CASE

    WHEN TRIM(TO_CHAR(reserve_date,'ww')) >= '21' 
    and TRIM(TO_CHAR(reserve_date,'ww')) <= '35' 
    and TRIM(building_number) = '25'
    THEN CHECK (number_guests  <= 10)

    WHEN TRIM(TO_CHAR(reserve_date,'ww')) < '21'  
    and TRIM(building_number) = '25'
    THEN CHECK (number_guests  <= 20)

    WHEN TRIM(TO_CHAR(reserve_date,'ww')) > '35' 
    and TRIM(building_number) = '25'
    THEN CHECK (number_guests  <= 20)

    ELSE RAISERROR (N'The number of guests is invalid.')
    END
    FROM SOMERSET_RESERVATION;

score 0 · Accepted Answer · answered May 23 '14 at 13:49

The format for a check constraint is not a select statement. It is an alter table statement (or it goes directly in the table definition). I think this is the logic you want:

alter table SOMERSET_RESERVATION
    add constraint check_InvalidNumberOfGuests
        check (number_guests <= 10 or
               (number_guests <= 20 and
                TRIM(building_number) = '25' and
                TO_CHAR(reserve_date, 'ww') not between '21' and '35'
               )
              );

Note that the ww format returns numbers with zero padding, so the logic does work. (Otherwise, '3' would fail, but it actually returns '03'.)

alter table to add contraint using a case statement?

1 Answers1