Oracle Analysis Function

Oracle Analysis Function

FUNCTION_NAME(<argument>,<argument>...) OVER(<Partition-Clause><Order-by-Clause><Windowing Clause>)

SELECT t.transaction_date,

       t.item_id,

       t.quantity,

       SUM(decode(t.transaction_type, 'RETRUN TO RECEIVING', -1 * t.quantity, t.quantity)) over(PARTITION BY t.item_id ORDER BY trunc(t.transaction_date)) Accumulative1,

       SUM(decode(t.transaction_type, 'RETRUN TO RECEIVING', -1 * t.quantity, t.quantity)) over(PARTITION BY t.item_id ORDER BY trunc(t.transaction_date) range between unbounded preceding and current row) Accumulative2,

       rank() over(ORDER BY trunc(t.transaction_date)) rank1,

       dense_rank() over(ORDER BY trunc(t.transaction_date)) rank2,

       row_number() over(ORDER BY trunc(t.transaction_date)) rank3

  FROM (SELECT a.quantity,

               pla.item_id,

               a.transaction_date,

               pla.po_line_id,

               a.organization_id,

               a.transaction_type,

               a.transaction_id

          FROM po.rcv_transactions a, po_lines_all pla

         WHERE a.po_line_id = pla.po_line_id(+)

           AND pla.item_ID = 210643

           AND a.organization_id = 216

           and a.destination_type_code = 'INVENTORY'

           AND a.SOURCE_DOCUMENT_CODE = 'PO') t