Sample clause with multiple conditions workaround?

From: Sub-Z Pulao <subzpulao_at_gmail.com>
Date: Thu, 22 Apr 2010 20:44:12 -0700 (PDT)
Message-ID: <0c1cbdcc-80cd-43f3-9b23-5aa688797c5d_at_q15g2000yqj.googlegroups.com>

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. Received on Thu Apr 22 2010 - 22:44:12 CDT