CASE Statement combining two previously interrogated elements

Transact-SQL
1

Be gentle please, I'm new to this forum and a baby SQL user.

I have a CASE Statement where I want to evaluate two data elements, then evaluate a combination of them, as so:

Case
when vm.minorityowned = 1 then 'Minority'
when vm.femaleowned = 1 then 'Female'
when vm.minorityowned= 1 and vm.femaleowned = 1 then 'Minority Female'
end As 'MinorityType',

I get results back for Minority and for Female, but not for Minority Female. What's wrong with my expression? Any help appreciated. Thanks.

2

CASE statements are evaluated in order, and the first match ends the tests.

Case
when vm.minorityowned= 1 and vm.femaleowned = 1 then 'Minority Female'
when vm.minorityowned = 1 then 'Minority'
when vm.femaleowned = 1 then 'Female'
end As 'MinorityType',

1 Like
3

Changing the sequence might be enough. Only the first WHEN which is true will be acted on, so:

Case
when vm.minorityowned= 1 and vm.femaleowned = 1 then 'Minority Female'
when vm.minorityowned = 1 then 'Minority'
when vm.femaleowned = 1 then 'Female'
end As [MinorityType],

other possibility is to concatenate them

RTRIM(    -- Remove any trailing spaces
    CASE 
    WHEN vm.minorityowned = 1 then 'Minority '
    ELSE ''
    END
  + CASE
    WHEN vm.femaleowned = 1 then 'Female '
    ELSE ''
    END
)
As [MinorityType],

Suggest you don't use single-quotes around alias names (such as [MinorityType])

1 Like
4

:sniped:

Haven't used one of those on this site since it changed to the new software AFAICR !

5

Awesome -- worked great. Thanks!!

6

Thanks!!