Hi Trey

I'm running VFP6 SP4. The naming conventions in the following are, Dop_PK is in the DefinedOptions table (Product.ID). Doc_PK is DefinedOptionPrices. Doc_DopFK is the product id (Dop_PK) foreign key in the DefinedOptionPrices table.

Select DefinedOptions.*, DefinedOptionPrices.* ;
from DefinedOptions ;
inner join DefinedOptionPrices on transform(Dop_PK)+dtos(Datetime()) in ;
  (select transform(Doc_DopFK)+dtos(max(Doc_Effective)) ;
    from DefinedOptionPrices group by Doc_DopFK ) ;
into cursor test

Gives "SQL: Subquery nesting is too deep".

Select DefinedOptions.*, DefinedOptionPrices.* ;
from DefinedOptions ;
inner join DefinedOptionPrices on Dop_PK = Doc_DopFK ;
where Doc_PK in ;
  (select Doc_PK,max(Doc_Effective) from DefinedOptionPrices group by Doc_DopFK) ;
into cursor test

Gives "Subquery is invalid".

Select DefinedOptions.*, DefinedOptionPrices.* ;
from DefinedOptions, DefinedOptionPrices 
where ;
transform(Dop_PK)+dtos(DateTime()) in ;
  (select transform(Doc_DopFK)+dtos(max(Doc_Effective)) ;
    from DefinedOptionPrices group by Doc_DopFK)

Seems to accept it, but its taking forever.

I should have included the stipulation that the pricing table may or may not have a corresponding record for the product (left outer join). The point of all of this is to produce a single result set that shows the current price (where max(doc_effective) < (the current date/time)). So the dtos(Datetime()) will be extremely unlikely to ever find a match.

The reason I asked if VFP 7 could do it was that I already tried such things in 6 with results like I report above.

I'm currently doing it with 2 views. The first gives me the product id and max effective date and the second view joins with the first and the product and pricing tables to give the final result.

Thanks