Thanks for your reply. I'm actually using Oracle 8i so I haven't come
across the CASE statement you've used below.
I was changing the name and type of my data (the problem is nothing to
do with libraries and books) so BookAuthor probably wasn't a very good
example to give as a column name. Maybe BookClassification would have
been better.
I've given up on using a PL SQL table and am just using a new "normal"
Oracle table which is dedicated to this report. I use a cursor to
select back my 100000+ records from my original Books Table which I
want to process then loop through and find those that have a status of
BookExpired. I use "If Then Else" logic to determine whether the
status should actually be BookLoan or BookRenewal for that record and
then write the "new status" to the special report table. For any
records that aren't expired, I simply copy their existing BookStatus,
LibraryName (code) to this special report table.
I then do my SUM(DECODE) across the status on the special report table
and use Group By to group the results.
My problem now is that the results are coming out "haywire".
Sum(Decode)....
seems to work fine on the original table but on the special report
table it's produces weird results.
Any reason why? Indexes wouldn't make a difference?
"Douglas Hawthorne" <DouglasHawthorne_at_yahoo.com.au> wrote in message news:<UPXQb.27872$Wa.14682_at_news-server.bigpond.net.au>...
> 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 Mon Jan 26 2004 - 12:59:13 CST
