[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 380
  • Last Modified:

Using DECODE function in create table command

I am working on a database project.  I have most of the project complete.  Currently, I am working on the following table:

drop table PATIENT_INSURANCE;
create table PATIENT_INSURANCE(
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)
references PATIENT_DEMOGRAPHIC (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?
0
CharleneS77
Asked:
CharleneS77
  • 3
1 Solution
 
sujith80Commented:
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
begin
 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 ;
end;
/
0
 
CharleneS77Author Commented:
Thank you for the response.  The following trigger:

create or replace trigger PPO_TRIGGER
before insert or update ON Patient_Insurance
for each row
begin
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;
end;
/

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

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


0
 
CharleneS77Author Commented:
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.

LINE/COL  ERROR  
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.  





0
 
CharleneS77Author Commented:
Nevermind, I found the mistake.  I needed to have the dots.

:new.Ins_Type

and

:new.Primary_Phy


Thank you!
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now