Month: December 2005

Small SAMPLE bug in Oracle 9i (ORA-30561)

If you’re using the SAMPLE clause in Oracle 9i, in combination with a join, be aware of this small gotcha. I found a workaround, thankfully.

select * from (select * from dual), (select * from dual sample (10));

Expected result: should return zero or one row (more or less at random)

Actual result (tested with “ORA-30561: SAMPLE option not allowed in statement with multiple table references”

Workaround: put the query with the SAMPLE clause first, i.e.

select * from (select * from dual sample (10)), (select * from dual);

Note: It works fine in 10g (tested under since 10g does not restrict how many SAMPLE clauses are in a query.