Using Analytical Clauses with DISTINCT

Question

The purpose is to query multiple tables using DISTINC (if not I get millions of rows as results), but at the same time using sample to gather a 10% sample from the results that should all be unique. I am getting the following error:

ORA-01446: cannot select ROWID from, or sample, a view with DISTINCT, GROUP BY, etc.

Here is the code I have written:

WITH V AS (SELECT DISTINCT AL1."NO", AL3."IR", AL1."ACCT", AL3."CUST_DA", AL1."NA", 
                AL3."1_LINE", AL3."2_LINE", AL3."3_LINE", AL1."DA", 
                AL1."CD", AL1."TITLE_NA", AL1."ENT_NA", AL3."ACCT", 
                AL3."ACCTLNK_ENRL_CNT" 

FROM "DOC"."DOCUMENT" AL1, "DOC"."VNDR" AL2, "DOC"."CUST_ACCT" AL3 

WHERE (AL1."ACCT"=AL2."VNDR" 

AND AL2."ACCT"=AL3."ACCT")  

AND ((AL1."IMG_DA" >= Trunc(sysdate-1) 

AND AL1."PROC"='A' 

AND AL3."ACCT"<>'03')))


SELECT * FROM V SAMPLE(10.0)

Answer 1

You can't sample a join view like this.

Simpler test case (MCVE):

with v as
     ( select d1.dummy from dual d1
              join dual d2 on d2.dummy = d1.dummy
     )
select * from v sample(10);

Fails with:

ORA-01445: cannot select ROWID from, or sample, a join view without a key-preserved table

The simplest fix would be to move the sample clause to the driving table:

with v as
     ( select d1.dummy from dual sample(10) d1
              join dual d2 on d2.dummy = d1.dummy
     )
select * from v;

I would therefore rewrite your view as:

with v as
     ( select distinct
              d.no
            , a.ir
            , d.acct
            , a.cust_da
            , d.na
            , a."1_LINE", a."2_LINE", a."3_LINE"
            , d.da, d.cd, d.title_na, d.ent_na
            , a.acct
            , a.acctlnk_enrl_cnt
       from   doc.document sample(10) d
              join doc.vndr v
                   on  v.vndr = d.acct
              join doc.cust_acct a
                   on  a.acct = v.acct
       and    d.img_da >= trunc(sysdate - 1)
       and    d.proc = 'A'
       and    a.acct <> '03'
     )
select * from v;