WHERE clause conundrum

Question

WHERE clause conundrum

Frank The Tank

SSC Eights!

Points: 870
More actions
June 2, 2005 at 12:33 pm

#66131

I have a where clause in a stored proc select statement that is giving me fits. This should be simple, I'm sure I am overlooking something obvious, can someone help me out? The where clause needs to filter on a column, based on the values in several input parameters to the proc. Basically I need to either filter for data_source_code = '30' or data_source_code <> '30'. Sounds simple, yet I can't get it to work. Here's what I have tried thus far, the ANY modifier in the case statement causes the proc to error when I try to compile. Any suggestions are appreciated. "Data_source" is a code table containing all valid data_source_codes in our database.
and data_source_code           =
case
        when @service_indicator = 'Y' then '30'
        when @HWSW_direct_indicator = 'Y' or @HWSW_channel_indicator = 'Y'
        then ANY ( select data_source_code
                             from data_source
                             where data_source_code <> '30'
                          )
end
Now I also tried an IN rather than the = before the case statement, but SQL Server doesn't like that either. I'm stuck! What am I missing here?
Thanks

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Frank The Tank SSC Eights! Points: 870 More actions · Answer 1

Nevermind...

Since I am retrieving the data from a view I just coded around the issue by adding a column to the view containing a derived value that I can use as a simple filtering column in the proc.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 2

Ninja's_RGR'us

SSC Guru

Points: 294069

June 2, 2005 at 1:19 pm

#562707

Glad we could help .

rcavill Default port Points: 1482 More actions · Answer 3

Even though got around the issue, here is some code that should work. Effectively use the case first to test your parameters, then test the field you want to filter on.

and

case

when @service_indicator = 'Y'

then case when data_source_code = '30' then 'true' else 'false' end

when @HWSW_direct_indicator = 'Y' or @HWSW_channel_indicator = 'Y'

then case when data_source_code '30' then 'true' else 'false' end

else /* unknown condition */ 'false'

end = 'true'

Frank The Tank SSC Eights! Points: 870 More actions · Answer 4

Thanks for responding, even though the issue is now moot. Interesting approach RCAVILL, I'll keep that in mind in the future!

Fred Haeckl SSC Enthusiast Points: 110 More actions · Answer 5

I know you worked around this already, but can't you replace the
ANY ( select data_source_code
                             from data_source
                             where data_source_code <> '30'
                          )
with
( select top 1  data_source_code
                             from data_source
                             where data_source_code <> '30'
                          )