Hi all

I use llblgen since 2 years now but I still have problem with a quite simple query

Here is what I try to do:

SELECT COUNT(P2.coun_id) AS indentation, P1.coun_id
FROM counter_node AS P1, counter_node AS P2
WHERE P1.coun_left BETWEEN P2.coun_left AND P2.coun_right
GROUP BY P1.coun_id

I'm using adapter pattern, llglben v1.0.20051.060508

It's the FROM that I don't know how to do that ?

Thank you for any help

Would something like this work for generating your From

IRelationPredicateBucket bucket = new RelationPredicateBucket();
                EntityRelation relation = new EntityRelation(CounterNodeFields.CounId, CounterNodeFields.CounId, RelationType.OneToOne);
                bucket.Relations.Add(relation, "P1", "P2", JoinHint.Cross);

The BETWEEN predicate can be set as the ON clause in a join, when you set the CustomFilter on the relation with that predicate and also set the flag CustomFilterReplacesOnClause to true.

Thank you, it works perfectly

SELECT COUNT([LPA_P2].[COUN_ID]) AS [Nb], [LPA_P2].[COUN_ID] AS [CounId] FROM ( [HRANEW_DEV].[dbo].[COUNTER_NODE] [LPA_P1] (nolock) INNER JOIN [HRANEW_DEV].[dbo].[COUNTER_NODE] [LPA_P2] (nolock) ON
( [LPA_P2].[COUN_LEFT] BETWEEN [LPA_P1].[COUN_LEFT] AND [LPA_P1].[COUN_RIGHT] ) ) GROUP BY [LPA_P2].[COUN_ID]