I am running into a limitation of the Having Clause and need to know if anybody has workaround.
It seems that SQL does not handle the IN statement well. EXAMPLE :
CASE WHEN DD.SomeField = '915' AND DD.SomeOtherField <> '1X50' THEN DD.AnotherField ELSE 0 END
works just fine, but can not handle
CASE WHEN DD.SomeField IN ('510','511','512','520','530','540','610','611') THEN DD.AnotherField ELSE 0 END
throws an error :
Server: Msg 8121, Level 16, State 1, Procedure BLAH_VW, Line 4
Column 'DD.AnotherField' is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause.
So I fixed it by putting it into a User Defined Function(UDF). But now I want to create an Clustered Index on this View and SQL does not like to play nice with UDF's (non deterministic error on the UDF).
Ideally I would keep this code in a UDF and be able to create the Clustered Index - but I fear the restrictions are too heavy to accomplish.
Suggestions? If you have links to good resources that would be great too.