WHERE statement with CASE clause to return one value or all others

Question

I want to ask you for help about the CASE statement in WHERE clause.

I want to achieve to get rows where SEMI_GV column with value 'SEMI' if description of division (IC.Description) is containing also the '%semi%'. This is actually working, but when if column SEMI_GV has different value than 'SEMI' (e.g. GV or NULL), I am not sure how to return all values excluding the 'SEMI'. Below is code I tried, but actually it's not working as I am not sure what should be used after second THEN statement to filter everything else.

...
WHERE 
    procur.SEMI_GV LIKE
        CASE 
            WHEN IC.[Description] LIKE '%semi%' THEN 'SEMI'
            WHEN IC.[Description] NOT LIKE '%semi%' THEN ???
        END

Thank you in advance for support!

EDIT: the result table I want to filter out is this

KUNNR   NAME1   LAND1   BRAN1   VKORG   VTWEG   SPART   ZTERM   Resp_FAM    Description MAIL    SEMI_GV POSITION
0050000001  Dummy account   GB  11002   1101    10  00  ECCP    GCN UK Inside Sales [email protected]   NULL    BC
0050000001  Dummy account   GB  11002   1101    10  00  ECCP    GCN UK Inside Sales [email protected]   SEMI    BC

In where clause I want to return only one row based on condition:

• if Description contains "SEMI" and SEMI_GV contains SEMI, return this row (2nd row)
• if Description contains anything else, return this row (1st row)

The WHERE clause is working for the case when description contains the 'semi' in it, however I tried to put the another conditions like:

WHERE 
    procur.POSITION = 'BC'
AND
    procur.SEMI_GV LIKE
        CASE 
            WHEN IC.[Description] LIKE '%semi%' THEN 'SEMI'
            WHEN IC.[Description] NOT LIKE '%semi%' THEN 
                CASE 
                    WHEN SEMI_GV IS NOT NULL THEN 'GV'
                ELSE NULL

However I cannot get through the NULL value because "LIKE" statement on the beginning.

Answer 1

From what I can gather, it sounds like you want a results set for when the IC.[Description] column contains "semi" and a different results set for when it doesn't (but lumped into one datatable).

If I've interpreted it correctly, you may be better of looking at a UNION, i.e.

SELECT *
FROM MyTable
WHERE procur.SEMI_GV = 'SEMI' 
      AND IC.[Description] LIKE '%semi%'
UNION ALL
SELECT * 
FROM MyTable 
WHERE procur.SEMI_GV != 'SEMI' 
      OR IC.[Description] NOT LIKE '%semi%';

Without more information on the data involved and what the end result should be, I'm not sure how much more I can help.