CASE clause inside WHERE clause-CodePudding

can anyone tell me why I can not run this code in SQL Management studio? Thanks in advance.

SELECT  * FROM TabKontaktJednani
where
TabKontaktJednani.Typ IN (
CASE 'ERP'--(SELECT ALIAS FROM TabCisZam where LoginId = SUSER_NAME())
WHEN 'ERP'
THEN ('HeO','OST')
WHEN 'TO'
THEN ('SW','OST')
END)

CodePudding user response：

The reason you can't run that code is that CASE expressions can only return one scalar value, not a list of values.

CodePudding user response：

Your CASE expression returns a tuple. That is not allowed in SQL. But as MySQL supports a boolean data type, a CASE expression may result in a boolean:

SELECT *
FROM tabkontaktjednani
WHERE 
  CASE (SELECT alias FROM tabciszam WHERE loginid = suser_name())
    WHEN 'ERP' THEN typ IN ('HeO', 'OST')
    WHEN 'TO' THEN typ IN ('SW', 'OST')
  END;