• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 329
  • Last Modified:

SQL Query Tuning

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
chokka
Asked:
chokka
  • 2
2 Solutions
 
lwadwellCommented:
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
 
sameer2010Commented:
Yes, also have a look at the following to understand the operator precendence.
http://msdn.microsoft.com/en-us/library/ms190276.aspx
0
 
chokkaStudentAuthor Commented:
@Sameer2010, I was not aware that there is Operator Precendence. Thank you for the link !
0
 
chokkaStudentAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now