Do I use a case statement?

[Reynet01]

I have posted this in the SQL Forum also but maybe some here can help

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>>

<<Subject Id>> and <<Dept>> are supplied by the user from a telephone keypad

Thanks for Any Help

 

[Veep]

Something like:

<%
SELECT CASE Dept
CASE 1
   myAlpha = "abc"
CASE 2
   myAlpha = "def"
CASE 3
   myAlpha = "ghi"
End Select

' Append the alpha to the subjectid

myCriteria = myAlpha & SubjectID

' this would need to be fixed with line continuation _
strSql="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='" & SubJectID & "'"
%>

You realize though that by appending alpha characters to the ID it is now a string data type.

 

[DreXor]

best thing to do is make a small temp table with the cross ref for each dept and the letters you want added, then you can do it via SP ( much faster ) or through a recordset loop
but this is only to update the table overall, if you're looking per-instance, you may need to make the temp table more permanent, and if there's more than one key field ( besides dept ) then you may need to add a secondary field for permutations.

 

[Reynet01]

Sorry to sound stupid but this was dumped on me and I AM NOT A DB PROGRAMMER but will this work for a normal SQL statement it will not be in a ASP page

 

[DreXor]

unless someone here might jump on this, i recommend explaining a little more in detail your objectives, and place that post in the SQL group.

 

[Veep]

This is the ASP forum.

 

[Reynet01]

The program I am trying to get this to work with uses standard sql statements for patient lookup the two fields <<Subject ID>> and <<Dept>> are typed in by a user on a telephone but unfortunetly patients at most clinics have alpha characters at the begining of there ID since there is no way for the users to type in alpha characters over a keypad they will have to type a dept code in and then I need to append the proper characters according to the dept code given in front of SubjectId so that the query can lookup the patient Id correctly. I have posted this in the sql forum but rarely seem to get any replies to my post in that forum. I have posted in this forum many times with query questons and have always had fantastic help from the great people in this forum.

 

[DreXor]

that's fine, ASP/VB/VBS/JS and a multitude of programming languages have case statements, you're asking in a programming environment about cases statements, kinda leans towards the bias, but since those details have been indirectly cleaned up by the generous posting by Veep with the SQL statement......


you seem to be in need of a cross reference table, or a modification to the tone menu, if you have 10 dept's you have 10 push buttons, assign them that way, if you must tie them into each other somehow with subj and dept, then you'll probably need a cross reference to match up from the user input, especially if there's going to be datatype issues. Veep seems to have answered your question as you requested, but being that you never specified where <<Dept>> resides, it's kind of hard to point you in the correct direction.