🚀 go-pugleaf

Try writing a  Stored Procedure 'FINDFIRST' which takes input parameters
Date . The stored proc should return the list of min Receive_id (as is done
in the subquery)
after iterating using FOR Select  SUSPEND....
Then use an inner join  i.e
Select P.Name R.* from Receive R inner join Customer P on
( R.Customer_ID = P.Customer_ID ) INNER JOIN FINDFIRST ON(R.Receive_ID
=FINDFIRST.RECEIVEID )
Amita
http://www.agnisoft.com


"Marcos Pegoraro" <mpegoraro@brturbo.com> wrote in message
news:3e03bb4d@newsgroups.borland.com...
> I have one table which has 90.000 records and it has all accounts to
receive
> Each record has a Contract_ID and the same Contract can have up to 20
> Accounts to receive
> But, I want to show only the first record of each Contract which was not
> paid yet or if it was paid between two dates.
>
> Select P.Name R.* from Receive R inner join Customer P on
> R.Customer_ID = P.Customer_ID where R.Receive_ID = (select min(Receive_ID)
> from Receive
> where (PayDate is Null or PayDate between '12/01/02' and '12/31/02') and
> Contract_ID=R.Contract_ID group by Contract_ID ) and R.DueDate between
> '12/01/02' and '12/31/02'
>
> Example:
> ID Contract_ID DueDate PayDate
> 1    1                 09/01/02 09/01/02
> 2    1                 10/01/02
> 3    1                 11/01/02
> 4    2                 10/01/02 10/01/02
> 5    2                 11/01/02
> 6    3                 10/01/02
> 7    3                 11/01/02
> 8    4                 12/01/02 12/01/02
> 9    4                 01/01/03
> ID is Primary Key
> Contract_ID is Foreign Key
> DueDate is indexed
> PayDate is indexed too.
>
> The result I want and it results is:
> 2    1                 10/01/02
> 5    2                 11/01/02
> 6    3                 10/01/02
> 8    4                 12/01/02 12/01/02
>
> So, the result is OK but my problem is the speed.
> It takes up to 11 seconds to return using IBConsole.
> and, if I use:
> select * from Receive where DueDate between '12/01/02' and '12/31/02'
> It takes just 0.006 Seconds to return
>
> Then, What do need I do to run that first query faster, like this last one
?
>
>