Possible to use Decode or Count function on PL SQL Table?

Hi,
Can someone tell me if it's possible to perform the Decode or Count functions on a PL SQL Table where the Table is a table of records and I want to get a count of the number of records that have a certain value in a particular column of the record? I have an Oracle table (call it Books) with +200000 records on it. I need to be able to pull back a report showing a count of X and Y grouped by A.
Let's say X is CountOFBookLoans, Y is CountOfLoanRenewalsAndOther, A is LibraryName.
Let's say there is a column on my Books table which holds a BookStatus
(Number) which would be the code for BookLoan, LoanRenewal, Expired,
OnShelf or OnHold. My problem is that the report needs to split the count of Expiry records into Loans and LoanRenewals (using date logic).
I therefore can't do a simple Decode function or Count function based on BookStatus column on the Books table because the BookStatus code of Expired needs to be split into BookLoans and LoanRenewals counts and I would end up double-counting some records. I was thinking of using a PL/SQL Table of records. I would do a select on my Books table and bring back roughly 100000 records based on report criteria and then I would process each row and use conditions to see if my BookExpired records are in fact Loans or LoanRenewals. I would then write the "new" status to the PL SQL Table along with the BookAuthor and LibraryName. For any records which had a BookStatus other than Expired, it would be a direct copy of existing BookStatus BookAuthor and LibraryName to the PL SQL Table. I would then like to do a SELCT statement on the PL SQL table using the DECODE function like this:

SELECT

	LIBRARYNAME.LIBRARYNAMEDESC, 
	PLSQLTABLE.BOOKAUTHOR, 
	SUM(DECODE(PLSQLTABLE.BOOKSTATUS,1,1,0)) + 
	SUM(DECODE(PLSQLTABLE.BOOKSTATUS,2,1,0)) +
	SUM(DECODE(PLSQLTABLE.BOOKSTATUS,3,1,0)) +
	SUM(DECODE(PLSQLTABLE.BOOKSTATUS,4,1,0)) BookRenewalAndOther,
	SUM(DECODE(PLSQLTABLE.BOOKSTATUS,5,0)) BookLoan
	FROM 
	PLSQLTABLE,
	LIBRARYNAME
	WHERE 
	LIBRARYNAME.LIBRARYNAME =PLSQLTABLE.LIBRARYNAME 
	GROUP BY LIBRARYNAME.LIBRARYNAME_DESC;

(Book Status of 1,2,3 and 4 need to get counted as
BookRenewalAndOther. BookStatus of 5 needs to get counted as just BookLoan. I don't have to worry
about BookExpiry status as I've processed all of these and set the status to either BookLoan or BookRenewal (using date logic) on the PL SQL Table for these records.)

As my PLSQL Table is a table of records, indexed by BinaryInteger, I'm not sure that this approach would work?? I could also use a normal Oracle table and just delete the records before the report is run but I'm not sure how efficient that would be. Can anyone help me?

Thanks
M Received on Sun Jan 25 2004 - 14:35:39 CST