CASE WHEN and LIKE error - 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

SQL Server 2012 Forums

Transact-SQL (2012)

CASE WHEN and LIKE error

Author

Topic

jimtimber
Yak Posting Veteran

60 Posts

Posted - 2014-09-05 : 08:09:37

Hi guys,

Can someone tell me what I need to do with the case statement below for it to work please.
Basically, 'resourcenotes' is a text field. If it contains '%available%' then I want it to return '100%', if it contains '%additional vacancies%' or '%respite%' then return '50%' ELSE return '0%'. I've highlighted the bit that won't work below.
Thanks
Jim
SELECT dbo.Tbl_Families.AgencyCode, dbo.Tbll_Agents.AgencyName, dbo.Tbl_Families.FamiliesID, dbo.Tbl_Families.ApplicantRef, dbo.Tbl_FamilyDetails.ApplicantTown,
dbo.Tbl_FamilyDetails.ApplicantPostcode, dbo.Tbl_Families.EthnicOriginF, dbo.Tbl_Families.EthnicOriginM, dbo.Tbl_Families.ApplicantCode,
dbo.Tbl_Families.DateApproved, dbo.Tbl_Families.ResourceNotes, dbo.Tbl_Families.AccNoChildren, dbo.Tbl_Families.ResourceNoChildren,

CASE WHEN dbo.Tbl_Families.ResourceNotes like '%available%' THEN '100%'
When like '%additional vacancies%' THEN '50%'
When like '%respite only%' THEN '50%'
Else '0%' END As 'Availability'

FROM dbo.Tbl_FamilyDetails INNER JOIN
dbo.Tbl_Families ON dbo.Tbl_FamilyDetails.FamiliesID = dbo.Tbl_Families.FamiliesID INNER JOIN
dbo.Tbll_Agents ON dbo.Tbl_Families.AgencyCode = dbo.Tbll_Agents.AgencyCode
WHERE (NOT (dbo.Tbl_Families.AgencyCode LIKE N'other respite arrangement'))

Jim

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-09-05 : 08:17:33

The simple type of case expression allows only an equality check. You cannot use like clause. So change it like this:

...CASE 
WHEN dbo.Tbl_Families.ResourceNotes like '%available%' THEN '100%'
When dbo.Tbl_Families.ResourceNotes like  '%additional vacancies%' THEN '50%'
When dbo.Tbl_Families.ResourceNotes like  '%respite only%' THEN '50%'
Else '0%' END As 'Availability'
...

jimtimber
Yak Posting Veteran

60 Posts

Posted - 2014-09-05 : 08:21:24

quote:
Originally posted by James K

The simple type of case expression allows only an equality check. You cannot use like clause. So change it like this:
...CASE
WHEN dbo.Tbl_Families.ResourceNotes like '%available%' THEN '100%'
When dbo.Tbl_Families.ResourceNotes like '%additional vacancies%' THEN '50%'
When dbo.Tbl_Families.ResourceNotes like '%respite only%' THEN '50%'
Else '0%' END As 'Availability'
...

Ah, this works for me now. So basically when doing the above, I repeat the field I want it to look in as many times as I query it in the CASE statement? Thank you James.

Jim

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources