Hi all

i try to write this predicate but it fail is there any way to write it

LLBL 2.5 and using adapter

select FundName
from dbo.FNDFund fund
where (
    select  ((nav.FundNAVValue- f.FundUnitPrice)/nav.FundNAVValue)*100
    from dbo.FNDFund f inner join dbo.FNDFundNAV nav
        on f.FundID =nav.FundID
    where (nav.FundSubscriptionNAVDate=(select max(FundSubscriptionNAVDate) from dbo.FNDFundNAV )and fund.fundID=f.FundID)
        ) between 10 and 20
                                            

and this equal

select FundName
from dbo.FNDFund fund 
        join (select nav.FundID,((nav.FundNAVValue- f.FundUnitPrice)/nav.FundNAVValue)*100 as SinceInception
                from dbo.FNDFund f inner join dbo.FNDFundNAV nav
                    on f.FundID =nav.FundID
                where nav.FundSubscriptionNAVDate=(select max(FundSubscriptionNAVDate) from dbo.FNDFundNAV )) CurrentNAV
        on CurrentNAV.FundID=fund.FundID
where CurrentNAV.SinceInception between 10 and 20

Hi mohamed, Could you confirm that this query is equivalent?:

SELECT FundName
FROM dbo.FNDFund fund
    INNER JOIN dbo.FNDFundNAV nav ON f.FundID = nav.FundID
WHERE 
    ( ( (nav.FundNAVValue - f.FundUnitPrice) / nav.FundNAVValue ) * 100 ) BETWEEN 10 AND 20
    AND nav.FundSubscriptionNAVDate = ( SELECT MAX(FundSubscriptionNAVDate) FROM dbo.FNDFundNAV )

which IMHO is more simple. Here is an approximate C#-Apdater code for that, using dynamic list:

/// the field part...
/// SELECT FundName
EntityFields2 fields = new EntityFields2(1);
fields.DefineField(FNDFundFields.FundName, 0);

/// the reladion part...
/// INNER JOIN dbo.FNDFundNAV nav ON f.FundID = nav.FundID
IRelationPredicateBucket filter = new RelationPredicateBucket();
filter.Relations.Add(FNDFundEntity.Relations.FNDFundNAVEntityUsingFundId);

/// fisrt filter...
/// ( ( (nav.FundNAVValue - f.FundUnitPrice) / nav.FundNAVValue ) * 100 ) BETWEEN 10 AND 20
IEntityField2 calculatedField = FNDFundNAVFields.FundNAVValue;
calculatedField.SetExpression((FNDFundNAVFields.FundNAVValue - FNDFund.FundUnitPrice) / FNDFundNAVValue * 100);
FieldBetweenPredicate betweenFilter = new FieldBetweenPredicate(calculatedField, null, 10, 20);
filter.PredicateExpression.Add(betweenFilter);

/// second filter...
/// AND nav.FundSubscriptionNAVDate = ( SELECT MAX(FundSubscriptionNAVDate) FROM dbo.FNDFundNAV )
FieldCompareSetPredicate dateFilter = new FieldCompareSetPredicate(
    FNDFundNAVFields.FundSubscriptionNAVDate, null,
    FNDFundNAVFields.FundSubscriptionNAVDate.SetAggregateFunction(AggregateFunction.Max), null,
    SetOperator.Equal, null);
filter.PredicateExpression.Add(dateFilter);


// fetch results...
DataTable results = new DataTable();
using (DataAccessAdapter adapter = new DataAccessAdapter())
{
    adapter.FetchTypedList(fields, results, filter);
}

If that query is not what you want, I think your second query is easier than the first one. Let us know