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

Mags,

My suggestion is, if you have Oracle 9.0.1 or later, would be to use something like the following query (assuming 1 means bookloan and 4 means expired for bookstatus):
SELECT

      l.librarynamedesc,
      SUM( CASE
              WHEN p.BookStatus = 1
                 THEN 1
              WHEN p.BookStatus = 4 AND
                   p.LoanDate   > SYSDATE - 14
                 THEN 1
              ELSE 0
            END
          ) CountOfBookLoans,
      SUM( CASE
              WHEN p.BookStatus = 1
                 THEN 0
              WHEN p.BookStatus = 4 AND
                   p.LoanDate   > SYSDATE - 14
                 THEN 0
              ELSE 1
            END
          ) CountOfLoanRenewalsAndOther
   FROM
      plsqltable p,
      libraryname l
   WHERE
       l.libraryname=p.libraryname
   GROUP BY
      l.librarynamedesc

I have dropped the bookauthor column because it is unlikely that you would have only one (1) author per library.

Douglas Hawthorne
"Mags" <mightymags_at_hotmail.com> wrote in message news:33b5419.0401251235.5a9305c1_at_posting.google.com...
> 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 - 16:55:48 CST