5 Replies Latest reply: Apr 30, 2012 10:31 AM by Rao RSS

    disease analysis problem in database

    Rao
      hy I am facing a problem of generating reports of diffenent diseases based on different criteria my problem is that I have four tables

      Patients (patient_id,name,gender,DOB) with data
      (1,jon,male,12-FEB-1980)
      (2,mical,male,12-FEB-1950)
      (3,sunita,female,12-FEB-1970)
      (4,shakira,female,12-FEB-1960)
      visits(visit_id,patient_id,visit_date) with data
      (1,1,12-FEB-2011)
      (2,1,12-FEB-2001)
      (3,1,12-FEB-2010)
      (4,2,12-FEB-2011)
      (5,2,12-FEB-2009)
      (5,3,12-FEB-2008)
      (6,3,12-FEB-2007)
      (7,4,12-FEB-2011)
      patient_disease_history(history_id,disease_id,visit_id) with data
      (1,F00,1)
      (2,F00,1)
      (3,F01,1)
      (4,F01,1)
      (5,F02,1)
      (6,F00,2)
      (7,F01,2)
      (8,F01.0,3)
      (9,F02.0,3)
      (10,F00.1,4)

      (11,F00,4)
      (12,F04,5)
      (13,F04,5)
      (14,F03.0,6)
      (15,F03.1,6)
      (16,F03.2,7)
      (17,F00.0,7)
      (18,F02.0,7)
      (19,F03.1,7)
      diseases(disease_id,title,base_category) HAVING data like

      (F001,Cancer,'')
      (F00,brest cancer,F001)
      (F00.0,left brest cancer,F00)
      (F00.1,right brest cancer,F00)
      (F01,chest cancer,F001)
      (F01.0,inner chest cancer,F01)
      (F01.1,outer cancer,F01)
      (F02,leg cancer,F001)
      (F02.0,left lef cancer,F02)
      (F02.2,right lef cancer,F02)
      (F03,arm cancer,F001)
      (F03.0,left arm cancer,F03)
      (F03.1,right arm cancer,F03)
      (F03.2,center finger cancer,F03)
      (F04, ear cancer,F001)
      (F002, TB,'')
      (F003, AIDS,'')

      1) Now my first requirement is to find total no of patients under F001 result should be collection of all the diseases under that disease
      2) second find total no of male PATIENTS under F001
      3) find total no of patients under F001 from visit_date 1-FEB-1949 to 1-FEB-1990
      4) find total no of female patients under F001 from visit_date 1-FEB-1949 to 1-FEB-1990
      5) find total no of patients under F03 from visit_date 1-FEB-1949 to 1-FEB-1990 whose DOB is 12-FEB-1980

      I tried using this query

      SELECT distinct t2.disease,

      *(SELECT SUM(t1.patient) From (select d.disease_id as disease,base_category as category ,count(distinct patient_id) as patient,gender*
      From ( patient_disease_history h join visits using(visit_id) join patients using(patient_id)) right outer join diseases d on   d.disease_id=h.disease_id

      group by d.disease_id,base_category,gender) t1
      where gender='Male'
      START WITH t1.disease= t2.disease

      CONNECT BY nocycle t1.category = PRIOR t1.disease) patient

      FROM (select   d.disease_id as disease,base_category as category ,count(distinct patient_id) as patient
      From ( patient_disease_history h join visits    using(visit_id)) right outer join diseases d on d.disease_id=h.disease_id

      group by d.disease_id,base_category) t2
      where category='F001'

      THIS Workds fine with only diseases and their sub categories
      but problem occure when i tried to include gender and visit_date in the where condition like above query is giving wrong results
      in this scenario query give wrong results i know why but i dont know how to solve this problem please read this whole document and give some query that will meet all requirements

      Edited by: Rao on Apr 19, 2012 9:15 PM
        • 1. Re: disease analysis problem in database
          Frank Kulash
          Hi,

          Whenever you have a problem, please post CREATE TABLE and INSERT statements for the sample data.
          Also post the results you want from that data, and an explanation of how you get those results from that data, with specific examples.
          Simplify the problem as much as possible. Remove all tables and columns that play no role in this problem.
          Always say which version of Oracle you're using.
          Rao wrote:
          hy I am facing a problem of generating reports of diffenent diseases based on different criteria my problem is that I have four tables

          Patients (patient_id,name,gender,DOB) with data
          (1,jon,male,12-FEB-1980)
          (2,mical,male,12-FEB-1950)
          (3,sunita,female,12-FEB-1970)
          (4,shakira,female,12-FEB-1960)
          visits(visit_id,patient_id,visit_date) with data
          (1,1,12-FEB-2011)
          (2,1,12-FEB-2001)
          (3,1,12-FEB-2010)
          (4,2,12-FEB-2011)
          (5,2,12-FEB-2009)
          (5,3,12-FEB-2008)
          (6,3,12-FEB-2007)
          (7,4,12-FEB-2011)
          patient_disease_history(history_id,disease_id,visit_id) with data
          (1,F00,1)
          (2,F00,1)
          (3,F01,1)
          (4,F01,1)
          (5,F02,1)
          (6,F00,2)
          (7,F01,2)
          (8,F01.0,3)
          (9,F02.0,3)
          (10,F00.1,4)

          (11,F00,4)
          (12,F04,5)
          (13,F04,5)
          (14,F03.0,6)
          (15,F03.1,6)
          (16,F03.2,7)
          (17,F00.0,7)
          (18,F02.0,7)
          (19,F03.1,7)
          diseases(disease_id,title,base_category) HAVING data like

          (F001,Cancer,'')
          (F00,brest cancer,F001)
          (F00.0,left brest cancer,F00)
          (F00.1,right brest cancer,F00)
          (F01,chest cancer,F001)
          (F01.0,inner chest cancer,F01)
          (F01.1,outer cancer,F01)
          (F02,leg cancer,F001)
          (F02.0,left lef cancer,F02)
          (F02.2,right lef cancer,F02)
          (F03,arm cancer,F001)
          (F03.0,left arm cancer,F03)
          (F03.1,right arm cancer,F03)
          (F03.2,center finger cancer,F03)
          (F04, ear cancer,F001)
          (F002, TB,'')
          (F003, AIDS,'')

          1) Now my first requirement is to find total no of patients under F001 result should be collection of all the diseases under that disease
          2) second find total no of male PATIENTS under F001
          3) find total no of patients under F001 from visit_date 1-FEB-1949 to 1-FEB-1990
          4) find total no of female patients under F001 from visit_date 1-FEB-1949 to 1-FEB-1990
          5) find total no of patients under F03 from visit_date 1-FEB-1949 to 1-FEB-1990 whose DOB is 12-FEB-1980

          I tried using this query
          Is the query below for problem 1, 2, 3, 4 or 5, or for some combination of them?
          SELECT distinct t2.disease,

          *(SELECT SUM(t1.patient) From (select d.disease_id as disease,base_category as category ,count(distinct patient_id) as patient,gender*
          From ( patient_disease_history h join visits using(visit_id) join patients using(patient_id)) right outer join diseases d on   d.disease_id=h.disease_id

          group by d.disease_id,base_category,gender) t1
          where gender='Male'
          START WITH t1.disease= t2.disease

          CONNECT BY nocycle t1.category = PRIOR t1.disease) patient

          FROM (select   d.disease_id as disease,base_category as category ,count(distinct patient_id) as patient
          From ( patient_disease_history h join visits    using(visit_id)) right outer join diseases d on d.disease_id=h.disease_id

          group by d.disease_id,base_category) t2
          where category='F001'
          The second item in the main SELECT clause (that is, patient) is a scalar sub-query. You'll get a run-time error if that sub-query produces more than 1 row of output. But if it's only producing one row, what's the point of doing CONNECT BY inside it?
          THIS Workds fine with only diseases and their sub categories
          but problem occure when i tried to include gender and visit_date in the where condition like above query is giving wrong results
          in this scenario query give wrong results i know why but i dont know how to solve this problem please read this whole document and give some query that will meet all requirements
          Sorry, I don't know what the reuirements (that is, the exact results you want) are. Post CREATE TABLE and INSERT statements for the sample data, and the results you want from that data.

          It looks like there's a lot of overlap between patient and t2. Assuming you really need both, you should put the common parts in a WITH clause.
          • 2. Re: disease analysis problem in database
            Rao
            thanks for your reply please give me your email address i will share my schema with you and all queries that i tried
            • 3. Re: disease analysis problem in database
              Rao
              thanks for your reply please give me your email address i will share my schema with you and all queries that i tried
              • 4. Re: disease analysis problem in database
                Frank Kulash
                Hi,
                Rao wrote:
                thanks for your reply please give me your email address i will share my schema with you and all queries that i tried
                You can post things on this site; then lots of people can help you.

                If you post many different queries, say which one seemed to be best.
                • 5. Re: disease analysis problem in database
                  Rao
                  this question answered by my consultant