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

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

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

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

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

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

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


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

REGIMENDRUG
RegimenID    DrugID      
1    123      
1    124      
2    124      
2    125     

PRESCRIPTION
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
as
 -- malisa plsql functions
   f_reg_arvid number := null;
begin

    declare
      cursor regimen_cur is select distinct regimenid from REGIMENDRUG;
     
      ------- variable declarations -------
      f_reg number;
      f_num1 number;
      f_num2 number;
      ------------------------------------- 
 
    begin 
     
      open regimen_cur;
      loop
        fetch regimen_cur into f_reg;
       
        exit when regimen_cur%notfound;
       
            select count(groupid) into f_num1
            from
            (
                select DrugID from PRESCRIPTION where id = pres_id
                intersect
                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;
     
    end;
     
    return f_reg_arvid;                   

end;

Usage.

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

Results.
 
ID    DrugID    RegimenID      
1    123    2      
1    124    2      
1    124    2     


Limitations.

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.

Future.

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: GeeksWithBlogs.net