-4
  SELECT * FROM Table1 A INNER JOIN Table2 B ON A.COnfigId = B.COnfigId JOIN Table3 C ON C.TypeId = B.TypeId INNER JOIN Table4 D ON D.ChannelId = B.ChannelId INNER JOIN Table5 E ON E.NoticeId = A.NoticeId WHERE E.NoticeCode = CASE 
    WHEN (
            D.Channel = 'Post'
            OR (
                D.Channel = 'Email'
                AND C.IsValue = 1
                )
            )
        THEN 'P'
    WHEN (
            (
                D.Channel = 'Email'
                AND C.IsValue = 0
                )
            OR D.Channel = 'Msg'
            )
        THEN 'I'
            OR 'E'
            OR 'N'
    END

OR condition in THEN 'I' OR 'E' OR 'N' is not working.

Is there any alternative for this? Please suggest.

Dale K
  • 25,246
  • 15
  • 42
  • 71
Raj P
  • 29
  • 3
  • 2
    Your query makes no sense, and you haven't explained it elsewhere.. Think about if I sent you to the shops, saying "If they have eggs or they have bread and it is freshly baked then potatoes or carrots or cereal" - it makes no sense – Caius Jard Feb 01 '21 at 17:19
  • 2
    `CASE` is an **expression** in T-SQL (like `a+b`) that returns **one, atomic value** - not a block of code, not a "list" of values - just ONE, atomic value$ – marc_s Feb 01 '21 at 17:25
  • 3
    [Please do not upload images of code/errors when asking a question.](//meta.stackoverflow.com/q/285551) – Thom A Feb 01 '21 at 17:36
  • QUestion Edited – Raj P Feb 01 '21 at 17:49
  • [This](https://stackoverflow.com/a/10260297/92546) answer may help clear up how to use a `case` _expression_ in an `on` clause. The same is true of a `where` clause. Performance is likely to suffer, though a `recompile` hint might help. – HABO Feb 01 '21 at 19:50

1 Answers1

0

Don't use case. Something like this:

where (a.code = 'A' and b.code = 'P') or
      (a.code = 'B' and a.value = 1 and b.code = 'P') or
      (a.code = 'B' and a.value <> 1 and b.code in ('I', 'N', 'E'))

and so on for whatever your full logic is.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786