Help creating Derived Column

Question

Post reply

Help creating Derived Column

Vertigo44

SSCarpal Tunnel

Points: 4055
More actions
July 27, 2011 at 9:28 am

#390062

declare @pattype char(20);
set @pattype =
CASE
WHEN patient_type = '1'
THEN @pattype = "Inpatient"
WHEN patient_type = '2'
THEN @pattype = "Outpatient"
END
select patient_type as PATCODE, @pattype as PATDESC from od_ar_demographics
====================================================================
I think you can see what I am trying to do but I keep getting errors like
'A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations'
or
'Incorrect Syntax near ='

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

what you want to do is all the case logic inside the SELECT, without using that extra variable.

something like this is syntactically correct...let me know if it is logically correct:

select patient_type as PATCODE,

PATDESC=

CASE

WHEN patient_type = '1'

THEN "Inpatient"

WHEN patient_type = '2'

THEN "Outpatient"

END

from od_ar_demographics

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Vertigo44 SSCarpal Tunnel Points: 4055 More actions · Answer 2

Lowell (7/27/2011)
what you want to do is all the case logic inside the SELECT, without using that extra variable.
something like this is syntactically correct...let me know if it is logically correct:
select patient_type as PATCODE,
PATDESC=
CASE
WHEN patient_type = '1'
THEN "Inpatient"
WHEN patient_type = '2'
THEN "Outpatient"
END
from od_ar_demographics

Msg 207, Level 16, State 1, Line 6

Invalid column name 'Inpatient'.

Msg 207, Level 16, State 1, Line 8

Invalid column name 'Outpatient'.

But it did parse!

Vertigo44 SSCarpal Tunnel Points: 4055 More actions · Answer 3

Got it! Thanks Lowell!!! I really appreciate it!

select patient_type as PATCODE,

PATDESC=

CASE

WHEN patient_type = '1'

THEN 'Inpatient'

WHEN patient_type = '2'

THEN 'Outpatient'

END

from od_ar_demographics