🚀 go-pugleaf

Hi Amita!
I think your problem is the order of the fields in your detail primary key.
It should be mast_key, Det_Key, ....etc.
If you get it. This should work fine for you:
Select *
  From master_table a
 Where 2000< (Select Sum(amount)
                        From detail_table b
                       Where b.mast_key =a.mast_key
                           And b.col_date between :fromdate and :todate)

Bye
FBrador

"amita" <amita@agnisoft.com> wrote in message
news:3de6f872@newsgroups.borland.com...
> I have a master and detail table
> as
> Master_Table ->  Mast_Key....
> Detail_Table  ->   Det_Key, Mast_key , Col_Date , Amount.....
>
> Now I have a query get the rows of the master
> where the Date lies between given dates and the total amount
> collected during that period exceeds say 2000
>
> so i have the query as
>
> select * from  Master_Table inner join Detail_Table
> on(Master_Table.Mast_Key=Detail_Table.Mast_Key)
> where Col_Date between :FromDate and :ToDate
> and Mast_Key in
>  (select Mast_key from Detail_Table
>  where Col_Date between :FromDate and :ToDate
>  group by Mast_Key having sum(Amount)>2000)
>
> Now the problem is that both Master_Table and
> Detail table have millions of records this
> query runs very slow
> I have indexes
> on Mast_Key, Det_key and Amount
>
> --
> Amita
> Agni Software Pvt Ltd
> Bangalore, India
>
>