Re: Sample clause with multiple conditions workaround?

On Apr 23, 5:02 am, Carlos <miotromailcar..._at_netscape.net> wrote:
> On Apr 23, 5:44 am, Sub-Z Pulao <subzpu..._at_gmail.com> wrote:
>
>
>
>
>
> > I am trying to do the equivalent of this Teradata query in Oracle, and
> > I can't figure it out
>
> > select *
> > from tab
> > sample
> > when prod_code = 'AS' then 10
> > when prod_code = 'CM' then 10
> > when prod_code = 'DQ' then 10
> > end
>
> > I got this example fromhttp://forums.teradata.com/forum/database/sample-function
>
> > and was able to adapt it for a specific Teradata query. Now I am
> > trying to do the same thing in Oracle 10.2.x which has a SAMPLE clause
> > but not a WHEN subclause. The effect of the WHEN subclause in the
> > SAMPLE..END block is that it is like doing a "sample 10" for each
> > value of prod_code (so in the example above, it would return a total
> > of up to 30 records)
>
> > Most of what I'm finding on Google seems to predate Oracle having a
> > sample clause at all, and it looks like people worked around it using
> > a subquery and ROWNUM. I thought maybe I needed to adapt one of those
> > examples so that it supports a sampling for each condition met, but I
> > was not successful.
>
> You could try something like this:
>
> SELECT *
>   FROM ( SELECT * FROM TAB WHERE PROD_CODE='AS' AND ROWNUM < 11
>          UNION ALL
>          SELECT * FROM TAB WHERE PROD_CODE='CM' AND ROWNUM < 11
>          UNION ALL
>          SELECT * FROM TAB WHERE PROD_CODE='DQ' AND ROWNUM < 11 )
>
> You may use order by dbms_random to force random sampling.
>
> But: the performance will be far from optimal.
>
> Also, you can mark each subquery with a value ('1,2,3') to emulate
> Teradata's SAMPLEID functionality.
>
> HTH.
>
> Cheers.
>
> Carlos.- Hide quoted text -
>
> - Show quoted text -
Received on Fri Apr 23 2010 - 13:10:09 CDT