How to use SAMPLE function when doing LEFT JOINS

General

How to use SAMPLE function when doing LEFT JOINS

I have a query that joins a half dozen tables. The results set has maybe 40 million records in it but during development and testing I want to work with only 25% of that. The SAMPLE function doesn't work when I have the sampled table followed by a LEFT JOIN. Does sampling not work in this situation and I need to do an in-line query of the sampled table or something like that? The error message is "expected something like ')' between a decimal number and the 'LEFT' keyword." Sorry for the difficulty reading this but I couldn't figure out how to insert hard returns. select from WFPDMHM.V_HM_ACCT_BAP sample .25 LEFT JOIN D507201.V_HM_CE_CUST_CMT_DLY_SSD ON V_HM_ACCT_BAP.CLNT_NUM=V_HM_CE_CUST_CMT_DLY_SSD.CLNT_NR AND V_HM_ACCT_BAP.LOAN_NUM=V_HM_CE_CUST_CMT_DLY_SSD.LN_NR ( more left joins) ;
  • SAMPLE
Tags (1)
2 REPLIES
Senior Apprentice

Re: How to use SAMPLE function when doing LEFT JOINS

Apply the SAMPLE on the outermost session using a Derived Table:

select from 
 ( select * from WFPDMHM.V_HM_ACCT_BAP sample .25) AS V_HM_ACCT_BAP
LEFT JOIN D507201.V_HM_CE_CUST_CMT_DLY_SSD
  ON V_HM_ACCT_BAP.CLNT_NUM=V_HM_CE_CUST_CMT_DLY_SSD.CLNT_NR 
AND V_HM_ACCT_BAP.LOAN_NUM=V_HM_CE_CUST_CMT_DLY_SSD.LN_NR

Btw, for source code there's the Insert Code button, the 6th icon from left.

Re: How to use SAMPLE function when doing LEFT JOINS

That's the solution I was expecting. Thanks! My browser doesn't allow JavaScript due to the paranoia of our IT security people so I don't have any site buttons for formatting, can't insert hard returns, and I can't Accept the Solution. But I've seen a Lot of replies from you and I greatly appreciate your help to the community.