Re: SQL question

jalford_at_immcms1.redstone.army.mil (huckleberry) wrote:
>Why does this query not give the expected results as when I break it into tow
>seperate queries ??
>
>Combined query:
>
> select count(l1.class) NUMBER_LOANS,
> count(l2.class) NUMBER_DELINQ
>from loan l1, loan l2
>where l1.loan_status='OPEN' and
> l2.loan_status='OPEN' and
> months_between(to_date(l2.EXPIRATION_DATE,'MON-YY'),sysdate) <= -2
>
> results:
>
>NUMBER_LOANS NUMBER_DELINQ
>---------------------------- -----------------------------
> 4982 4982
>
>-----------------------------------------------------------------
>individual queries:
>
> select count(class) NUMBER_LOANS
> from loan
> where loan_status='OPEN'
>
> results: NUMBER_LOANS = 106
>
> and
>
> select count(class) NUMBER_DELINQ
> from loan
> where loan_status='OPEN' and
> months_between(to_date(EXPIRATION_DATE,'MON-YY'),sysdate) <= -2
>
> results: NUMBER_DELINQ = 47
>
>For some reason unknown to me, the numbers the combined query returns is the
>result of the two numbers multiplied together. So, how do i get the right
>answer from one query or can I do it with one query ??
>
>Thanks in Advance !!
>
> - huckleberry

The unknown reason is a cartesian product. SQL is really just relational algebra. Multiply two sets and you get one set that is the product of the two. For example given a table A with 2 rows and a table B with 3, "select * from a, b" will return 6 rows. Obviously in your query, Loan_status is *NOT* the primary key of both tables (so 11 and 12 return more then 1 row) and hence, all of the rows from 11 will be joined with each of the rows from 12-- before the counting begins.....

In short, to get the right answer you would query:

• Assume &A =
• monnths_between(to_date(EXPIRATION_DATE,'MON-YY'),sysdate)

select count(*) number_loans,

       sum( decode( sign( &A + 2 ), -1, 1, 0, 1, 0 ) ) NUMBER_DELINQ from loan
where loan_status = 'OPEN'
/

What the decode does is:
if ( &A + 2 < 0 ) then -- &A < -2

   return 1
else
if ( &A + 2 = 0 ) then -- &A = -2

   return 1

else                   -- &A > -2

   return 0
end

so the sum aggregate will get 1 if the exp. date is more then 2 months before today, it will get zero otherwise. summing up all of the loans for loan_status = 'OPEN' with the count(*) {not with count(class), what if class is NULL and LOAN_STATUS is not, wouldn't you want to count that row?} along with that sum will show you:

count of the loans with a status of OPEN, alongside the count of deliquent loans with status of open.

Hope this helps and hope the math is all right (hate those date functions)....

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government Received on Sat Jun 17 1995 - 00:00:00 CEST