Hello,

i've got a problem to make relations with two instances of a table. The target query is the following :

SELECT DISTINCT 
LPA_P3.PROCESS_ID AS ProcessId, 
LPA_P3.CODE AS Code, 
LPA_P3.STATUS AS Status, 
LPA_P3.VERSION AS Version, 
LPA_P3.IN_USE AS InUse, 
LPA_P3.FINAL_PRODUCT AS FinalProduct,
LPA_P3.MOLECULE_ID AS MoleculeId,  
LPA_P3.LAST_MOD_DATE AS LastModDate, 
LPA_P3.CREATION_DATE AS CreationDate, 
LPA_P3.MODIFIED_BY AS ModifiedBy 
FROM  
MEDICIS.MOLECULE LPA_T1 , 
MEDICIS.SUBSTANCE LPA_S2 , 
MEDICIS.PROCESS LPA_P3 ,
MEDICIS.MOLECULE LPA_T4 
WHERE  
LPA_T1.MOLECULE_ID=LPA_S2.MOLECULE_ID 
AND LPA_S2.SUBSTANCE_ID=LPA_P3.FINAL_PRODUCT 
AND ( ( UPPER(LPA_T1.MOLNUM) LIKE '%SR1%'))
AND LPA_T4.MOLECULE_ID=LPA_P3.MOLECULE_ID 
AND ( ( UPPER(LPA_T4.MOLNUM) LIKE '%SR12%'))

My code for this query with an adapter is the following :

bucket.Relations.Add(SubstanceEntity.Relations.MoleculeEntityUsingMoleculeId, "TargetMolecule");
bucket.Relations.Add(SubstanceEntity.Relations.ProcessEntityUsingFinalProduct);

likeFilter = new FieldLikePredicate(MoleculeFields.Molnum, null, "TargetMolecule", DSIChimie_BD.CastStringForSearch(strMolCodeTarget.ToUpper()));
likeFilter.CaseSensitiveCollation = true;
bucket.PredicateExpression.Add(likeFilter);

bucket.Relations.Add(ProcessEntity.Relations.MoleculeEntityUsingMoleculeId, "SubMolecule");

likeFilter = new FieldLikePredicate(MoleculeFields.Molnum, null, "SubMolecule", DSIChimie_BD.CastStringForSearch(strMolCodeInProc.ToUpper()));
likeFilter.CaseSensitiveCollation = true;
bucket.PredicateExpression.Add(likeFilter);

The error code returned is : "Relation at index 2 doesn't contain an entity already added to the FROM clause. Bad alias?"

Relation at index 2 corresponds to the table Molecule with the instance name "SubMolecule".

Can you help me please because I can't find the right way to follow to correct my bug.

Thank you

You code looks fine, are you sure that's all of it?

Which LLBLGen Pro runtime library version are you using?

I'm using 2.6 LLBLGEN PRO version.

That's not the real situation of my query but it looks similar.

Here is my real query that I want to implement :

bucket.Relations.Add(SubstanceEntity.Relations.MoleculeEntityUsingMoleculeId, "TargetMolecule");
bucket.Relations.Add(SubstanceEntity.Relations.ProcessEntityUsingFinalProduct);

likeFilter = new FieldLikePredicate(MoleculeFields.Molnum, null, "TargetMolecule", DSIChimie_BD.CastStringForSearch(strMolCodeTarget.ToUpper()));
likeFilter.CaseSensitiveCollation = true;
bucket.PredicateExpression.Add(likeFilter);

bucket.Relations.Add(MolInPartEntity.Relations.MoleculeEntityUsingMoleculeId, "SubMolecule");
bucket.Relations.Add(MolInPartEntity.Relations.PartEntityUsingPartId);
bucket.Relations.Add(PartEntity.Relations.ProcessEntityUsingProcessId);

likeFilter = new FieldLikePredicate(MoleculeFields.Molnum, null, "SubMolecule", DSIChimie_BD.CastStringForSearch(strMolCodeInProc.ToUpper()));
likeFilter.CaseSensitiveCollation = true;
bucket.PredicateExpression.Add(likeFilter);

There are two additionnal tables between Process and Molecule for the second way.

To simplify :

For the first way of my query : Process -> Substance -> Molecule (named "Target Molecule")

For the second way of my query : Process -> Part -> MolInPart -> Molecule (named "SubMolecule")

Thanks for your help.

Sometime you have to reorder the relations and swap them (instead of Order->Customer, Customer<-Order, for instance). Please try this:

bucket.Relations.Add(SubstanceEntity.Relations.MoleculeEntityUsingMoleculeId, "TargetMolecule");
bucket.Relations.Add(SubstanceEntity.Relations.ProcessEntityUsingFinalProduct);
bucket.Relations.Add(ProcessEntity.Relations.PartEntityUsingProcessId);
bucket.Relations.Add(PartEntity.Relations.MolInPartEntityUsingPartId);
bucket.Relations.Add(MolInPartEntity.Relations.MoleculeEntityUsingMoleculeId, "SubMolecule");

likeFilter = new FieldLikePredicate(MoleculeFields.Molnum, null, "TargetMolecule", DSIChimie_BD.CastStringForSearch(strMolCodeTarget.ToUpper()));
likeFilter.CaseSensitiveCollation = true;
bucket.PredicateExpression.Add(likeFilter);

likeFilter = new FieldLikePredicate(MoleculeFields.Molnum, null, "SubMolecule", DSIChimie_BD.CastStringForSearch(strMolCodeInProc.ToUpper()));
likeFilter.CaseSensitiveCollation = true;
bucket.PredicateExpression.Add(likeFilter);

Thanks a lot, it works perfectly, you've solve my problem.

It was just a question of relation position.