Solved

Filtering records in the FROM clause vs the WHERE clause

Posted on 2013-10-25
5
386 Views
Last Modified: 2013-10-25
I have a report that needs to pull records that are equal to S, R, or M.

So I need to pull records from OD_RECORD_TYPE = S,R,M

1. How do I write that in either section
2.......and does either affect the results since I will need these records for various parts of the report for several columns of data.
0
Comment
Question by:ruavol2
  • 3
5 Comments
 
LVL 65

Accepted Solution

by:
Jim Horn earned 350 total points
Comment Utility
2.  No, it doesn't affect the execution of the query.

1. Either of the below works, <air code, so making a few assumptions>
SELECT goo, foo, boo
FROM YourTable
WHERE ODS_RECORD_TYPE IN ('S', 'R', 'M') 

SELECT yt.goo, yt.foo, yt.boo, sot.wahoo
FROM YourTable yt
   INNER JOIN SomeOtherTable ON yt.ID = sot.ID AND sod.ODS_RECORD_TYPE IN ('S', 'R', 'M') 

Open in new window

0
 
LVL 65

Assisted Solution

by:Jim Horn
Jim Horn earned 350 total points
Comment Utility
>I have a report that needs to pull records that are equal to S, R, or M.
You'll want to expand on this.  
*  If it always does this, it can be hard-coded into the T-SQL that is the data source for the data set behind your report.
*  If this will be a user-selectable parameter value, then you'll want to set it up in SSRS as such.
0
 
LVL 100

Assisted Solution

by:mlmcc
mlmcc earned 150 total points
Comment Utility
SO long as you are using inner joins it doesn't matter.

If you are using an outer join it can make a difference, particularly with Crystal

Modifying jimhorn's example above
SELECT yt.goo, yt.foo, yt.boo, sot.wahoo
FROM YourTable yt
   LEFT OUTER JOIN SomeOtherTable ON yt.ID = sot.ID AND sod.ODS_RECORD_TYPE IN ('S', 'R', 'M')  

Open in new window


That will return all records from YourTable with a matching record that has an S, R, or M in the ODS_Record_Type field.  It will also return records from YourTable that don't have a matching record.

If you do it in the where clause the join is treated as INNER so only records from YourTable with a matching record will be returned.

mlmcc
0
 

Author Closing Comment

by:ruavol2
Comment Utility
That took care of it....gotta love Experts-Exchange.com
Its like a beatiful woman except much easier to deal with........!
0
 
LVL 65

Expert Comment

by:Jim Horn
Comment Utility
^^^  Dude, don't do that.  I had coffee in my mouth when I read the above line, and nearly sprayed.

Thanks for the grade.  Good luck with your project.  -Jim
0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
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…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how the fundamental information of how to create a table.

762 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

6 Experts available now in Live!

Get 1:1 Help Now