Skip to main content

Notifications

Finance | Project Operations, Human Resources, ...
Answered

QueryRun not return data

(0) ShareShare
ReportReport
Posted on by 12
Hi Experts,
 
I have problem with QueryRun.
 
I create a query that join 3 table: A join B, then join C by field of A and B
 
Select statement is working fine: it return only 1 record as I expected.
 
 
 
 
I try to using query builder: qr.next() do not return any data.
 
I run t-sql from query.getSQLStatement() on SQL, it return 1 record as well;
 
 
  • Community member Profile Picture
    Community member 12 on at
    QueryRun not return data
    WOW, thank you much Martin Dráb, I've never seen any code using the third argument of addLink before, and it work like a charm :D

    Honestly, I still wonder why queryRun not return data as statement from query, because I always use query.getSQLstatement() to verify my query builder.
  • Verified answer
    Martin Dráb Profile Picture
    Martin Dráb 230,817 Most Valuable Professional on at
    QueryRun not return data
    The purpose of a dynalink is different. It's used to filter a query based on a record existing outside of the query. For example, in sales orders form, there is a dynalink between the list of orders and order lines. When a query to fetch lines executes, it's filtered by the selected sales order. There is no join between orders and lines; there are two separate queries.
     
    The correct approach in your case is using addLink() and utilizing the third argument: _parentDatasourceName. For example:
    QueryBuildDataSource aQbds = query.addDataSource(tableNum(TableA);
    ...
    QueryBuildDataSource cQbds = bQbds.addDataSource(tableNum(TableC);
    ...
    cQbds.addLink(fieldNum(TableA, ValA), fieldNum(TableC, ValA), aQbds.name());
  • Community member Profile Picture
    Community member 12 on at
    QueryRun not return data
    GirishS: the query that I run on SQL is generated from query.getSQLStatement(), Ive already check the query.toString() as well, the query is correct.
     
    I have to link 3 table in circle.
     
    tableA has ItemId, RefType
    tableB has ItemId, GroupId
    tableC has RefType, GroupId
     
    tableA join to tableB by ItemId, then join to tableC by C.RefType = A.RefType and C.GroupId = B.GroupId
     
    => the select statement is working fine as posted.
     
    but I need to implement to query builder to add some more range input by user.
    => Query add table A, then add tableB (link A and B), then add tableC (link B and C), then I have to add link C and A, but cannot add tableA again, so I used add addDynalink() to link tableC to existed tableA.
    => the query statement generate from query.toString() and query.getSQLStatement() is as I expected, and execute fine in SQL.
    if remove link of tableC to tableA, queryRun return more than 1 record and not correct.
     
    Ive just confuse why queryRun not return data as query statement.
     
     
  • GirishS Profile Picture
    GirishS 27,821 Super User 2024 Season 1 on at
    QueryRun not return data
    Hi,
     
    You are using the same QueryBuildDataSource buffer for all the tables - Create a separate QueryDataSource buffer for each table and add relations based on that.
    Try to print the query output to info log using query.toString() and identify how the query is built.
     
    Thanks,
    Girish S.
  • Martin Dráb Profile Picture
    Martin Dráb 230,817 Most Valuable Professional on at
    QueryRun not return data
    The dynalink makes no sense to me. Why do you have it there? I think you should delete the line with qbds.addDynalink().
     
    By the way, I strongly recommend you utilize table relations, as usual in F&O, rather than addLink(). You'll also get simpler code.

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

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Announcing Forum Attachment Improvements!

We're excited to announce that attachments for replies in forums and improved…

Vahid Ghafarpour – Community Spotlight

We are excited to recognize Vahid Ghafarpour as our February 2025 Community…

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,965 Super User 2025 Season 1

#2
Martin Dráb Profile Picture

Martin Dráb 230,817 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Product updates

Dynamics 365 release plans