?
Solved

SQL Query Tuning

Posted on 2012-09-10
4
Medium Priority
?
326 Views
Last Modified: 2012-09-11
SQL Query Tuning

1) In the below query, I am trying to fetch Status only - 'A' or 'P'. But i am getting output with all the status available in the Table.

2) I have mentioned RxProName <> ICName. But still i am getting the Matching Values of both the columns.


select Member,Status,DOB,RxProName,ICName from dbo.VW_RxsToPrint
where	ICName like '%SC Med%'
or		ICName like '%NC Med%'
and		RTRIM(RxProName) <> RTRIM(ICName)
and		VW_RxsToPrint.Status in ('A','P')
order by VW_RxsToPrint.Status ASC

Open in new window

0
Comment
Question by:chokka
  • 2
4 Comments
 
LVL 25

Accepted Solution

by:
lwadwell earned 1600 total points
ID: 38385046
without brackets the priority of AND over OR has the query as:
select Member,Status,DOB,RxProName,ICName from dbo.VW_RxsToPrint
where	ICName like '%SC Med%'
or	(	ICName like '%NC Med%'
and		RTRIM(RxProName) <> RTRIM(ICName)
and		VW_RxsToPrint.Status in ('A','P')   )
order by VW_RxsToPrint.Status ASC

Open in new window

I think you might have wanted

select Member,Status,DOB,RxProName,ICName from dbo.VW_RxsToPrint
where  (   ICName like '%SC Med%'
or	 	ICName like '%NC Med%'   )
and		RTRIM(RxProName) <> RTRIM(ICName)
and		VW_RxsToPrint.Status in ('A','P')

Open in new window

0
 
LVL 13

Assisted Solution

by:sameer2010
sameer2010 earned 400 total points
ID: 38385676
Yes, also have a look at the following to understand the operator precendence.
http://msdn.microsoft.com/en-us/library/ms190276.aspx
0
 

Author Comment

by:chokka
ID: 38386928
@Sameer2010, I was not aware that there is Operator Precendence. Thank you for the link !
0
 

Author Closing Comment

by:chokka
ID: 38387050
Thank you lwadwell

select Member,Status,DOB,RxProName,ICName from dbo.VW_RxsToPrint
where  (   ICName like '%SC Med%'
or             ICName like '%NC Med%'   )
and            RTRIM(RxProName) <> RTRIM(ICName)
and            VW_RxsToPrint.Status in ('A','P')

Returns exact results
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question