Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Where Statement Expand / Collapse
Author
Message
Posted Monday, August 26, 2013 8:42 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, October 10, 2014 1:58 PM
Points: 4, Visits: 75
I want to do a "where statement" using "OR". When I try to run it, the right info does not come out. Can you see what I am doing wrong.
thank you

SELECT
SV00300.Service_Call_ID
,SV00300.Service_Description
,SV00300.DATE1
,SV00300.LOCATNNM
,SV00300.CUSTNAME
,SV00300.CUSTNMBR
,SV00300.ADRSCODE
,SV00300.Type_Call_Short
,SV00300.Contract_Number
,DAM_TechEE_Info.FRSTNAME
,DAM_TechEE_Info.Technician
FROM
SV00300
FULL OUTER JOIN WS30702
ON SV00300.Service_Call_ID = WS30702.WS_Job_Number
INNER JOIN DAM_TechEE_Info
ON WS30702.EMPLOYID = DAM_TechEE_Info.EMPLOYID

WHERE
SV00300.DATE1 Between (@Start_Date) and (@End_Date)and SV00300.CUSTNMBR in (@CUSTNMBR)
or SV00300.DATE1 Between (@Start_Date) and (@End_Date)and SV00300.LOCATNNM in (@LOCATNNM)
or SV00300.DATE1 Between (@Start_Date) and (@End_Date)and SV00300.Contract_Number in (@Contract_Number)
Post #1488406
Posted Monday, August 26, 2013 8:46 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:50 PM
Points: 13,325, Visits: 12,811
You are missing parenthesis. But you can actually streamline this quite a bit. I think you are looking for something along these lines.

WHERE SV00300.DATE1 Between (@Start_Date) and (@End_Date)
and
(
SV00300.CUSTNMBR in (@CUSTNMBR)
or SV00300.LOCATNNM in (@LOCATNNM)
or SV00300.Contract_Number in (@Contract_Number)
)



_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1488409
Posted Monday, August 26, 2013 10:39 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, October 10, 2014 1:58 PM
Points: 4, Visits: 75
THANK YOU - this works when I am in design and go to run
But does not work when I double click on the report and apply the parameters. I hit apply and nothing happens
Post #1488450
Posted Monday, August 26, 2013 11:05 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, December 14, 2014 11:09 PM
Points: 1,962, Visits: 2,406
lindavol (8/26/2013)
THANK YOU - this works when I am in design and go to run
But does not work when I double click on the report and apply the parameters. I hit apply and nothing happens

information you are providing is not enough for us to give you the answer...
please provide some more info on this...



_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1488551
Posted Tuesday, August 27, 2013 12:54 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:47 PM
Points: 35,770, Visits: 32,436
lindavol (8/26/2013)
THANK YOU - this works when I am in design and go to run
But does not work when I double click on the report and apply the parameters. I hit apply and nothing happens


That actually may be that because the WHERE clause is now written correctly and there are actually no rows to return.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1488573
Posted Tuesday, August 27, 2013 1:50 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 1:43 AM
Points: 6,890, Visits: 14,254
Why are you using IN and not = for these comparisons?

SV00300.CUSTNMBR in (@CUSTNMBR)
or SV00300.LOCATNNM in (@LOCATNNM)
or SV00300.Contract_Number in (@Contract_Number)

If any of these variables are a comma-delimited list, they won't be resolved by SQL Server in the way you are expecting. You'll need to rework the code to resolve the items in the list or alternatively use dynamic SQL.


“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1488594
Posted Wednesday, August 28, 2013 7:46 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, October 10, 2014 1:58 PM
Points: 4, Visits: 75
I tried changing it to the = it lets me be able to do the dates and the contract number with no problem but if I want just dates and location it says I need to put something in the contract number also.


WHERE SV00300.DATE1 Between (@Start_Date) and (@End_Date)and SV00300.LOCATNNM = (@LOCATNNM)

OR(SV00300.DATE1 Between (@Start_Date) and (@End_Date)and SV00300.Contract_Number = (@Contract_Number))
Post #1489225
Posted Wednesday, August 28, 2013 11:00 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, November 24, 2014 4:58 PM
Points: 522, Visits: 241
ChrisM@Work (8/27/2013)
Why are you using IN and not = for these comparisons?

SV00300.CUSTNMBR in (@CUSTNMBR)
or SV00300.LOCATNNM in (@LOCATNNM)
or SV00300.Contract_Number in (@Contract_Number)

If any of these variables are a comma-delimited list, they won't be resolved by SQL Server in the way you are expecting. You'll need to rework the code to resolve the items in the list or alternatively use dynamic SQL.


I agree! You may have to handle comma-delimited fields using custom function. I had faced such issue where it works when I select a single value from parameter but doesn't when I select multiple.
Post #1489333
Posted Wednesday, August 28, 2013 12:48 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:50 PM
Points: 13,325, Visits: 12,811
Snigdha Vartak (8/28/2013)
ChrisM@Work (8/27/2013)
Why are you using IN and not = for these comparisons?

SV00300.CUSTNMBR in (@CUSTNMBR)
or SV00300.LOCATNNM in (@LOCATNNM)
or SV00300.Contract_Number in (@Contract_Number)

If any of these variables are a comma-delimited list, they won't be resolved by SQL Server in the way you are expecting. You'll need to rework the code to resolve the items in the list or alternatively use dynamic SQL.


I agree! You may have to handle comma-delimited fields using custom function. I had faced such issue where it works when I select a single value from parameter but doesn't when I select multiple.


I would recommend that you view the link in my signature about splitting strings. It will provide you with details for the "custom function" that will split your string super fast and easy.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1489378
Posted Wednesday, August 28, 2013 1:25 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, November 24, 2014 4:58 PM
Points: 522, Visits: 241
Sean Lange (8/28/2013)
Snigdha Vartak (8/28/2013)
ChrisM@Work (8/27/2013)
Why are you using IN and not = for these comparisons?

SV00300.CUSTNMBR in (@CUSTNMBR)
or SV00300.LOCATNNM in (@LOCATNNM)
or SV00300.Contract_Number in (@Contract_Number)

If any of these variables are a comma-delimited list, they won't be resolved by SQL Server in the way you are expecting. You'll need to rework the code to resolve the items in the list or alternatively use dynamic SQL.


I agree! You may have to handle comma-delimited fields using custom function. I had faced such issue where it works when I select a single value from parameter but doesn't when I select multiple.


I would recommend that you view the link in my signature about splitting strings. It will provide you with details for the "custom function" that will split your string super fast and easy.


Thanks Sean!
Post #1489397
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse