How can I create a Custom Relation for the following scenario in a Dynamic Typed List?

 from tableA  left outer join tableB  on tableA .ColumnA = tableB.ColumnB Join
tableC on tableC.ColumnA = 'VM'

I am struggling on the following part to define as a Custom Relation through the code...

 Join tableA on tableA.ColumnA = 'ABC'

Thanks.

I think I figured it out...its there in the help!!!

 // C#
IPredicateExpression customFilter = new PredicateExpression();
customFilter.Add(PredicateFactory.CompareValue(OrderFieldIndex.ShipCountry, ComparisonOperator.Equal, "Mexico"));
// ... 
bucket.Relations.Add(CustomerEntity.Relations.OrderEntityUsingCustomerID).CustomFilter = customFilter;
// ...

glad it's solved!

Well, I am getting this error when I tried the above method...

Relation at index 2 doesn't contain an entity already added to the FROM clause. Bad alias?

Here is my code...

 IDataAccessAdapter adapter = DataAccessAdapterFactory.GetDataAdapter();
            ResultsetFields fields = new ResultsetFields(3);
            fields.DefineField(VehViolTypesFieldIndex.VehViolDesc, 0, "VehViolDesc");
            fields.DefineField(VehViolIncidFieldIndex.IncidDte, 1, "IncidDte");
            fields.DefineField(VehViolIncdDtlFieldIndex.FineAmt, 2, "FineAmt");

            IRelationPredicateBucket bucket = new RelationPredicateBucket();
            bucket.Relations.Add(VehViolIncdDtlEntity.Relations.VehViolIncidEntityUsingIncidNum, JoinHint.Inner);
            bucket.Relations.Add(VehViolIncdDtlEntity.Relations.VehViolTypesEntityUsingVehViolType, JoinHint.Left);

            // CustomFilter
            IPredicateExpression customFilter = new PredicateExpression();
            customFilter.Add(PredicateFactory.CompareValue(StudLifeConfigFieldIndex.SubModuleCde, ComparisonOperator.Equal, "VM"));

            // define custom relation self-joined
            IEntityRelation CustomRelation = new EntityRelation(RelationType.OneToOne);
            CustomRelation.AddEntityFieldPair(EntityFieldFactory.Create(StudLifeConfigFieldIndex.SubModuleCde),
                EntityFieldFactory.Create(StudLifeConfigFieldIndex.SubModuleCde));

            // add this as a relation
            bucket.Relations.Add(CustomRelation,"StudLifeConfigB",JoinHint.None).CustomFilter = customFilter;

adapter.FetchTypedList(fields, dynamicList, bucket, 0, null, false, null);

Your relation:

IEntityRelation CustomRelation = new EntityRelation(RelationType.OneToOne);
CustomRelation.AddEntityFieldPair(EntityFieldFactory.Create( StudLifeConfigFieldIndex.SubModuleCde),
     EntityFieldFactory.Create(StudLifeConfigFieldIndex.SubModuleCde));

joins Studlifeconfig 2 times, though that entity isn't in the pack of the previous 2 relations added.

so this results in: a inner join b on.. b inner join c on... k inner join l on..

k isn't attached to a-b-c.

Frans, Problem is I don't have any FK relations to a-b-c tables from k...so how do I handle in this scenario?. Here is my FROM clause...

 FROM veh_viol_incd_dtl join veh_viol_incid ON veh_viol_incd_dtl.incid_num = veh_viol_incid.incid_num LEFT OUTER JOIN veh_viol_types ON veh_viol_incd_dtl.veh_viol_type = veh_viol_types.veh_viol_type JOIN stud_life_config ON sub_module_cde = 'VM'

Ganesh wrote:

Frans, Problem is I don't have any FK relations to a-b-c tables from k...so how do I handle in this scenario?. Here is my FROM clause...

 FROM veh_viol_incd_dtl join veh_viol_incid ON veh_viol_incd_dtl.incid_num = veh_viol_incid.incid_num LEFT OUTER JOIN veh_viol_types ON veh_viol_incd_dtl.veh_viol_type = veh_viol_types.veh_viol_type JOIN stud_life_config ON sub_module_cde = 'VM'

I fail to see what you want to achieve with sub_module_cde="VM" in that query. As it doesn't have any relation with the rest of the tables, it won't affect the rows returned from them (IMHO).

Thats right, but my WHERE criteria will make sure rest of filtering but for now I get the error message no matter whatever I do!!!...

Relation at index 2 doesn't contain an entity already added to the FROM clause. Bad alias?

Here is my WHERE criteria...

 where veh_viol_incd_dtl.sa_vp_cde = sa_vp_cde and veh_viol_incd_dtl.vp_num = vp_num and veh_viol_incd_dtl.id_num = id_num_vp_holder and veh_viol_incid.incid_dte is not null and veh_viol_incd_dtl.veh_viol_type is not null and ((stud_life_config.show_on_web = 'G' and veh_viol_incd_dtl.viol_access_rstr = 'G') or (stud_life_config.show_on_web = 'R' and veh_viol_incd_dtl.viol_access_rstr = 'R') or stud_life_config.show_on_web = 'B')

If I run the entire SQL in MSS, I don't get any error message and it works fine!

SELECT DISTINCT veh_viol_types.veh_viol_desc, veh_viol_incid.incid_dte, veh_viol_incd_dtl.fine_amt 
FROM veh_viol_incd_dtl JOIN veh_viol_incid ON veh_viol_incd_dtl.incid_num = veh_viol_incid.incid_num LEFT OUTER JOIN veh_viol_types ON veh_viol_incd_dtl.veh_viol_type = veh_viol_types.veh_viol_type JOIN  stud_life_config ON sub_module_cde = 'VM' 
WHERE veh_viol_incd_dtl.sa_vp_cde = sa_vp_cde AND veh_viol_incd_dtl.vp_num = vp_num AND veh_viol_incd_dtl.id_num = id_num_vp_holder AND veh_viol_incid.incid_dte is not null AND veh_viol_incd_dtl.veh_viol_type is not null AND ((stud_life_config.show_on_web = 'G' AND veh_viol_incd_dtl.viol_access_rstr = 'G') OR (stud_life_config.show_on_web = 'R' AND veh_viol_incd_dtl.viol_access_rstr = 'R') OR stud_life_config.show_on_web = 'B')

What am I still missing?

Ganesh wrote:

Thats right, but my WHERE criteria will make sure rest of filtering but for now I get the error message no matter whatever I do!!!...

Relation at index 2 doesn't contain an entity already added to the FROM clause. Bad alias?

Here is my WHERE criteria...

 where veh_viol_incd_dtl.sa_vp_cde = sa_vp_cde and veh_viol_incd_dtl.vp_num = vp_num and veh_viol_incd_dtl.id_num = id_num_vp_holder and veh_viol_incid.incid_dte is not null and veh_viol_incd_dtl.veh_viol_type is not null and ((stud_life_config.show_on_web = 'G' and veh_viol_incd_dtl.viol_access_rstr = 'G') or (stud_life_config.show_on_web = 'R' and veh_viol_incd_dtl.viol_access_rstr = 'R') or stud_life_config.show_on_web = 'B')

If I run the entire SQL in MSS, I don't get any error message and it works fine!

yes, but it doesn't make sense.

->

SELECT DISTINCT veh_viol_types.veh_viol_desc, veh_viol_incid.incid_dte, veh_viol_incd_dtl.fine_amt 
FROM veh_viol_incd_dtl JOIN veh_viol_incid ON veh_viol_incd_dtl.incid_num = veh_viol_incid.incid_num LEFT OUTER JOIN veh_viol_types ON veh_viol_incd_dtl.veh_viol_type = veh_viol_types.veh_viol_type JOIN  stud_life_config ON sub_module_cde = 'VM' 

will join each row resulting from

veh_viol_incd_dtl JOIN veh_viol_incid ON veh_viol_incd_dtl.incid_num = veh_viol_incid.incid_num LEFT OUTER JOIN veh_viol_types ON veh_viol_incd_dtl.veh_viol_type = veh_viol_types.veh_viol_type

with all stud_life_config rows where sub_module_cde = "VM".

Then, you're filtering rows out, but that doesn't affect the rows already returned formed by:

veh_viol_incd_dtl JOIN veh_viol_incid ON veh_viol_incd_dtl.incid_num = veh_viol_incid.incid_num LEFT OUTER JOIN veh_viol_types ON veh_viol_incd_dtl.veh_viol_type = veh_viol_types.veh_viol_type

as it's typically a cross join. It will result in a lot of duplicate rows. The main issue is: you don't select any data from stud_life_config, so: 1) it doesn't affect the outcome of the query, as the resultset is not selected from stud_life_config 2) filtering on it won't limit the resultset as every row in stud_life_config is joined with every row in the data of which the resultset is retrieved.

Typical example:

select  distinct c.*
from    customers c inner join orders o
    on c.customerid = o.customerid
--  join products p on p.categoryid=2
--where     p.Supplierid=3

Now, if I uncomment the last 2 rows, do you think it will affect the outcome? No, because it won't limit any results from the join from which the resultset is retrieved.

Try it, remove all filters on stud_life_config frm the where clause and the clause from the join, it should result in the same resultset.

Yep, Got your point. It doesn't make any difference if its there or not!

It also should run faster now, as no duplicates have to be filtered out