Tuesday, October 16, 2012


At my site there are few audit reports that display a given percentage of changes. For example, report to List 0.5 percentage of new invoices created or List 20 percent of supplier data changes such as vendor, site or payment method changes. I noticed that these reports were running a query to get total changed records and then running same query with one additional where clause like

AND rownum <= total_rows_returned_by_qury * input percentage

I thought there has to be some better way and i found analytic function LAST_VALUE.

Suppose you want to list 5 percentage of suppliers whose name like 'JOHN%', we can use LAST_VALUE function as given below

Oracle PL/SQL Programming By Feuerstein, Steven/ Pribyl, Bill (Google Affiliate Ad)

No comments:

Post a Comment