SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Where Statement


Where Statement

Author
Message
lindavol
lindavol
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
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)
Sean Lange
Sean Lange
SSC Guru
SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)

Group: General Forum Members
Points: 61135 Visits: 17954
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 Modens 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)
lindavol
lindavol
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
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
kapil_kk
kapil_kk
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5236 Visits: 2767
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/
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (210K reputation)SSC Guru (210K reputation)SSC Guru (210K reputation)SSC Guru (210K reputation)SSC Guru (210K reputation)SSC Guru (210K reputation)SSC Guru (210K reputation)SSC Guru (210K reputation)

Group: General Forum Members
Points: 210283 Visits: 41973
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
ChrisM@Work
ChrisM@Work
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40518 Visits: 20000
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
lindavol
lindavol
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
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))
Snigdha Vartak
Snigdha Vartak
Say Hey Kid
Say Hey Kid (692 reputation)Say Hey Kid (692 reputation)Say Hey Kid (692 reputation)Say Hey Kid (692 reputation)Say Hey Kid (692 reputation)Say Hey Kid (692 reputation)Say Hey Kid (692 reputation)Say Hey Kid (692 reputation)

Group: General Forum Members
Points: 692 Visits: 247
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.
Sean Lange
Sean Lange
SSC Guru
SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)

Group: General Forum Members
Points: 61135 Visits: 17954
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 Modens 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)
Snigdha Vartak
Snigdha Vartak
Say Hey Kid
Say Hey Kid (692 reputation)Say Hey Kid (692 reputation)Say Hey Kid (692 reputation)Say Hey Kid (692 reputation)Say Hey Kid (692 reputation)Say Hey Kid (692 reputation)Say Hey Kid (692 reputation)Say Hey Kid (692 reputation)

Group: General Forum Members
Points: 692 Visits: 247
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!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search