Translating returned value
-
Hello - In following stored procedure I am returning a row from a table that contains a column called visitType. The value of visittype can be either an O or I. I need to translate these values to Out-Patient and In-Patient respectively. I am not sure of the correct technique. Can someone assist?
Thanks
Rick
CREATE PROCEDURE usp_Person_GetVisits
(
@PersonID Varchar(30) = NULL
)
AS
SET NOCOUNT ON
SELECT *
FROM Visit
WHERE (PersonID= @PersonID)
ORDER BY AdmitDate
SET NOCOUNT OFF
GO
-
CREATE PROCEDURE usp_Person_GetVisits
(
@PersonID Varchar(30) = NULL
)
AS
SET NOCOUNT ON
SELECT visitType,
CASE WHEN visitType='I' THEN 'In-Patient' ELSE 'Out-Patient' END AS [visitTypeDesc]
FROM Visit
WHERE PersonID = @PersonID
ORDER BY AdmitDate
Check that visitType is ONLY O or I otherwise you will get 'Out-Patient' for values other than O and I
Another way would be to put the descriptions in another table and use a join, depends on how query performs and volumes
btw, it is good practice to list the columns for select rather than using * (unless this was for the example only)
Far away is close at hand in the images of elsewhere.
Anon. -
Thanks for solution and the tip
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply