Prev: dummy value?
Next: Apex sample
From: Carlos on 23 Apr 2010 05:02
On Apr 23, 5:44 am, Sub-Z Pulao <subzpu...(a)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.
From: Sub-Z Pulao on 23 Apr 2010 14:10
Thanks, Carlos, that looks very useful and I will try it.

On Apr 23, 5:02 am, Carlos <miotromailcar...(a)netscape.net> wrote:
> On Apr 23, 5:44 am, Sub-Z Pulao <subzpu...(a)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 -

 | 
Pages: 1
Prev: dummy value?
Next: Apex sample