Finance | Project Operations, Human Resources, ...

Answered

QueryRun not return data

(0) Share

Report

Posted on by Community member

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;

I have the same question (0)

All responses (5)

Answers (1)

Martin Dráb 237,965 Most Valuable Professional on at

Like (0)

Report

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.

Was this reply helpful? Yes No
GirishS 27,827 Moderator on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Community member 12 on at

Like (0)

Report

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.

Martin Dráb:

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.

Was this reply helpful? Yes No
Verified answer

Martin Dráb 237,965 Most Valuable Professional on at

Like (0)

Report
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());

Was this reply helpful? Yes No
Community member 12 on at

Like (1)

Report

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.

Was this reply helpful? Yes No

Join the conversation

Community site session details

QueryRun not return data

Helpful resources

Quick Links

Responsible AI policies

Neeraj Kumar – Community Spotlight

Congratulations to the November Top 10 Community Leaders!

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

Featured topics

Product updates

Community site session details

QueryRun not return data

Helpful resources

Quick Links

Responsible AI policies

Neeraj Kumar – Community Spotlight

Congratulations to the November Top 10 Community Leaders!

Subscribe to this forum!

Select categories

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

Featured topics

Product updates