Re: Subquery help with Max !

From: Guido Konsolke <NoSpam_at_MyAccount.com>
Date: Wed, 27 Feb 2002 13:42:34 +0100
Message-ID: <1014812551.976586@news.thyssen.com>

Hi April,
I did some work on your question to Dieter. I used this version:
Oracle7 Server Release 7.3.4.3.0 - Production With the distributed and parallel query options PL/SQL Release 2.3.4.3.0 - Production

I have an solution for you, but:
it is not nice, probably not fast.
Anyway, it works this way.

I created a view as follows:
CREATE VIEW view1 as
select a.paymentyear, a.paymentmonth, tblPaymentItems.bidno, a.paymentkey paykey from tblProject, tblPaymentItems, tblpaymentschedule a where tblproject.bidno = tblPaymentItems.bidno and tblPaymentItems.paymentkey = a.paymentkey and a.paymentyear = (select min(paymentyear)

                        from   tblpaymentschedule b, tblPaymentItems
          where  tblPaymentItems.bidno = tblproject.bidno
                        and    tblPaymentItems.paymentkey= b.paymentkey
                       )
and   a.paymentmonth=(select min(paymentmonth)
                      from   tblpaymentschedule c
        where  tblPaymentItems.bidno = tblproject.bidno
                      and    tblPaymentItems.paymentkey= c.paymentkey
                      and    c.paymentyear = (select min(paymentyear)
                         from   tblpaymentschedule b, tblPaymentItems
           where  tblPaymentItems.bidno = tblproject.bidno
                         and    tblPaymentItems.paymentkey= b.paymentkey
                                             )
                      );

After that, I issued this Select-Statement: SELECT a.paymentyear,a.paymentmonth
FROM tblpaymentschedule a,

        view1
WHERE view1.Bidno = 32144
AND view1.PAYKEY = a.PAYMENTKEY

AND     a.paymentyear=(select min(paymentyear)
                       from   view1
                       where  bidno = 32144


        )

AND     a.paymentmonth=(select min(paymentmonth)
                        from   view1
                        where  bidno = 32144
                        and    paymentyear=(select min(paymentyear)
                       from   view1
                       where  bidno = 32144


        )

   )
;

This came back as the result:

PAYMENTYEAR PAYMENTMONTH
----------- ------------

       2000 1

 real: 220
13:23:24 DOAIAW1> Maybe you won't need the view, but it would look even more horrible whithout.

Greetings from Germany,
Guido

"April" <privateBenjamin_at_hushmail.com> schrieb im Newsbeitrag news:6effef59.0202260720.6119daec_at_posting.google.com...

> Thanks very much Guido, that worked perfectly!
> 
> April
> 

Received on Wed Feb 27 2002 - 06:42:34 CST