Case with Substring return more than 1 value?

Transact-SQL
1

I'm working with an EMR that has a proprietary query designer so I'm somewhat limited in what I can do as far as SQL commands.

I have one field (PTC_BASIC.MENTAL_STATUS) that puts a 1 if the field is checked and 0 if it is not. For instance if box 1 and box 6 are both checked it will return 1000010.

I need to write a report that will return the values that are checked so if the first character is a 1 return 'Oriented', if character 2 = 1 then return 'Forgetful'. If 2 boxes are checked then both values should be returned.

Here is what I have, but I'm only getting data returned if all fields are checked.

Case WHEN SUBSTRING(PTC_BASIC.MENTAL_STATUS,1,1) = 1 then 'Oriented' end + ' ' + Case WHEN SUBSTRING(PTC_BASIC.MENTAL_STATUS,2,1) = 1 then 'Comatose' end + ' ' + Case WHEN SUBSTRING(PTC_BASIC.MENTAL_STATUS,3,1) = 1 then 'Forgetful' End + ' ' + Case WHEN SUBSTRING(PTC_BASIC.MENTAL_STATUS,4,1) = 1 then 'Depressed' End + ' ' + Case WHEN SUBSTRING(PTC_BASIC.MENTAL_STATUS,5,1) = 1 then 'Disoriented' End + ' ' + Case WHEN SUBSTRING(PTC_BASIC.MENTAL_STATUS,6,1) = 1 then 'Lethargic' End + ' ' + Case WHEN SUBSTRING(PTC_BASIC.MENTAL_STATUS,7,1) = 1 then 'Agitated' End

So based on a value of 1000010 I would like the output to be Oriented Lethargic

2

Actually I just figured it out. Had to add and else after each Case statement so it will look like this now:

Case WHEN SUBSTRING(PTC_BASIC.MENTAL_STATUS,1,1) = 1 then 'Oriented' else ' ' end + ' ' + Case WHEN SUBSTRING(PTC_BASIC.MENTAL_STATUS,2,1) = 1 then 'Comatose' else ' ' end

3

:slight_smile:

You've probably figured out the reason, but in case not: if you don't have an ELSE then SQL will give you "NULL" for the ELSE condition, and NULL concatenated to the other string(s) would result in NULL. So when all the field were checked then there was no ELSE execution path ... and only in that condition did it work OK.

Your solution of using ELSE is what I would do, but I wonder if there is a smarter way to use the binary 10101010 value numerically, rather than the multiple SUBTRINGs, as I suspect it would be more efficient.