Solved

SQL Like Query

Posted on 2013-06-26
2
294 Views
Last Modified: 2013-06-26
Hi all I have the following query

SELECT     TOP (100) PERCENT dbo.cname.cn_ref, dbo.cname.cn_anal, dbo.cname.cn_catag, dbo.ctran.ct_date, dbo.ctran.ct_quan
FROM         dbo.ctran RIGHT OUTER JOIN
                      dbo.cname ON dbo.ctran.ct_ref = dbo.cname.cn_ref
WHERE     (dbo.ctran.ct_type = 'I') AND (dbo.ctran.ct_date >= '2013-05-01')

which returns me 401 Records if I then add a like statement

SELECT     TOP (100) PERCENT dbo.cname.cn_ref, dbo.cname.cn_anal, dbo.cname.cn_catag, dbo.ctran.ct_date, dbo.ctran.ct_quan
FROM         dbo.ctran RIGHT OUTER JOIN
                      dbo.cname ON dbo.ctran.ct_ref = dbo.cname.cn_ref
WHERE     (dbo.ctran.ct_type = 'I') AND (dbo.ctran.ct_date >= '2013-05-01') AND (dbo.cname.cn_catag LIKE '%STD%')

I then get 177 records I then want to add a further like to include LIKE '%HBNE%')

this however returns thousends of records yet I f I run the query with just the one like

SELECT     TOP (100) PERCENT dbo.cname.cn_ref, dbo.cname.cn_anal, dbo.cname.cn_catag, dbo.ctran.ct_date, dbo.ctran.ct_quan
FROM         dbo.ctran RIGHT OUTER JOIN
                      dbo.cname ON dbo.ctran.ct_ref = dbo.cname.cn_ref
WHERE     (dbo.ctran.ct_type = 'I') AND (dbo.ctran.ct_date >= '2013-05-01') AND (dbo.cname.cn_catag LIKE '%HBNE%')

I only get 12 records

so basically I would like the original statement to include both STD and HBNE

John
0
Comment
Question by:pepps11976
2 Comments
 
LVL 16

Accepted Solution

by:
EvilPostIt earned 500 total points
ID: 39278348
SELECT     TOP (100) PERCENT dbo.cname.cn_ref, dbo.cname.cn_anal, dbo.cname.cn_catag, dbo.ctran.ct_date, dbo.ctran.ct_quan
FROM         dbo.ctran RIGHT OUTER JOIN
                      dbo.cname ON dbo.ctran.ct_ref = dbo.cname.cn_ref
WHERE     (dbo.ctran.ct_type = 'I') AND (dbo.ctran.ct_date >= '2013-05-01') AND (dbo.cname.cn_catag LIKE '%HBNE%' OR dbo.cname.cn_catag LIKE '%STD%')

Open in new window

0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39280187
regarding TOP 100 PERCENT..
the optimizer recognizes that TOP 100 PERCENT qualifies all rows and does not need to be computed at all.  It gets removed from the query plan, and there is no other reason to do an intermediate sorting operation.  As such, the output isn't returned in any particular order.
fro MSDN : TOP 100 Percent ORDER BY Considered Harmful.
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

746 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now