Somebody know how I can make this query in LLBLGEN (I think it's not possible but ...)

select 
    et_id
from 
    employee_tl etl
    join 
        (
            select 
                e_id, 
                currvalidfrom = max(et_valid_from)
            from 
                employee_tl
            where 
                et_valid_from < @AtDate
            group by
                e_id 
        ) etlcurr on etl.e_id = etlcurr.e_id and etl.et_valid_from = etlcurr.currvalidfrom

In an employee_Tl table I've a field (et_valid_from) which specify when the record is valid. the record is valid until another record have an et_valid_from later. Simple exemple

e_id,   et_id,  et_valid_from
0,      0,      12h00
0,      1,      13h00
1,      2,      12h00
1,      3,      14h00

Of course its not hours but full datetime so to be simple I only put hours Some query. if I asks : - 12h, I'll have et_id 0 and 2 - 13h, I'll have et_id 1 and 2 - 14h, I'll have et_id 1 and 3

The query I found is the quoted one above. I don't find a way to fetch with a query accepted by llblgen.

Thanks for any help

isn't that something like:

SELECT et_id FROM employee_tl E WHERE et_id = ( SELECT TOP 1 et_id FROM employee_tl WHERE employee_tl.e_id = E.e_id ORDER BY et_valid_from DESC )

?

which can be done with a FieldCompareSetPredicate instance.

Humm I was searching something too much complicate Yes your query work, except that you forgot a restriction on the date ("AND et_valid_from < GetDate()")

SELECT E.et_id, E.e_id 
FROM employee_tl E
WHERE E.et_id = (
    SELECT TOP 1 et_id FROM employee_tl 
    WHERE employee_tl.e_id = E.e_id
    AND et_valid_from < GetDate()
    ORDER BY et_valid_from DESC
)

Because I can give another date than today, and I can have timeline record in the future (so it's not always the first one)

Thanks a lot for your help

Ah yes, I didn't quite know what to do with the date . Glad to see it solved

almost solved I've the query, but without alias so it don't work lol

SELECT ETl.et_id, ETl.e_id FROM employee_tl ETl WHERE ETl.et_id = ( SELECT TOP 1 et_id FROM employee_tl WHERE employee_tl.e_id = ETl.e_id AND et_valid_from < GetDate() ORDER BY et_valid_from DESC )

I found how to put aliases in the subselect ("WHERE employee_tl.e_id = ETl.e_id") But how to put it in the main select ? Here is my actual code :

            FieldCompareRangePredicate      compareRangePredicate   = new FieldCompareRangePredicate(EntityFieldFactory.Create(EmployeeTlFieldIndex.EId), null, list);

            FieldCompareExpressionPredicate compareExpPredicate     = PredicateFactory.CompareExpression(
                EmployeeTlFieldIndex.EId, 
                ComparisonOperator.Equal, 
                new Expression(EntityFieldFactory.Create(EmployeeTlFieldIndex.EId)),
                "ETl"
            );
            
            PredicateExpression             subSelectPredicate      = new PredicateExpression();
            subSelectPredicate.Add(compareExpPredicate);
            subSelectPredicate.AddWithAnd(PredicateFactory.CompareValue(EmployeeTlFieldIndex.EtValidFrom, ComparisonOperator.LessEqual, dt));

            FieldCompareSetPredicate        compareSetPredicate     = new FieldCompareSetPredicate(
                EntityFieldFactory.Create(EmployeeTlFieldIndex.EtId), null,
                EntityFieldFactory.Create(EmployeeTlFieldIndex.EtId), null,
                SetOperator.Equal,
                subSelectPredicate,
                null, 
                string.Empty, 
                1, 
                new SortExpression(SortClauseFactory.Create(EmployeeTlFieldIndex.EtValidFrom, SortOperator.Descending))
                );

            IRelationPredicateBucket predicateBucket    = new RelationPredicateBucket();
            predicateBucket.PredicateExpression.Add(compareRangePredicate);
            predicateBucket.PredicateExpression.AddWithAnd(compareSetPredicate);

ps : actually my query looks like (I've simplify)

SELECT et_id, e_id FROM employee_tl WHERE et_id = ( SELECT TOP 1 et_id FROM employee_tl WHERE ETl.e_id = employee_tl.e_id AND et_valid_from < '...mydate...' ORDER BY et_valid_from DESC )

The 2 column data is read into a datatable using a dyn. list, right? Or are you fetching entities?

if you're fetching a list, do this:

ResultsetFields fields = new ResultSetFields(2);
fields.DefineField(EmployeeTlFieldIndex.EtId, 0, "et_id", "ETl");
fields.DefineField(EmployeeTlFieldIndex.EID, 1, "e_id", "ETl");

FieldCompareRangePredicate compareRangePredicate = new 
    FieldCompareRangePredicate(EntityFieldFactory.Create(EmployeeTlFieldIndex.EId), null, list);
    
FieldCompareExpressionPredicate compareExpPredicate = PredicateFactory.CompareExpression(
    EmployeeTlFieldIndex.EId, 
    ComparisonOperator.Equal, 
    new Expression(EntityFieldFactory.Create(EmployeeTlFieldIndex.EId)),
    "ETl"
);

PredicateExpression subSelectPredicate = new PredicateExpression();
subSelectPredicate.Add(compareExpPredicate);
subSelectPredicate.AddWithAnd(PredicateFactory.CompareValue(
    EmployeeTlFieldIndex.EtValidFrom, ComparisonOperator.LessEqual, dt));

FieldCompareSetPredicate compareSetPredicate = new FieldCompareSetPredicate(
    fields[0], null,
    EntityFieldFactory.Create(EmployeeTlFieldIndex.EtId), null,
    SetOperator.Equal,
    subSelectPredicate,
    null, 
    "ETl", 
    1, 
    new SortExpression(SortClauseFactory.Create(EmployeeTlFieldIndex.EtValidFrom, SortOperator.Descending))
    );

IRelationPredicateBucket predicateBucket = new RelationPredicateBucket();
predicateBucket.PredicateExpression.Add(compareRangePredicate);
predicateBucket.PredicateExpression.AddWithAnd(compareSetPredicate);

DataTable tlist = new DataTable();
adapter.FetchTypedList(fields, tlist, predicateBucket);

(haven't tested it, this is the idea) If you're fetching entities, you can alias the subquery field, by setting that field's objectAlias to ETl. Which version is aliased is not important.

RelationsetFields ? You mean ResultsetFields I suppose

Yes I'm fetching entity and not dynamic list so ... ResultsetFields don't work for me. Is it a way to put an alias to all fields of a entity query ?

edit : I've understand, I'm trying to do it

Yes I mean resultsetfields ... I don't know why, but I always make that mistake...

I got this query :

SELECT ...
FROM [HRANEW_NUNITTEST].[dbo].[EMPLOYEE_TL] 
WHERE 
( 
    [HRANEW_NUNITTEST].[dbo].[EMPLOYEE_TL].[E_ID]IN (...) And 
    [HRANEW_NUNITTEST].[dbo].[EMPLOYEE_TL].[ET_ID] = 
    (
        SELECT TOP 1 ETl.[ET_ID] AS [EtId] 
        FROM [HRANEW_NUNITTEST].[dbo].[EMPLOYEE_TL] 
        WHERE 
        (   
            ETl.[E_ID] = [HRANEW_NUNITTEST].[dbo].[EMPLOYEE_TL].[E_ID] And 
            ETl.[ET_VALID_FROM] <= @EtValidFrom11
        )
        ORDER BY ETl.[ET_VALID_FROM] DESC
    )
)

And I need :

SELECT ...
FROM [HRANEW_NUNITTEST].[dbo].[EMPLOYEE_TL] 
WHERE 
( 
    [HRANEW_NUNITTEST].[dbo].[EMPLOYEE_TL].[E_ID]IN (...) And 
    [HRANEW_NUNITTEST].[dbo].[EMPLOYEE_TL].[ET_ID] = 
    (
        SELECT TOP 1 ETl.[ET_ID] AS [EtId] 
        FROM [HRANEW_NUNITTEST].[dbo].[EMPLOYEE_TL] ETl  
        WHERE 
        (   
            ETl.[E_ID] = [HRANEW_NUNITTEST].[dbo].[EMPLOYEE_TL].[E_ID] And 
            ETl.[ET_VALID_FROM] <= @EtValidFrom11
        )
        ORDER BY ETl.[ET_VALID_FROM] DESC
    )
)

The difference is in the subselect : FROM [HRANEW_NUNITTEST].[dbo].[EMPLOYEE_TL] ETl I've to set an alias for the table in the FROM clause but I don't find how I've to do ... In the help there is an example but for a relation between 2 tables ... Any help? I'm a bit stuck now ... My code :

            ResultsetFields fields = new ResultsetFields(1);
            fields.DefineField(EmployeeTlFieldIndex.EtId, 0, EmployeeTlFieldIndex.EtId.ToString(), "ETl");

            FieldCompareRangePredicate      compareRangePredicate   = new FieldCompareRangePredicate(EntityFieldFactory.Create(EmployeeTlFieldIndex.EId), null, list);

            FieldCompareExpressionPredicate compareExpPredicate     = PredicateFactory.CompareExpression(
                EmployeeTlFieldIndex.EId, 
                ComparisonOperator.Equal, 
                new Expression(EntityFieldFactory.Create(EmployeeTlFieldIndex.EId)),
                "ETl"
            );
            
            PredicateExpression             subSelectPredicate      = new PredicateExpression();
            subSelectPredicate.Add(compareExpPredicate);
            subSelectPredicate.AddWithAnd(PredicateFactory.CompareValue(EmployeeTlFieldIndex.EtValidFrom, ComparisonOperator.LessEqual, dt, "ETl"));

            FieldCompareSetPredicate        compareSetPredicate     = new FieldCompareSetPredicate(
                EntityFieldFactory.Create(EmployeeTlFieldIndex.EtId), null,
                fields[0], null,
                SetOperator.Equal,
                subSelectPredicate,
                null, 
                string.Empty, 
                1, 
                new SortExpression(SortClauseFactory.Create(EmployeeTlFieldIndex.EtValidFrom, SortOperator.Descending, "ETl"))
                );

            IRelationPredicateBucket predicateBucket    = new RelationPredicateBucket();
            Console.WriteLine(predicateBucket.Relations.Count);
            predicateBucket.PredicateExpression.Add(compareRangePredicate);
            predicateBucket.PredicateExpression.AddWithAnd(compareSetPredicate);

oh darn... the FROM clause emitter doesn't obey an object alias set, when a single table is the target, only with relations.

I'll fix it now.

(edit). I've mailed you an updated runtime library archive (to the email address you used in the forum). That should emit the alias for the object after the table name in teh FROM clause.

It works Thanks a lot ! Now I'll finish the merge code

cool sorry for the inconvenience.

Bah, I've not lost too much time on the alias bug, and you provide a very quick support

For info, on a 300 entities fetch (yes I know I'll not often fetch a so large page) it's now from 50 to 90% faster. It's 50% the first call, an 90% the next calls. I suppose the difference come from SQL cache : the cache is more efficient with only 2 queries (it was 301 before o_O)

Sounds great! I'm glad you could optimize your code, although it isn't optimal...