August 6, 2008 at 3:07 pm
I have the following code:
DECLARE @StartRangeAmount Decimal(19,2)
DECLARE @EndRangeAmount Decimal(19,2)
SET @StartRangeAmount = 100
SET @EndRangeAmount = 200;
IF @LineFilter = ( 'Lines 1, 2 and 3' )
BEGIN
SELECT *
from #DLFiltered
WHERE Active = 1
AND DownlineLevel Between 1 AND 3
--AND SalesVolumeTotal BETWEEN 100 AND 200
or DownlineLevel = 0
OR ( COnsultantID IS NULL )
END
WHen I run it like this I get 54 records back and that is what I expect. But if I run it like this adding in the AND clause for the SalesVolumeTotal I lose everything even though there is valid data in the SalesVolumeTotal field:
IF @LineFilter = ( 'Lines 1, 2 and 3' )
BEGIN
SELECT *
from #DLFiltered
WHERE Active = 1
AND DownlineLevel Between 1 AND 3
AND SalesVolumeTotal BETWEEN 100 AND 200
or DownlineLevel = 0
OR ( COnsultantID IS NULL )
END
Please help.
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
August 6, 2008 at 3:25 pm
Please read "Forum Etiquette: How to post data/code on a forum to get the best help" at http://www.sqlservercentral.com/articles/Best+Practices/61537/
With the requested data/code, it might be possible to answer your questions. For example, what is the data type of column SalesVolumeTotal ?
SQL = Scarcely Qualifies as a Language
August 6, 2008 at 3:29 pm
First, when mixing ANDs with ORs, I strongly recommend using parenthesis to explicitly specify the order of operations.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 6, 2008 at 4:02 pm
I am sorry I figured it out. I found out the issue it was the parameters were giving me back exactly what I told them too. Sorry to have wasted your time
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
August 7, 2008 at 3:10 am
rbarryyoung (8/6/2008)
First, when mixing ANDs with ORs, I strongly recommend using parenthesis to explicitly specify the order of operations.
that's exactly what i was thinking. In many case there's no way around it, unless you want screwy results.
August 7, 2008 at 3:53 am
i think it should be like this...
IF @LineFilter in ( '1,2,3' )
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply