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 from
http://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.

On Apr 23, 5:44=A0am, Sub-Z Pulao <subzpu...@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 =3D 'AS' then 10
> when prod_code =3D 'CM' then 10
> when prod_code =3D 'DQ' then 10
> end
>
> I got this example fromhttp://forums.teradata.com/forum/database/sample-f=
unction
>
> 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=3D'AS' AND ROWNUM < 11
         UNION ALL
         SELECT * FROM TAB WHERE PROD_CODE=3D'CM' AND ROWNUM < 11
         UNION ALL
         SELECT * FROM TAB WHERE PROD_CODE=3D'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.

Thanks, Carlos, that looks very useful and I will try it.

On Apr 23, 5:02=A0am, Carlos <miotromailcar...@netscape.net> wrote:
> On Apr 23, 5:44=A0am, Sub-Z Pulao <subzpu...@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 =3D 'AS' then 10
> > when prod_code =3D 'CM' then 10
> > when prod_code =3D '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 *
> =A0 FROM ( SELECT * FROM TAB WHERE PROD_CODE=3D'AS' AND ROWNUM < 11
> =A0 =A0 =A0 =A0 =A0UNION ALL
> =A0 =A0 =A0 =A0 =A0SELECT * FROM TAB WHERE PROD_CODE=3D'CM' AND ROWNUM < =
11
> =A0 =A0 =A0 =A0 =A0UNION ALL
> =A0 =A0 =A0 =A0 =A0SELECT * FROM TAB WHERE PROD_CODE=3D'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 -