web
You’re offline. This is a read only version of the page.
close
Skip to main content
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Finance | Project Operations, Human Resources, ... / Is OuterJoin in Query ...
Finance | Project Operations, Human Resources, ...
Answered

Is OuterJoin in Query creates duplicate row ?

(0) ShareShare
ReportReport
Posted on by Voltes 596

Hi all,

I'm creating a query, using query builder, and I found duplicate row in my output. But strangely, if I query one by one of the 'participate' table, it produce 1 record each.

The join is OuterJoin, is this type can produce duplicate ?

Kindly advice,

Thanks

I have the same question (0)
  • Voltes Profile Picture
    Voltes 596 on at
    RE: Is OuterJoin in Query creates duplicate row ?

    Hi guys,

    this is actually the query:

    qbds = query.addDataSource(tableNum(CaseDetailBase));

    qbds1 = qbds.addDataSource(tableNum(CaseCategoryHierarchyDetail));

    qbds1.addLink(fieldNum(CaseDetailBase,CategoryRecId ), fieldNum(CaseCategoryHierarchyDetail, RecId));

    qbds1.joinMode(JoinMode::InnerJoin);

    // add to worker

    qbds2 = qbds.addDataSource(tableNum(HcmWorker));

    qbds2.addLink(fieldNum(CaseDetailBase, OwnerWorker), fieldNum(HcmWorker, RecId)); 

    qbds2.joinMode(JoinMode::OuterJoin);

    Qr = new QueryRun(query);

    while (qr.next())

    {

    cdb = qr.get(tableNum(CaseDetailBase));

    hcmw = qr.get(tableNum(HcmWorker));

    info(strFmt("%1 %2 %3 %4 %5", hcmw.RecId, cdb.CaseId, cdb.OwnerWorker));

    }

    Again if I create 2 query for each and run it, it will show 1 record each. The Relation of OwnerWorker and HCMWorker.recid is RelationForeginKey type, does it has anything to do with it ?

    Please help

    Thanks

  • Sergei Minozhenko Profile Picture
    Sergei Minozhenko 23,093 on at
    RE: Is OuterJoin in Query creates duplicate row ?

    Hi Voltes,

    Do you use to see the full query for qbds variable in debugger? I have a feeling that

    qbds2 = qbds.addDataSource(tableNum(HcmWorker));

    doesn't work properly. Try to use qbds1 instead of qbds.

  • Voltes Profile Picture
    Voltes 596 on at
    RE: Is OuterJoin in Query creates duplicate row ?

    Hi Sergei,

    but the link is between the 1st table, will it be ok using QBDS1 instead ?

    Thanks,

  • Verified answer
    Sergei Minozhenko Profile Picture
    Sergei Minozhenko 23,093 on at
    RE: Is OuterJoin in Query creates duplicate row ?

    Hi Voltes,

    Yes, but you need also specify the third parameter for addLink in this case:

    qbds2 = qbds1.addDataSource(tableNum(HcmWorker));

    qbds2.addLink(fieldNum(CaseDetailBase, OwnerWorker), fieldNum(HcmWorker, RecId), qbds.name());  

  • Voltes Profile Picture
    Voltes 596 on at
    RE: Is OuterJoin in Query creates duplicate row ?

    Hi Sergei,

    I just tried it, and it's not correct. The query then will be between the 2nd table and 3rd. And also with the strange pick of column for the 2nd table while the "Addlink" is defined with 1st table and 3rd table.

    this is from the query string Query:

    SELECT * FROM CaseDetailBase(CaseDetailBase_1) WHERE ((CaseId = N'00074')) JOIN * FROM CaseCategoryHierarchyDetail(CaseCategoryHierarchyDetail_1) ON CaseDetailBase.CategoryRecId = CaseCategoryHierarchyDetail.RecId OUTER JOIN * FROM HcmWorker(HcmWorker_1) ON CaseCategoryHierarchyDetail.TrackStatus = HcmWorker.RecId}

    But with the previous (original) query, actually I even cannot see the final query.

    Thanks

  • Voltes Profile Picture
    Voltes 596 on at
    RE: Is OuterJoin in Query creates duplicate row ?

    Hi Sergei.

    Thanks, I didn't know I should add the 3rd parameter.

    Resolved.

    GBU. :)

  • Voltes Profile Picture
    Voltes 596 on at
    RE: Is OuterJoin in Query creates duplicate row ?

    Hi Sergei,

    Just one more question. If lets say I want to extend the query, to add another table linked to this HCMWorker the 3rd table and then after that another table. Should all need to have the 3rd parameter ?

    So probably like this :

    query = new Query();

    // Add a datasource to the query

    qbds = query.addDataSource(tableNum(CaseDetailBase));

    qbds1 = qbds.addDataSource(tableNum(CaseCategoryHierarchyDetail));

    qbds1.addLink(fieldNum(CaseDetailBase,CategoryRecId ), fieldNum(CaseCategoryHierarchyDetail, RecId));

    qbds1.joinMode(JoinMode::InnerJoin);

    qbds2 = qbds1.addDataSource(tableNum(HcmWorker));

    qbds2.addLink(fieldNum(CaseDetailBase, OwnerWorker), fieldNum(HcmWorker, RecId), qbds.name());

    qbds2.joinMode(JoinMode::OuterJoin);

    qbds3 = qbds2.addDataSource(tableNum(DirPerson));

    qbds3.addLink(fieldnum(HcmWorker, Person), fieldNum(DirPerson, RecId));

    qbds3.joinMode(joinmode::OuterJoin);

    qbds4 = qbds3.addDataSource(tableNum(DirPartyTable));

    qbds4.addLink(fieldNum(DirPerson, RecId), fieldNum(DirPartyTable, RecId));

    qbds4.joinMode(joinmode::OuterJoin);

    Should all the "addlink" (in green) have the 3rd parameters ? I believe it's "no", right ?

    Many thanks in advance,

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Pallavi Phade – Community Spotlight

We are honored to recognize Pallavi Phade as our Community Spotlight honoree for…

Congratulations to the October Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > Finance | Project Operations, Human Resources, AX, GP, SL

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 724 Super User 2025 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 580 Most Valuable Professional

#3
CA Neeraj Kumar Profile Picture

CA Neeraj Kumar 554

Last 30 days Overall leaderboard

Featured topics

End-of-life Announcement for Microsoft Dynamics GP

Product updates

Dynamics 365 release plans