Using DECODE function in create table command

Posted on 2007-10-19
Last Modified: 2013-12-07
I am working on a database project.  I have most of the project complete.  Currently, I am working on the following table:

Policy_Num            NUMBER NOT NULL,
Comp_Code            VARCHAR2(20),
SSNO                  NUMBER,
LName                  VARCHAR2(25),
FName                  VARCHAR2(25),
Eff_Date            DATE,
Exp_Date            DATE,
Ins_Type            VARCHAR2(3) constraint check_ins_type CHECK (Ins_Type IN ('PPO', 'HMO')),
Primary_Phy            VARCHAR2(35),
constraint PATIENT_INSURANCE_PK primary key (Policy_Num),
constraint INS_COMP_FK foreign key (Comp_Code)
references INSURANCE_COMPANY (Comp_Code),
constraint PATIENT_FK_1 foreign key (SSNO)

The description states, "If the insurance type is PPO, the name of the primary physician (PRIMARY_PHY) must be specified."

I attempted the following:
constraint check_for_PPO CHECK (Ins_Type IN (DECODE(if Ins_Type = PPO, then Primary_Phy NOT NULL)))

I get the following error:
constraint check_for_PPO CHECK (Ins_Type IN (DECODE(if Ins_Type = PPO, then Primary_Phy NOT NULL)))

ERROR at line 16:
ORA-00907: missing right parenthesis

Am I on the right page here or am I way off?  Why am I receiving the "missing right parenthesis" error?
Question by:CharleneS77
    LVL 27

    Accepted Solution

    You cannot use a check constraint in this case. You must use a trigger for the validation.
    Trigger would take the form as shown below.

    create or replace trigger test_trg
    before insert or update
    for each row
     if :new.ins_type = 'PPo' and :New.primary_phy is null then
      raise_application_error(-20001, 'Primary_Phy cannot be null, when ins_type is PPO');
     end ;

    Author Comment

    Thank you for the response.  The following trigger:

    create or replace trigger PPO_TRIGGER
    before insert or update ON Patient_Insurance
    for each row
    if :new Ins_Type = 'PPO' and :new Primary_Phy is null then
    raise_application_error (-20001, 'Primary_Phy cannot be null when Ins_Type is PPO');

    gives me this warning:
    Warning: Trigger created with compilation errors.

    Should I get a different message if it is created successfully?


    Author Comment

    OK, this is pretty neat.  After reading some more, I learned that I can show the errors with the following:

    show errors trigger PPO_TRIGGER;

    I get the following errors, but I'm not sure what they mean.

    2/4  PLS-00049: bad bind variable 'NEW'  
    2/9  PLS-00103: Encountered the symbol "INS_TYPE" when expecting one o f the following: . ( * @ % & = - + < / > at in is mod remaind er not rem then <an exponent (**)> <> or != or ~= >= <= <> and or like LIKE2_ LIKE4_ LIKEC_ between || indicator multiset me mber SUBMULTISET_ The symbol "." was substituted for "INS_TYPE " to continue.  
    2/30  PLS-00049: bad bind variable 'NEW'  
    2/35  PLS-00103: Encountered the symbol "PRIMARY_PHY" when expecting on e of the following: . ( * @ % & = - + < / > at in is mod rema inder not rem then <an exponent (**)> <> or != or ~= >= <= <> and or like LIKE2_ LIKE4_ LIKEC_ between || indicator multiset member SUBMULTISET_ The symbol "." was substituted for "PRIMA RY_PHY" to continue.  


    Author Comment

    Nevermind, I found the mistake.  I needed to have the dots.




    Thank you!

    Featured Post

    Highfive Gives IT Their Time Back

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    This post first appeared at Oracleinaction  ( Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
    Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
    This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
    Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now