Geeks With Blogs
Malisa Ncube - .NET Delights .NET Development ideas and things

I have been wondering how i can use the set theory to determine the which items form a subset of another in oracle. I wanted to deduct a regimen of drugs from a prescription and what i had to do was to loop through all regimens and find out which one exists in the current prescription.

A regimen is a combination of drugs and how they are administered. e.g. to treat Tubercolosis the doctor may prescribe a regimen called (RHE 150/75/275) which is composed of Rifampicin 150 mg/Isoniazid 75 mg/ Ethambutol 275 mg.

The following are some examples of TB regimens and their drug combinations

Regimen     | Drugs

(RH150/150) | Rifampicin 150mg/Isoniazid 150mg
(EH400/150) | Ethambutol 400mg/Isoniazid 150mg

The following are the table structures for prescription, regimen  and drugs

    Field    DataType      
    ID         Number(5)       (KEY)
    PatientNo    Number(6)      
    DrugID    Number(5)      
    DispensedDate    Date      
    DispenserID    Number(5)      

   Field    DataType      
   ID    Number(5)      (KEY)
   DrugName    Number(6)      

    Field    DataType      
    RegimenID    Number(5)        (KEY)
    DrugID    Number(5)        (KEY)

    Field    DataType      
    ID    Number(5)    (KEY)  
    RegimenName    Varchar(50)      
Give the sample data as shown below

ID    RegimenName      
1    RH150/150      
2    EH400/150      
3    RHE 150/75/275     

 ID    DrugName      
100    Panadol 500mg      
123    Rifampicin 150mg      
124    Isoniazid 150mg      
125    Ethambutol 400mg      
130    Cotrimoxazole     

RegimenID    DrugID      
1    123      
1    124      
2    124      
2    125     

ID    PatientNo    DrugID    DispensedDate    DispenseID      
1    250    123    10/08/2008    10      
1    250    125    10/08/2008    10      
1    250    100    10/08/2008    10     

From the above we can see that patient 250 was given a number of drugs and they constitute a regimen EH400/150

Create a UDF with following code.

create or replace function get_regimen(pres_id in number) return number
 -- malisa plsql functions
   f_reg_arvid number := null;

      cursor regimen_cur is select distinct regimenid from REGIMENDRUG;
      ------- variable declarations -------
      f_reg number;
      f_num1 number;
      f_num2 number;
      open regimen_cur;
        fetch regimen_cur into f_reg;
        exit when regimen_cur%notfound;
            select count(groupid) into f_num1
                select DrugID from PRESCRIPTION where id = pres_id
                select DrugID from REGIMENDRUG where regimenid = f_reg
            select count(DrugID ) into f_num2
            from REGIMENDRUG where regimenid = f_reg;
            if f_num1 = f_num2 then -- regimen was found
               select id into f_reg_arvid    -- you can decide to kee the Regimenname here and return it. You decide.
               from REGIMEN where id = f_reg; 
            end    if;
      end loop;
    return f_reg_arvid;                   



select id, DrugID, get_regimen(prescriptionid) as RegimenID 
from prescription where id = 1

ID    DrugID    RegimenID      
1    123    2      
1    124    2      
1    124    2     


1. The above computation of subsets assumes the prescription has only one regimen of drugs. I have not tested it with drugs which form two different regimens.
2. The perfomance needs to be enhanced, the loop should be broken once a regimen had been found.


I will try the same example in MS SQL Server 2005/8.
Posted on Thursday, August 14, 2008 9:06 PM | Back to top

Comments on this post: Creating a PL/SQL UDF to enable subset deduction

No comments posted yet.
Your comment:
 (will show your gravatar)

Copyright © Malisa L. Ncube | Powered by: