Sample Clause After Filtering With A WHERE Clause with Table Joins

Hello -

Im attempting to pull a 5% sample from the resulting dataset of a table join.  However, when trying to run this code I get an error.  I believe its because the sample clause is getting confused as to which table to sample.  Is there a way to get around this? What is the smartest way to get around this limitation of the Sample Clause?

Sample code below.

Thanks in advance.
SELECT a.Contact a.ContactNum b.Address c.Phone
FROM a.ExecInfo, b.Location, c.Demographics
SAMPLE(5)
WHERE a.LinkID = b.LinkID
   AND b.LinkID = c.LINKID;

Open in new window

KeeneticAsked:
Who is Participating?
 
MikeOM_DBAConnect With a Mentor Commented:

Try this:

WITH Q0 AS (
SELECT a.Contact a.ContactNum b.Address c.Phone
FROM a.ExecInfo, b.Location, c.Demographics
WHERE a.LinkID = b.LinkID
   AND b.LinkID = c.LINKID)
SELECT * FROM Q0 SAMPLE(5);

Open in new window

0
 
MikeOM_DBACommented:

-- Or --
 Maybe you need to sample the largest table only:

WITH Q0 AS (
SELECT Contact, ContactNum, LinkID
  FROM ExecInfo SAMPLE(5))
SELECT a.Contact, a.ContactNum, b.Address c.Phone
  FROM Q0 a, b.Location, c.Demographics
 WHERE a.LinkID = b.LinkID
   AND b.LinkID = c.LINKID);

Open in new window

0
 
dxladnerCommented:
The SAMPLE clause in ORACLE is basically:
SAMPLE(the number that you want )

The are some limitations to this clause: 1. Discrepancies in actual percentage in the returned data2. Using views and certain joins.  
0
 
hqassapCommented:
Try this:

SELECT *
  FROM (SELECT a.contact, a.contactnum, b.address, c.phone
          FROM a.execinfo, b.LOCATION, c.demographics
         WHERE a.linkid = b.linkid AND b.linkid = c.linkid)
 WHERE ROWNUM < 6

Open in new window

0
 
dxladnerCommented:
The RowNum clause will get you the first 6 rows of data only. If that is all you are looking for  then use hgassap's example. But using the RowNum will not pull RANDOM sampling throughout the database. You must use the SAMPLE keyword.
0
All Courses

From novice to tech pro — start learning today.