Case Statement?

[Reynet01]

I have the following query

SELECT PatientProfile.PatientId, PatientProfile.First, PatientProfile.Middle, PatientProfile.Last, PatientProfile.SSN, PatientProfile.Birthdate,DoctorFacility.ListName, DoctorFacility.Phone2, DoctorFacility.Phone2Type FROM PatientProfile LEFT JOIN DoctorFacility ON PatientProfile.RefDoctorId = DoctorFacility.DoctorFacilityId WHERE PatientProfile.PatientID='<<Subject ID>>'

I would Like to change the query based on another field <<Dept>> for example if the <<Dept>> is lets say 3 I would Like to append alpha characters mwt to the beginning of <<Subject ID>> for the lookup clause. Would I do this with a case statement? if so could someone give me an example of how to do this. I will have about 10 different dept codes all with different alpha values I will have to append to the beggining of the <<subject ID>>

Thanks for Any Help

 

[mjp483]

try this:


SELECT PatientProfile.PatientId, PatientProfile.First, PatientProfile.Middle, PatientProfile.Last, PatientProfile.SSN, PatientProfile.Birthdate,DoctorFacility.ListName, DoctorFacility.Phone2, DoctorFacility.Phone2Type FROM PatientProfile LEFT JOIN DoctorFacility ON PatientProfile.RefDoctorId = DoctorFacility.DoctorFacilityId WHERE PatientProfile.PatientID=case when dept=3 then 'mwt'+ SubjectID End

 

[Reynet01]

How would i put more than on entrie into the state lets say for Dept 6 also with a alpha output of ftr I tried adding them to your statement but I am getting a when statement placement error

Thanks for the responce

 

[Pattycake245]

How about this:

SELECT PatientProfile.PatientId, PatientProfile.First, PatientProfile.Middle, PatientProfile.Last,
PatientProfile.SSN, PatientProfile.Birthdate,DoctorFacility.ListName, DoctorFacility.Phone2,
DoctorFacility.Phone2Type
FROM PatientProfile
LEFT JOIN DoctorFacility
ON PatientProfile.RefDoctorId = DoctorFacility.DoctorFacilityId
WHERE PatientProfile.PatientID=
case dept
when 3 then 'mwt'+ SubjectID
when 6 then 'ftr'+ SubjectID....
else 'aaa'+ SubjectID
end

Not tested, but compiles with no errors.

Tim

 

[Reynet01]

When I use the following I keep getting asked for Patient ID for each of the Patient ID listed What am I doing Wrong?

Thanks for your help


SELECT PatientProfile.PatientId, PatientProfile.First, PatientProfile.Middle, PatientProfile.Last,
PatientProfile.SSN, PatientProfile.Birthdate,DoctorFacility.ListName, DoctorFacility.Phone2,
DoctorFacility.Phone2Type
FROM PatientProfile
LEFT JOIN DoctorFacility
ON PatientProfile.RefDoctorId = DoctorFacility.DoctorFacilityId
WHERE PatientProfile.PatientID=
case <<Department>>
when 1 then ''+ <<Subject ID>>
when 2 then 'B'+ <<Subject ID>>
when 3 then 'EB'+ <<Subject ID>>
when 4 then 'M'+ <<Subject ID>>
when 5 then 'BW'+ <<Subject ID>>
when 6 then 'P'+ <<Subject ID>>
when 7 then 'PW'+ <<Subject ID>>
when 8 then 'PWS'+ <<Subject ID>>
when 9 then 'PWST'+ <<Subject ID>>
when 10 then 'PWT'+ <<Subject ID>>
when 11 then 'LC'+ <<Subject ID>>
when 12 then 'H'+ <<Subject ID>>
when 13 then 'HC'+ <<Subject ID>>
when 14 then 'JC'+ <<Subject ID>>
when 15 then 'S'+ <<Subject ID>>
when 16 then 'SM'+ <<Subject ID>>
when 17 then 'S10'+ <<Subject ID>>
when 18 then 'FS'+ <<Subject ID>>
when 19 then 'WB'+ <<Subject ID>>
when 20 then 'WB'+ <<Subject ID>>
when 21 then 'TOK'+ <<Subject ID>>
when 22 then 'TOM'+ <<Subject ID>>
when 23 then 'TOP'+ <<Subject ID>>
else ''+ <<SubjectID>>
end

 

[Pattycake245]

Hmmm, Try this instead

SELECT PatientProfile.PatientId, PatientProfile.First, PatientProfile.Middle, PatientProfile.Last,
PatientProfile.SSN, PatientProfile.Birthdate,DoctorFacility.ListName, DoctorFacility.Phone2,
DoctorFacility.Phone2Type,
case Department
when 1 then ''+ [Subject ID]
when 2 then 'B'+ [Subject ID]
when 3 then 'EB'+ [Subject ID]
when 4 then 'M'+ [Subject ID]
when 5 then 'BW'+ [Subject ID]
when 6 then 'P'+ [Subject ID]
when 7 then 'PW'+ [Subject ID]
when 8 then 'PWS'+ [Subject ID]
when 9 then 'PWST'+ [Subject ID]
when 10 then 'PWT'+ [Subject ID]
when 11 then 'LC'+ [Subject ID]
when 12 then 'H'+ [Subject ID]
when 13 then 'HC'+ [Subject ID]
when 14 then 'JC'+ [Subject ID]
when 15 then 'S'+ [Subject ID]
when 16 then 'SM'+ [Subject ID]
when 17 then 'S10'+ [Subject ID]
when 18 then 'FS'+ [Subject ID]
when 19 then 'WB'+ [Subject ID]
when 20 then 'WB'+ [Subject ID]
when 21 then 'TOK'+ [Subject ID]
when 22 then 'TOM'+ [Subject ID]
when 23 then 'TOP'+ [Subject ID]
else ''+ [SubjectID]
end "subjectid"
FROM PatientProfile
LEFT JOIN DoctorFacility
ON PatientProfile.RefDoctorId = DoctorFacility.DoctorFacilityId
WHERE PatientProfile.PatientID= subjectid

Not sure which table Department and subject id are from. If this doesn't work give an example of what the subject id and patient id look like.

Tim

 

[Reynet01]

The <<subject ID>> and the <<Department>> are inputted through a telephone keypad and reside in an access database
the << >> are what the software package sees as its local data and when these two values are inputted they pull the patient ID up from a seperate sql database sorry for the confusion with the << >>'s

 

[Reynet01]

Thanks, I figured it out Thanks to everyone for pointing me in the right direction