error in using case with like sql anywhere query

Question

I want to use like in case statement and search string from a particular colmun if any part of that string contains 'Moderate to severe aortic stenosis' THEN instead of full string I want to display 'Moderate to Severe' if any part of that string contains 'Severe aortic stenosis with' THEN instead of full string I want to display 'Severe' if that string contains 'moderate aortic stenosis present' THEN instead of full string I want to display 'Moderate'

Below is my query not working some systax error

SELECT (CASE  a.Column when  LIKE '%Moderate to severe aortic stenosis%' THEN 'Moderate to Severe'
            when  LIKE '%Severe aortic stenosis with%' THEN 'Severe'
            when  LIKE '%moderate aortic stenosis present%' THEN 'Moderate' else ' ' end)
       from MyTable a

Please help

My daughter was born with moderate to severe aortic stenosis. Keep up the good work :) — crthompson, Apr 23 '14 at 20:21

score 3 · Answer 1 · answered Apr 23 '14 at 20:19

Without seeing the error message it's hard to tell what the problem is.

But I suspect you want to write this like this:

SELECT CASE when a.Column LIKE '%Moderate to severe aortic stenosis%' THEN 'Moderate to Severe'
            when a.Column LIKE '%Severe aortic stenosis with%' THEN 'Severe'
            when a.Column LIKE '%moderate aortic stenosis present%' THEN 'Moderate' 
            else ' ' 
       end
       from MyTable a

I've moved the a.Column into the when portion of the case statement. I think you can only move it outside when doing an equality comparison rather than a fuzzy match.

potashin · Accepted Answer · 2014-04-24T08:02:29.563

1

There are syntax errors in your query:

SELECT 
  CASE 
     WHEN a.Column LIKE '%Moderate to severe aortic stenosis%' THEN 'Moderate to Severe'
     WHEN a.Column LIKE '%Severe aortic stenosis with%' THEN 'Severe'
     WHEN a.Column LIKE '%moderate aortic stenosis present%' THEN 'Moderate' else ' ' 
  END
FROM 
 MyTable a

Field a.Column should appear after every WHEN in comparison with LIKE.

edited Apr 24 '14 at 08:02

answered Apr 23 '14 at 20:18

potashin

44,205
11
83
107

@Jim "*Column should appear after every WHEN*" is not entirely true. For equality conditions (`=`) it is allowed to have it only once: `case some_col when 1 the 'foo' when 2 then 'bar' else 'foobar' end` is valid SQL. But this **only** works for a comparison with `=` not for anything else and that's why for `LIKE` the column must be repeated – Apr 23 '14 at 22:44

error in using case with like sql anywhere query

2 Answers2