Regarding case control statement in mysql

aghori

/* 2:Display the patient_id, patient_name, phone number( +91-999-999-9999 format) , type_of_ailment .
type of ailement is decided with the number of days stay in hospital.
if stay <=5 ---- Minor
if stay >5 and <=15 days ----Medium
if stay >15 days ---- Major */

I have 2 tables room_allocation and patient_master where pid is the primary key of patient_master table and it also happens to be foreign key in room_allocation table. 1)Columns of patient_master table are(pid Varchar(5) Primary Key, name Varchar(20) Not Null, age int Not Null, weight int Not Null, gender Varchar(10) Not null, address Varchar(50) Not Null, phoneno varchar(10) Not Null, disease Varchar(50) Not Null, doctorid Varchar(5) Not Null, constraint patient_master_doctorid_fk foreign key(doctorid) references doctor_master(doctorid)) 2)columns of room_allocation table are (room_no varchar(5), pid varchar(5), adm_date date, release_date date, constraint room_allocation_room_no_fk foreign key(room_no) references room_master(room_no), constraint room_allocation_pid_fk foreign key(pid) references patient_master(pid)) -------------------------------------------------------------------------- I have written the following querry for above question but it gave a syantax error. -------------------------------------------------------------------------

select p.pid,p.name,concat('91-',p.phoneno),
case (release_date-adm_date)
when (release_date-adm_date)<=5 then 'minor'
when ((release_date-adm_date)>5 and (release_date-adm_date)<=15) then 'medium'
else 'Major'
end as 'type_ailment'
from patient_master p,room_allocation r
when (release_date-adm_date)<=5 then minor
when (release_date-adm_date)>5 and when (release_date-adm_date)<=15 then med
from patient_master p,room_allocation r
where p.pid=r.pid ;

Wayne Gaylard

Look at your query closely, and you will see that you have duplicated the when clauses in the second half of the query

select p.pid,p.name,concat('91-',p.phoneno),
case (release_date-adm_date)
when (release_date-adm_date)<=5 then 'minor'
when ((release_date-adm_date)>5 and (release_date-adm_date)<=15) then 'medium'
else 'Major'
end as 'type_ailment'

from patient_master p,room_allocation r
when (release_date-adm_date)<=5 then minor
when (release_date-adm_date)>5 and when (release_date-adm_date)<=15 then med

from patient_master p,room_allocation r
where p.pid=r.pid ;

Everyone dies - but not everyone lives

Jorgen Andersson

select p.pid,p.name,concat('91-',p.phoneno),
case
when (release_date-adm_date)<=5 then 'minor'
when (release_date-adm_date)<=15 then 'medium'
else 'Major'
end as 'type_ailment'
from patient_master p,room_allocation r
where p.pid=r.pid

Wrong is evil and must be defeated. - Jeff Ello[^]

aghori

Thanx for replying.I have duplicated the when clauses but I need to test for each condition so what can be the exact alternative.I tried removing the second when clause and replaced it with else clause but still it does not work.Should I go for nested if.

aghori

Thank You very much it worked.