Relation at index 1 - Bad Alias ?

Posts   
 
    
Posts: 26
Joined: 29-Sep-2011
# Posted on: 14-Dec-2016 17:23:00   

Here is the relation I have in my query RelationCollection relations = new RelationCollection(); relations.Add(LiWDecisionEntity.Relations.LiWDcsnSubCnctrEntityUsingRateKey, "WD", "SC", JoinHint.Inner); IEntityRelation newRelation = new EntityRelation(); newRelation.AddEntityFieldPair(LiWDecisionFields.CntctAddrsSeqNu, LiAddrLnkFields.AddrLnkSeqNu); relations.Add(newRelation, "WD", “wdAddrLinkAlias”, JoinHint.Inner); relations.Add(LiAddrLnkEntity.Relations.LiAddressEntityUsingAddrSeqNu, “wdAddrLinkAlias”, “wdAddrAlias”, JoinHint.Inner); relations.Add(LiWDcsnSubCnctrEntity.Relations.LiAddrLnkEntityUsingAddrLnkSeqNu, "SC", “SubCnctrAddrLinkAlias”, JoinHint.Inner); relations.Add(LiAddrLnkEntity.Relations.LiAddressEntityUsingAddrSeqNu, “SubCnctrAddrLinkAlias”, “SubCnctrAddrAlias”, JoinHint.Inner);

When I execute the query I get the following error: Relation at index 1 doesn't contain an entity already added to the FROM clause. Bad alias?

Is there something wrong in the relation?

Here is the SQL that i am trying to acheive

select saddr.addr_1, saddr.city, saddr.zip, waddr.addr_1, waddr.city, waddr.zip from LI.LI_W_DECISION d inner join LI.LI_W_DCSN_SUB_CNCTR s on D.RATE_KEY = s.rate_key inner join li.li_addr_lnk wal on d.cntct_addrs_seq_nu = wal.addr_lnk_seq_nu inner join li.li_address waddr on waddr.addr_seq_nu = wal.addr_seq_nu inner join li.li_addr_lnk sal on s.addr_lnk_seq_nu = sal.addr_lnk_seq_nu inner join li.li_address saddr on saddr.addr_seq_nu = sal.addr_seq_nu order by saddr.create_dt desc

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 14-Dec-2016 19:30:22   

Assuming you are fetching a collection of LiWDecisionEntity.

Try removing the alias of the LiWDecisionEntity, usually you don't need aliasing unless you are joining to the same entity more than once. The thing is in your select statement the "WD" aliasing will not be used, and hence the From statement will have LiWDecisionEntity without the alias, then you are trying to use the alias, in your first join statement, where it fails.

Try the following:

RelationCollection relations = new RelationCollection();
            relations.Add(LiWDecisionEntity.Relations.LiWDcsnSubCnctrEntityUsingRateKey, "SC", JoinHint.Inner);
IEntityRelation newRelation = new EntityRelation();
newRelation.AddEntityFieldPair(LiWDecisionFields.CntctAddrsSeqNu, LiAddrLnkFields.AddrLnkSeqNu);
relations.Add(newRelation, “wdAddrLinkAlias”, JoinHint.Inner);
relations.Add(LiAddrLnkEntity.Relations.LiAddressEntityUsingAddrSeqNu, “wdAddrLinkAlias”, “wdAddrAlias”, JoinHint.Inner);   
relations.Add(LiWDcsnSubCnctrEntity.Relations.LiAddrLnkEntityUsingAddrLnkSeqNu, "SC", “SubCnctrAddrLinkAlias”, JoinHint.Inner);
relations.Add(LiAddrLnkEntity.Relations.LiAddressEntityUsingAddrSeqNu, “SubCnctrAddrLinkAlias”, “SubCnctrAddrAlias”, JoinHint.Inner);

Posts: 26
Joined: 29-Sep-2011
# Posted on: 15-Dec-2016 18:37:52   

Thank you for the suggestion, that fixed the problem. simple_smile