General discussion

Locked

Random Sampling in PL/SQL

By mikesmith ·
I have written some SQL code (below) to get a 5% sample of claim items for a specified location. It uses the OVER keyword and works in TOAD.

When I try to use this code in PL/SQL it does not recognise the OVER keyword. I have rewritten it to get the result I want procedurally, but would like to be able to do it in one step rather than using a cursor.

Is there some way I can do it in PL/SQL?

SQL Code :
select loc, itm, cln, dos
FROM (
select ci.location_id loc, ci.item_num itm,ci.client_num cln, ci.date_of_service dos, ROW_NUMBER()
OVER (ORDER BY dbms_random.value) as tr
from ohs.claim_item ci, ohs.claim cl
where ci.client_num = cl.client_num AND
ci.HIC_CLAIM_NUM = cl.HIC_CLAIM_NUM AND
cl.source_of_claim = 'E' AND
cl.hic_processing_date between '01-jul-2000' and '30-sep-2000'AND
ci.location_id = ('108074') AND
ci.ITEM_NUM = 710 AND
(cl.contractor_code <> '0128' OR
(cl.contractor_code = '0128' ANDEXISTS (SELECT 'x'
FROM ohs.client c
WHERE c.client_num = ci.client_num AND
(Upper(Substr(c.eligibility, 1, 1)) <> 'V')))))
WHERE tr < 24.85

This conversation is currently closed to new comments.

3 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

Random Sampling in PL/SQL

by peace monger In reply to Random Sampling in PL/SQL

What should the OVER operation do? Is it a kind of JOIN like a cartesian product?

There may be some new functions in Oracle8i that can do what your asking... REPORT_RATIO I think is one of them.

Collapse -

Random Sampling in PL/SQL

by mikesmith In reply to Random Sampling in PL/SQL

I am not 100% sure what it does. It seems to allow the inner query to order by dbms_random.value() and then rename it as tr to allow the final where satement (tr<24.85) to be done.

I am not aware of the REPORT_RATIO function. Where can I get some doco on it?

Collapse -

Random Sampling in PL/SQL

by mikesmith In reply to Random Sampling in PL/SQL

I am not 100% sure what it does. It seems to allow the inner query to order by dbms_random.value() and then rename it as tr to allow the final where statement (tr<24.85) to be done.

I am not aware of the REPORT_RATIO function. Where can I get some doco on it?

Back to Web Development Forum
3 total posts (Page 1 of 1)  

Related Discussions

Related Forums