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
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26412 Visits: 17557
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
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3119 Visits: 2766
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 (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87913 Visits: 41126
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
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16526 Visits: 19557
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
Mr or Mrs. 500
Mr or Mrs. 500 (570 reputation)Mr or Mrs. 500 (570 reputation)Mr or Mrs. 500 (570 reputation)Mr or Mrs. 500 (570 reputation)Mr or Mrs. 500 (570 reputation)Mr or Mrs. 500 (570 reputation)Mr or Mrs. 500 (570 reputation)Mr or Mrs. 500 (570 reputation)

Group: General Forum Members
Points: 570 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
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26412 Visits: 17557
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
Mr or Mrs. 500
Mr or Mrs. 500 (570 reputation)Mr or Mrs. 500 (570 reputation)Mr or Mrs. 500 (570 reputation)Mr or Mrs. 500 (570 reputation)Mr or Mrs. 500 (570 reputation)Mr or Mrs. 500 (570 reputation)Mr or Mrs. 500 (570 reputation)Mr or Mrs. 500 (570 reputation)

Group: General Forum Members
Points: 570 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