Hi everybody,

I want to code this following query with an adapter but it's too difficult for me :

SELECT * 
FROM VARIATION v, COMPOUND c, REACTION r, COMPOUND_ROLE_VOC crv, SUBSTANCE s
WHERE r.REACTION_ID = v.REACTION_ID
AND v.VARIATION_ID = c.VARIATION_ID
AND c.SUBSTANCE_ID = s.SUBSTANCE_ID
AND crv.ROLE_ID = c.ROLE_ID
AND crv.VOC_TEXT = 'P'
AND r.REACTION_ID IN 
(
SELECT REACTION_ID
FROM VARIATION v, MOLECULE m, COMPOUND c, REACTION r, COMPOUND_ROLE_VOC crv, SUBSTANCE s
WHERE r.REACTION_ID = v.REACTION_ID
AND v.VARIATION_ID = c.VARIATION_ID
AND c.SUBSTANCE_ID = s.SUBSTANCE_ID
AND crv.ROLE_ID = c.ROLE_ID
AND m.MOLECULE_ID = c.MOLECULE_ID
AND m.MOLECULE_TYPE = 6
)

All is OK except the IN (...). I've founded no solution...

I found some documentation about the FieldCompareSetPredicate :

FieldCompareSetPredicate fcsp = new FieldCompareSetPredicate(ReactionFields.ReactionId, null, ReactionFields.ReactionId, null, SetOperator.In, (ReactionFields.ReactionId == 9));

I don't know how to put a complex query inside the IN (...), only simple query without relation are accepted.

Please help me to replace the (ReactionFields.ReactionId == 9) in my sample by a complex query.

Hi there.

You are very close. You just need to add the relations so the predicate at the FieldCompareSetPredicate would be valid. According to your SQL query, and assuming you have all relations set on the LLBLGenPro Designer, the filter should look like:

// build the relations used in the FieldCompareSetPredicate
subQueryRelations = new RelationCollection();
subQueryRelations.Add(CustomersEntity.Relations.OrdersEntityUsingCustomerId);
subQueryRelations.Add(ReactionEntity.Relations.VariationEntityUsingReactionId);
subQueryRelations.Add(VariationEntity.Relations.CompundEntityUsingVariationId);
subQueryRelations.Add(CompundEntity.Relations.SubstanceEntityUsingSubstanceId);
subQueryRelations.Add(CompundEntity.Relations.CompundRoleVocEntityUsingRoleId);
subQueryRelations.Add(CompundEntity.Relations.MoleculeEntityUsingMoleculeId);

// set the filter
FieldCompareSetPredicate fcsp = 
    new FieldCompareSetPredicate( ReactionFields.ReactionId, null, ReactionFields.ReactionId, null, 
        SetOperator.In, (MoleculeFields.MoleculeType == 6), subQueryRelations);

// now add the subfilter to the whole filter
IRelationPredicateBucket wholeFilter = new RelationPredicateBucket();
wholeFilter.PredicateExpression.Add(fcsp);

// ... etc.

Hope helpful

It works perfectly. Thank you very very much.

I didn't see this kind of constructor. :-( That's a mistake.

You're the boss of LLBLGENRPO man. :-)