Case and Like problems - 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 and Like problems

Author

Topic

SimonJ
Starting Member

2 Posts

Posted - 2012-09-12 : 06:09:01

Hi,

Im quite new to SQL Server programming and am trying to take some old data from a crystal report and set up a column in a view in SQL Server Management Studio.

The code passed to me is below and uses quite a few tables.

WHEN{dmAdminUnits.dmAUWardDesc} like ["North East", "North West", "Central", "Home Scotland"]
and {dmAdminUnits.dmAUSchemeCode} Like "02*" then "North Shared Ownership" else
WHEN {dmAdminUnits.dmAUSchemeCode} Like "02*" then "South Shared Ownership" else
WHEN {dmAdminUnits.dmAUWardDesc} like ["North East", "North West", "Central", "Home Scotland"] and {dmAdminUnits.dmAUSchemeCode} Like "09*" then "North Leasehold" else
WHEN {dmAdminUnits.dmAUSchemeCode} Like "09*" then "South Leasehold" else
WHEN {dmAdminUnits.dmAUWardDesc} like "Live Smart*" Then "Live Smart" else
WHEN {dmAdminUnits.dmAUArrearsOfficerCode} like "SE*" then "South East & South West" else
WHEN {dmAdminUnits.dmAUArrearsOfficerCode} like "SL*" then "London" else
WHEN {dmAdminUnits.dmAUArrearsOfficerCode} like "SM*" then "East Midlands & Eastern England" else
{dmAdminUnits.dmAUWardDesc}

What i want to do is put this in a CASE format so i can hard code some regions into the view as a column named "Ward"

Is there an easy way to do this?

Thank You

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-09-12 : 07:04:27

I don't think that anyone here is able to understand what you want.

You are giving just a fragment of code and it doesn't look like T-SQL (and we are on MS SQL Server here).

Please check if you would be able to help with ONLY the given information in your post...

Too old to Rock'n'Roll too young to die.

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2012-09-12 : 07:19:11

OK, so why not write your best guestimate of the case statement?

something like

CASE
WHEN dmAdminUnits.dmAUWardDesc IN ('North East', 'North West', 'Central', 'Home Scotland') AND
dmAdminUnits.dmAUSchemeCode LIKE '02%' THEN 'North Shared Ownership'
WHEN dmAdminUnits.dmAUSchemeCode Like '02%' THEN 'South Shared Ownership'
WHEN dmAdminUnits.dmAUWardDesc ....

etc

SimonJ
Starting Member

2 Posts

Posted - 2012-09-12 : 08:07:03

Hi Rick D

Thank you very much. As soon as you listed the start i could do the rest. Im so used to working with different syntax that i found this quite difficult to do.

The code extract was a crystal report extract that needed to be replicated in SQL Server and i had no idea how to do it.

Thank you again

Simon

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources