Case Statement - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Case Statement

Author

Topic

werhardt
Constraint Violating Yak Guru

270 Posts

Posted - 2007-11-19 : 15:06:44

I am very new at SQL and I need some help if you can. A lot of my case statements have not been working for me. When I run my query the new names do post into the PRO_XTYP field. But when I want to add a criteria into my where clause it does not recognize the change. I have been trying to figure this out. My where clause would be…… Where Pro_XTYP = ‘Ancilary’ (nothing shows), but when I put Pro_XTYP = ‘ANCI’ then data pops up. Why isn’t my labels working? Do I need to write a IF Clause? Below is my case statement.

Pro_XTYP =
Case
When Pro_XTYP = 'PRIM' then 'Physician'
When Pro_XTYP = 'PROV' then 'Physician'
When Pro_XTYP = 'SPEC' then 'Physician'
When Pro_XTYP = 'ANCI' then 'Ancillary'
When Pro_XTYP = 'HOSP' then 'Hospital'
else Pro_XTYP
End,

anonymous1
Posting Yak Master

185 Posts

Posted - 2007-11-19 : 15:16:17

looking at this sql the field value for your where clause should be like this...
Where Pro_XTYP = 'ANCI'

it is not a good idea to reuse the actual field name as an alias name "Pro_XTYP" => "ALIASED_Pro_XTYP ="

that is likely the confusion. if not please post the data, table and full tsql

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-11-19 : 15:17:09

The CASE statement does not update your data. The data is still 'ANCI' and not 'Ancillary'. Your CASE statement is just changing how the data will be displayed.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

werhardt
Constraint Violating Yak Guru

270 Posts

Posted - 2007-11-19 : 16:01:17

Tara, I understand that my statement is just change how my data looks, but how do I get it to pull the criteria I need. I need to have PRIM, PROV and SPEC pull all together under the label Physician. If Someone needs to pull the physicians they wouldn't be able too.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-11-19 : 16:02:57

You should have a table that does this in your database and just join to it. You should never stuff data into your code like this. see: http://weblogs.sqlteam.com/jeffs/archive/2006/02/10/9002.aspx

- Jeff
http://weblogs.sqlteam.com/JeffS

werhardt
Constraint Violating Yak Guru

270 Posts

Posted - 2007-11-20 : 15:39:09

I just wanted to thank everyone for their help regarding my issue. Everything is working properly now!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources