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

Mags,

I think the query can be done directly on the original BOOKS table without the use of any intermediate table.

The decode version of the same query would be something like:  SELECT

       l.librarynamedesc,
       SUM( DECODE(p.BookStatus,
                   1, 1,
                   4, DECODE( SIGN( SYSDATE - p.LoanDate - 14 ),
                              -1, 1,
                                  0
                      ),
                      0
                   )
           ) CountOfBookLoans,
       SUM( DECODE(p.BookStatus,
                   1, 0,
                   4, DECODE( SIGN( SYSDATE - p.LoanDate - 14 ),
                              -1, 0,
                                  1
                      ),
                      1
                   )
           ) CountOfLoanRenewalsAndOther
    FROM
       books p,
       libraryname l
    WHERE
        l.libraryname=p.libraryname
    GROUP BY
       l.librarynamedesc

As you can see, the logic is a bit more obscure than in the CASE version. I have replaced the inequality for p.LoanDate with a SIGN function which returns -1, 0, or 1 depending on whether the passed argument is <0, =0, or
>0.

If SYSDATE - p.LoanDate - 14 < 0, then SYSDATE - 14 < p.LoanDate .

This will work for Oracle 7 and later.

In general, I find that the RDBMS server does a far better job of processing rows than I can program so I let the RDBMS do all the hard work.

As for indices, their use should not affect the outcome of a query only the speed of getting that outcome. The exception being that their are integrity problems within your RDBMS caused by data within the index not corresponding to data in the base table.

I agree with the maxim that one should get a program (or query) working correctly before trying to optimize it. Let's get the right results first.

Douglas Hawthorne
"Mags" <mightymags_at_hotmail.com> wrote in message news:33b5419.0401261059.70575e17_at_posting.google.com...
> 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 - 15:34:22 CST