Monday 4 March 2019

Join multiples tables with same table using query build data source

Today, I will be discussing about one of the common issue related to query creation using  query build data source. 

Let's suppose we have following tables.
 - Leave Assignment  table - (Base Table) - Namely Parent for demo purpose
 - HcmEmployment table   - Namely Child 1 for demo purpose
 - Leave Plan table    - Namely Child 1 for demo purpose

We want to link base table with both hcm employment (child1) and leave plan table (child2)  using query build data source. Below is the code used for performing this operation.

public QueryRun   fetchMultipleTableQueryDemo()
{
    QueryBuildDataSource hcmEmploymentds;
    QueryBuildDataSource qbds;
    Query query = new Query();
       
    // Adding parent  as a datasource
    QueryBuildDataSource qbds = query.addDataSource(tableNum(Parent));

    // Adding child 1 as a datasource
    hcmEmploymentds= qbds.addDataSource(tableNum(Child1));
    hcmEmploymentds.joinMode(JoinMode::InnerJoin);
    hcmEmploymentds.addLink(fieldNum(Parent, Worker),fieldNum(Child1, Worker));
    hcmEmploymentds.fetchMode(QueryFetchMode::One2One);

   // Adding child 2 as a datasource
   qbds = qbds.addDataSource(tableNum(Child2));
   qbds.joinMode(JoinMode::InnerJoin);
   qbds.addLink(fieldNum(Parent, LeaveDetail), fieldNum(Child1, RecId));
   qbds.fetchmode(QueryFetchMode::One2One);
       
    return new QueryRun(query);
}

No comments:

Post a Comment

Note: only a member of this blog may post a comment.

Connect and upload in azure blob with azure key vault using x++

 Today, I will be sharing details about file upload in azure blob using azure vault that includes pre requisite and code snippet used to it....