I need to do the following something akin to the following SQL...

select * from SomeTable where [Some Constant Value] >= (<sub query>)

Looking at the FieldCompareSetPredicate in the docs and here in the forums I've only found examples of the following...

select * from SomeTable where SomeTable.SomeField >= (<sub query>)

If someone could offer an example of how to construct a query like the first one above that would be a huge help.

I don't think it's possible, but I think the query can be re-written so that it would be possible to formulate it with LLBLGen.

Would you please post the exact query you want to produce, maybe we can find another format of it?

Thanks

Walaa,

Thanks for your time...

I'm working against a deadline so I'm not able to explain my situation in any detail but I can share the approach I settled on in the unlikely event it proves useful to someone else.

Wanted to do...

[constant date value] >= (
SELECT MAX(SomeTable.DateField) 
FROM SomeTable 
WHERE SomeTable.Field = ParentTable.Field)

Ended up doing...

EXISTS
(
SELECT SomeTable.Field2
FROM SomeTable
WHERE SomeTable.Field = Parent.Field
GROUP BY SomeTable.Field2
HAVING [constant date value] >= MAX(SomeTable.DateField)
)

The specific code I use to implement the general EXISTS scenario above...

    private QueryDescriptor ConstructDateSetQuery(DateTime cutOffDate)
    {
        QueryDescriptor oQuery = new QueryDescriptor();

        //Fields
        oQuery.Fields = new EntityFields2(1);
        oQuery.Fields.DefineField(TrcrPaymentScheduleDatesFields.PaymentScheduleId, 0);

        //Filter
        oQuery.Filter.Add(TrcrPaymentScheduleDatesFields.PaymentScheduleId == TrcrPaymentScheduleInfoFields.PaymentScheduleId);

        //Group By
        oQuery.GroupBy.Add(TrcrPaymentScheduleDatesFields.PaymentScheduleId);

        //Having
        oQuery.GroupBy.HavingClause = new PredicateExpression(TrcrPaymentScheduleDatesFields.ExpectedPayDate.SetAggregateFunction(AggregateFunction.Max) <= cutOffDate);

        return oQuery;
    }

    QueryDescriptor oDateSetQuery = ConstructDateSetQuery(cutOffDate);
    oQuery.Filter.Add(
        new FieldCompareSetPredicate(
            null,
            null,
            oDateSetQuery.Fields[0],
            null,
            SetOperator.Exist,
            oDateSetQuery.Filter,
            oDateSetQuery.Relations,
            "",
            -1,
            null,
            false,
            oDateSetQuery.GroupBy
            )
        );

The FieldCompareSetPredicate supports the second alternative easily although GROUP BY feels less performant than the first method above.

By the way, I just HAVE to say that LLBL Gen Pro is absolutely brilliant. Saying that here is like preaching to the choir but I fealt something akin to a physical need to do it .

Thanks for the feedback, I would have tried to re-write it to use the FieldCompareSetPredicate too.

(As I've learned from Frans) It can be done in v2, with a field compare expression where you formulate something like: (subquery) < value and (subquery) is then formulated with a ScalarQueryExpression on the field passed to the fieldCompareExpression predicate and the value is the value to compare with.