| Urgent Help [message #370087] |
Wed, 13 December 2000 05:18  |
Muhammad Asim
Messages: 6
Registered: November 2000
|
Junior Member |
|
|
Hi,
iam very new to oracle and i stuck in a problem if you could solve that problem
the problem is that i have a table of patient record.in which i keep the patient record
and table is like that
Reg_no,Name,Main_Diagnosis,Anc_diag1,Anc_diag2,Anc_diag3,Anc_diag4
1 Allan AAA
2 Smith BBB AAA
3 Robin CCC AAA BBB
3 Noor AAA BBB CCC DDD EEE
6 David DDD AAA
In this table I’ve inserted supposed values. A patient come for checkup of a specific diseases say AAA doctor will enter his record in his database .AAA will be inserted in main_diagnosis ,because it is main disease for which patient came.But sometimes it happens that patient doesn’t have only that specific disease .doctor find that he has also some other diseases as well so doctor will enter main disease in main_diagnosis and other diseases in Anc_diag1,Anc_diag2,Anc_diag3,Anc_diag4 respectively.
There are no of diseases that are not fixed they are dynamic.Now I want a query to find out no of patient of any specific disease like AAA ,in above table 5 patient have that problem.so query will count no patient from all disease’s columns and its result should be like this
Disease Patient
AAA 5
BBB 3
CCC 2
DDD 2
EEE 1
Once again diseases are not fix ,it will grow as doctor enter the record. No of disease are not known
It is dynamic.
Plz if you could solve my problem than send the query at my mailing address
Debonair_pk@yahoo.com
Thanks in anticipation.
|
|
|
|
| Re: Urgent Help [message #370144 is a reply to message #370087] |
Thu, 21 December 2000 03:06  |
amarpatgiri
Messages: 11
Registered: December 2000
|
Junior Member |
|
|
Sorry for the late reply - but you can do something like the following code snippet:
--------------------------------- begin of code
declare
cursor c1 is select main_diagnosis_name,anc_diag1_name,
anc_diag2_name, anc_diag3_name, anc_diag4_name
from patient;
diag1 varchar2(10);
diag2 varchar2(10);
diag3 varchar2(10);
diag4 varchar2(10);
diag5 varchar2(10);
begin
-- create table mytemptable
-- ( diag varchar2(10) primary key, patients number(5) ) cache;
-- I have used the above defn for the results table, a temporary table may
-- also be defined ;
-- print all your reports from this table (mytemptable or whatever you choose to call it!)
-- this is a barebones approach, a better option will be to populate the "mytemptable"
-- with all the names of diagnosises (??) with patients set to 0. This way we can avoid
-- the begin/exception/end block structure (update will not through any exception even
-- if there are no records found - so we can't have it the other way round)
--
open c1;
fetch c1 into diag1, diag2, diag3, diag4, diag5;
while c1%found loop
if diag1 is not null then
begin
insert into mytemptable values ( diag1, 1);
exception
when others then
update mytemptable set patients = patients + 1
where diag = diag1;
end;
end if;
if diag2 is not null then
begin
insert into mytemptable values ( diag2, 1);
exception
when others then
update mytemptable set patients = patients + 1
where diag = diag2;
end;
end if;
if diag3 is not null then
begin
insert into mytemptable values ( diag3, 1);
exception
when others then
update mytemptable set patients = patients + 1
where diag = diag3;
end;
end if;
if diag4 is not null then
begin
insert into mytemptable values ( diag4, 1);
exception
when others then
update mytemptable set patients = patients + 1
where diag = diag4;
end;
end if;
if diag5 is not null then
begin
insert into mytemptable values ( diag5, 1);
exception
when others then
update mytemptable set patients = patients + 1
where diag = diag5;
end;
end if;
fetch c1 into diag1, diag2, diag3, diag4, diag5;
end loop;
close c1;
end;
--------------------------------- end of code
Hope this helps! Pl let me know.
Thanks,
-amar
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
