Geeks With Blogs

News

I LOVE

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
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.

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