Malisa Ncube - .NET Delights

.NET Development ideas and things

posts - 36, comments - 71, trackbacks - 0

My Links

News

I LOVE

Twitter

malisancube @jmakumbi Hie my friend, who is very hard to see. about 3 days ago

malisancube @peter_mushangwe Its very unfortunate for the generations that grow up to that ordeal. about 12 days ago

malisancube @peter_mushangwe Looks likes those kind of things that cannot be controlled. I guess people would normally get on with their lives. about 12 days ago

malisancube @peter_mushangwe There is major problem that the financial crisis will not end soon. about 12 days ago

malisancube @peter_mushangwe Worst fraud in history of financial industry. about 12 days ago

malisancube @peter_mushangwe Not at all my friend. These people destroyed the world economy and its affecting all of us. about 12 days ago

malisancube Watching "Inside Job" documentary about the financial meltdown. I can't help the tears from my eyes. about 12 days ago

malisancube RT @AfricaPostSharp: Check out "PostSharp meeting - PostSharp Essentials (27th Jan)" http://t.co/1SgAGegp about 18 days ago

malisancube Spent hours debugging an SSIS package and realised that Oracle allows dates like '01-FEB-0210' and fails on transforming it to MSSQL. :( about 27 days ago

malisancube @raybesiga I will go to Bukoto. Close to Kadic. you? about 33 days ago

Post Categories

<< Oracle Data Conversion: ORA-01722: invalid number | Home | DataObjects.NET 4.0 ORM Released >>

Creating a PL/SQL UDF to enable subset deduction

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.

Share This Post:

Print | posted on Thursday, August 14, 2008 9:06 PM |

Feedback

No comments posted yet.