How to join query datasource to EITHER its sister datasource OR its parent?

(0) Share

Report

Posted on by TimothyOtt3

I am attempting to join a query datasource to Either its parent OR its sister, but am not getting the desired results.

I overrode the init method of the query and added a range to the subject datasource with a query expression after the super() similar to this:

datasourceRange.value('((whsLoadLine.inventTransId == salesLine.inventTransId) || (whsLoadLine.inventTransId == kitSalesLine.inventTransId))');

SalesLine = Parent DS

kitSalesLine = Child of SalesLine

whsLoadLine = Child of SalesLine

The issue is that it is not returning whsLoadLine records for the kitSalesLines.

At this point, my assumption is that this just isn't possible. I can't make the whsLoadLine a child of the KitSalesLine because it would change the formatting of the XML. This is an aif document query.

I have the same question (0)

All responses (5)

Answers (0)

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

Like (0)

Report

You're skipping an important step in debugging. You write X++ code generating some SQL code and then you check what data is returned by this SQL code. But you don't have any idea whether your X++ code generated the right query and if not, what's wrong with it.

Also, you're hard-coding data source names in your expressions, but are you sure they're correct? Maybe they're named something like whsLoadLine_1. A safer approach is using QueryBuildDataSource.name().

By the way, have you verified that your code gets executed at all?

Was this reply helpful? Yes No
MuthukumaranAX 2,903 on at

Like (0)

Report

can you confirm what join type you have used b/w these data sources?

thanks

Was this reply helpful? Yes No
TimothyOtt3 on at

Like (1)

Report

Hi Martin,

Thanks for the Reply. I guess there is a fine line between being concise and providing enough details to a question.

I did debug and check the SQL, which appeared fine to me. I used each datasource.toString() which showed me the joins for each DB, but I was not able to get the raw sql. Is there a good method for this? I attempted to use sql profiler, but I could not find this query hitting the DB.

I did use a query expression with QuiryBuildDataSource.name(), but was just trying to simplify my question by writing it out.

Yes, the code gets executed. In fact, it returns the first part of the join, meaning that it does return whsLoadLine records for the Parent (salesLine), but not the sister (kitLines).

I think the question is simple. Can you join to both a sister database OR a parent database using a query object?

Was this reply helpful? Yes No
TimothyOtt3 on at

Like (0)

Report

Here is the query and how I attempted to make the join in the init method. You can see when I test the query that the join is showing in SQL. I dont have the output here, but it does return loadLine records for the Parent (salesLine) but not for the Sister (kitLine). Thanks.

Was this reply helpful? Yes No
Martin Dráb 237,882 Most Valuable Professional on at

Like (0)

Report

I did use similar conditional joins in query objects. But not it AOT queries used by AIF document services.

I don't know if I've ever did *exactly* the same thing as yourself, therefore I can't give you a definite answer to whether it is possible without actually trying it.

You can catch the actual T-SQL code by either the profiler or extended events.

Was this reply helpful? Yes No