Hello folks,

I have a configuration table in wich I have a parent and a child resource. I want the resource name field to be fetched by using the relation ResourceEntityUsingResourceid and the child resource name to be fetched by the relation ResourceEntityUsingChildresourceid.

I think I have to use the entity alias for this purpose. I tried a lot of different settings but could not figure out how to do it.

Can somebody please help me with this.

Below is my code without the values for the entity aliases.

{
    DataAccessAdapter   adapter =   new DataAccessAdapter();
    
    try 
    {
        //Define    view    fields
    
        ResultsetFields fields  =   new ResultsetFields(5);
        fields.DefineField(ConfigurationhistoryFieldIndex.Dateadded,0,"Date added");
        fields.DefineField(ConfigurationhistoryFieldIndex.Datedeleted,1,"Date   deleted");
        fields.DefineField(ResourceFieldIndex.Name,2,"Resource  name");
        fields.DefineField(ResourceFieldIndex.Name,3,"Childresource name");
        fields.DefineField(GroupFieldIndex.Name,4,"Groupname");
            
            //Define    used    relations
        IRelationPredicateBucket    relationBucket  =   new RelationPredicateBucket();
        relationBucket.Relations.Add(ConfigurationhistoryEntity.Relations.
            ResourceEntityUsingResourceid);
        relationBucket.Relations.Add(ConfigurationhistoryEntity.Relations.  
            ResourceEntityUsingChildresourceid);
        relationBucket.Relations.Add(
            ConfigurationhistoryEntity.Relations.GroupEntityUsingGroupid);
    
        if  (filter !=  null)   
        {
            relationBucket.PredicateExpression.Add(filter);
        }
    
        //Create    datable wich    will    contain the configuration   history view
        DataTable   allConfigurations   =   new DataTable();
    
        //Sort  on  date    added   descending
        ISortExpression sorter  =   new SortExpression(SortClauseFactory.Create (
            ConfigurationhistoryFieldIndex.Dateadded,   SortOperator.Descending));
                    
        adapter.FetchTypedList(fields,allConfigurations,relationBucket,0,sorter,false);
                
        return  allConfigurations;
    }   
    finally
    {
        adapter.Dispose();
    }           
}

Please see the section "Using the generated code -> Adapter -> Using the typed list and typed view classes -> Creating dynamic lists "

In there you'll see an example of using two times the same entity in a dynamic list. You have to specify aliasses when you add the relations to the relation collection, for the start and/or for the end entity. If you don't want to alias an entity, specify string.Empty.

These aliasses are also used when you call DefineField, in an overload of DefineField.

Hello Otis,

Thank you for your fast response . I tried the following:

public DataTable getConfigurationHistoryView(IPredicateExpression filter) 
{
  DataAccessAdapter adapter = new DataAccessAdapter();

  try 
 {
   //Define view fields
  ResultsetFields fields = new ResultsetFields(5);
  fields.DefineField(ConfigurationhistoryFieldIndex.Dateadded,0,"Dateadded");
  fields.DefineField(ConfigurationhistoryFieldIndex.Datedeleted,1,"Datedeleted");
  fields.DefineField(ResourceFieldIndex.Name,2,"Resourcename","ParentResource");
  fields.DefineField(ResourceFieldIndex.Name,3,"Childresourcename","ChildResource");
  fields.DefineField(GroupFieldIndex.Name,4,"Groupname");
            
  //Define used relations
  IRelationPredicateBucket relationBucket = new RelationPredicateBucket();
  relationBucket.Relations.Add (ConfigurationhistoryEntity.Relations.ResourceEntityUsingResourceid,"ParentResource");
   relationBucket.Relations.Add(ConfigurationhistoryEntity.Relations.ResourceEntityUsingChildresourceid,"ChildResource");
   relationBucket.Relations.Add(ConfigurationhistoryEntity.Relations.GroupEntityUsingGroupid);

  //Create datable wich will contain the configuration history view
  DataTable allConfigurations = new DataTable();

   //Sort on date added descending
   ISortExpression sorter = new SortExpression(SortClauseFactory.Create(ConfigurationhistoryFieldIndex.Dateadded, SortOperator.Descending));
                
   adapter.FetchTypedList(fields,allConfigurations,relationBucket,0,sorter,false);
                
   return allConfigurations;
  } 
  finally
 {
  adapter.Dispose();
 }          
}

I specified the alias "ParentResource" for the resultfield wich holds the name of the parentresource and the alias "ChildResource" for the resultfield wich holds the name of the childresource. I also specified the endaliasses for the relations.

When I run this code I get the following error message "ORA-00904: invalid column name".

What am I doing wrong?

Instead of doing:

ISortExpression sorter = new SortExpression(SortClauseFactory.Create(ConfigurationhistoryFieldIndex.Dateadded, SortOperator.Descending));

do:

ISortExpression sorter = new SortExpression(new SortClause(fields[0], SortOperator.Descending));

As the order by then uses the correct aliased column