SERPland Flugverkehr/Airtraffic Zürich – Auto Direktimport Dänemark – Disneyland Paris – Legoland Deutschland

15Jul/110

Oracle Sum Function

The Oracle Sum fuction is very useful. Also analytical Oracle Sum functions are available. Using sum(<column>) over (partition by <column>): with this oracle sum function, you can sum over a range within the result set.

Sample: I have 526 Rows of payed bills, unfortunatelly within this view I get bills with their position in the same view (not normalized). With this oracle sum function, I calculate a sum of all positions within its bill.

select persnr ,billnr ,positionnr ,betrag ,currency ,sum(betrag) over (partition by persnr, rechnungnr order by persnr, to_number(billnr),to_number(positionnr)) as oracle_sum   -- oracle sum function  from billing_view where 1=1 and persnr = 37797

Related Posts:

10Jan/080

Error / Problem: select statement returns no rows – but result set contains about 3 rows (from CW_ALL_DB_LINKS / sys.link$ / Oracle9i)

CAST Application Mining Error/Problem:

Following select statement returns no rows - but result set contains about 3 rows

Thu May 27 11:19:18 2004: Query sent:
Thu May 27 11:19:18 2004: SELECT 1020, db_link, 'V', -1, host, UserName
Thu May 27 11:19:18 2004: FROM CW_ALL_DB_LINKS
Thu May 27 11:19:18 2004: WHERE ( owner = 'PUBLIC' )
Thu May 27 11:19:18 2004: --

It seems as on 9i the privilege SELECT ANY TABLE excludes the "sys.link$" table! Now we try with direct grants on these objects.

SELECT *
FROM sys.link$

Solution:
Permission missing: GRANT SELECT ON SYS.LINK$ TO CAST

Related Posts: