Re: Sample clause with multiple conditions workaround?

From: Carlos <miotromailcarlos_at_netscape.net>
Date: Fri, 23 Apr 2010 02:02:26 -0700 (PDT)
Message-ID: <16ffaa37-6d67-492f-b824-ecd2517ea131_at_k36g2000yqn.googlegroups.com>

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. Received on Fri Apr 23 2010 - 04:02:26 CDT