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