CASE Statement combining two previously interrogated elements

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.

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

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

:sniped:

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

Awesome -- worked great. Thanks!!

Thanks!!