Aggregate in Oracle 8

[csphillips6]

I'm a new position with a company that uses Oracle 8 after work on on SQL Server 2000 for years.

I'm working on a report where I have to sum a table field (charge) for each row and the sum will be an aggregate based on a billing code in another table. I would use a sub-query expression for each billing category in SQL Server, but I learned this isn't supported in O 8. I've also read a lot about the OVER function which I haven't seen explicitly not supported in ver 8, but I'm having no luck with the syntax there apparently.

If anyone has had to deal with the same situation, I would appreciate any direction I should take to resolve this.

Thanks in advance for any help.

 

[LKBrwnDBA]

You need to post an example of source data/tables and the result you want.

Also the SQL you coded.



----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[csphillips6]

SELECT
blcat.rpdesc,
blbhst.bankno,
blbhst.bkname,
sum(blhst.charge)
FROM
blcat,
bldhst,
blhst,
blbhst
WHERE
blcat.bilcat = bldhst.bilcat and
bldhst.appl = blhst.appl and
bldhst.subcd = blhst.subcd and
blhst.bankno = blbhst.bankno and
blhst.seq = bldhst.seq
GROUP BY
blbhst.bankno,
blbhst.bkname,
blcat.rpdesc;


4 tables

blbhst, bldhst, blhst, blcat

relations

blbhst -> bankno <- blhst
blhst -> seq <- bldhst
blhst -> appl <- bldhst
blhst -> subcd <- bldhst
bldhst -> bilcat <- blcat

ABOVE SQL RETURNS:


RPDESC BANKNO BKNAME SUM(BLHST.CHARGE)
--------------- ---------- ---------------------------------------- -----------------
BACKROOM 3 FIRST NATIONAL BANK 0
IP/IMG 3 FIRST NATIONAL BANK 21562.4
PROCESSING 3 FIRST NATIONAL BANK 224388
BACKROOM 5 FIRST STATE BANK 0
IP/IMG 5 FIRST STATE BANK 965.2
PROCESSING 5 FIRST STATE BANK 35638
IP/IMG 5 FIRST STATE BANK 965.2 I


WOULD LIKE:

BANKNO BKNAME BACKROOM IP/IMG PROCESSING .. .. .. TOTAL
------ ---------------------- -------- ------- ---------- -- -- -- ----------
3 FIRST NATL BANK 0 21562.4 224388 .. .. .. ########
5 FIRST STATE BANK 0 965.2 35638 .. .. .. ########

The number of RPDESC/bilcat records is dynamic.

 

[LKBrwnDBA]

The result you require looks more like a "pivot" table, tis type of question has been asked and answered many times, just search for "pivot table" or "crosstab query", "rows to columns" or ask Tom:

http://asktom.oracle.com/pls/ask/f?...P8_DISPLAYID,F4950_P8_CRITERIA:7086279412131,

http://asktom.oracle.com/pls/ask/f?..._P8_DISPLAYID,F4950_P8_CRITERIA:419593546543,

http://asktom.oracle.com/pls/ask/f?..._P8_DISPLAYID,F4950_P8_CRITERIA:766825833740,

http://asktom.oracle.com/pls/ask/f?..._P8_DISPLAYID,F4950_P8_CRITERIA:925229353765,

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb