i having issue here. trying use case statement determine filter use. ok use single values, not ok use select statement results.
declare @singlevalue int = 0 select t1.field1 , t1.field2 , t1.field3 tbltable1 t1 inner join tbltable2 t2 on t1.field1 = t2.field t1.field2 in (select case when @singlevalue = 0 (select field1 tbltable3) else t1.field2 end) , t2.field2 = (case when @singlevalue = 0 t2.field2 else @singlevalue end)
once @singlevalue set value '123' working fine, moment when set value '0' (zero) returns error 'subquery returned more 1 value. not permitted when subquery follows =, !=, <, <= , >, >= or when subquery used expression.'
the problem start '(select field1 tbltable3)' in case statement. how can work around multiple values in case statement.
thanks.
maybe like:
declare @singlevalue int = 0 select t1.field1 , t1.field2 , t1.field3 tbltable1 t1 inner join tbltable2 t2 on t1.field1 = t2.field ( ( @singlevalue <> 0 ) or ( t1.field2 in ( select field1 tbltable3) ) ) , ( ( @singlevalue = 0 ) or ( t2.field2 = @singlevalue ) )
Comments
Post a Comment