Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 333
  • Last Modified:

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

0
Keenetic
Asked:
Keenetic
  • 2
  • 2
1 Solution
 
MikeOM_DBACommented:

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

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now