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

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics AX (Archived) / How to join using Quer...
Microsoft Dynamics AX (Archived)

How to join using QueryBuilder?

(0) ShareShare
ReportReport
Posted on by Community Member

Hi, I'm trying to create a QueryBuilder with joins, but I must be doing something wrong. How is it done?

Here's what I've been doing so far:




Query query;
    QueryRun queryRun;
    QueryBuildDataSource dataSource;
    QueryBuildDataSource dataSource2;
    QueryBuildDataSource dataSource3;
    str textDesc = "";

    query = new Query();
    query.queryType(QueryType::Join);
    query.addDataSource(tableNum(LogisticsAddressCountryRegionTranslation), "LogisticsAddressCountryRegionTranslation", UnionType::Union);
    query.addDataSource(tableNum(Currency), "Currency", UnionType::Union);
    query.addDataSource(tableNum(LogisticsAddressCountryRegion), "LogisticsAddressCountryRegion", UnionType::Union);
    dataSource = query.dataSourceTable(tableNum(LogisticsAddressCountryRegionTranslation));
    dataSource2 = query.dataSourceTable(tableNum(Currency));
    dataSource3 = query.dataSourceTable(tableNum(LogisticsAddressCountryRegion));

    SysQuery::findOrCreateRange(
        dataSource, fieldNum(LogisticsAddressCountryRegionTranslation, LanguageId)
    ).value(
        "EN-US"
    );

    if (textDesc != "")
    {
        SysQuery::findOrCreateRange(
            dataSource, fieldNum(LogisticsAddressCountryRegionTranslation, ShortName)
        ).value(
            SysQuery::valueLike(textDesc)
        );
    }

    queryRun = new QueryRun(query);

 
But the query is not joined. I can see 3 distinct queries (for each datasource) at the QueryRun. Check it below:


NAME: queryRun VALUE: Query object 20a9d848: SELECT * FROM LogisticsAddressCountryRegionTranslation(LogisticsAddressCountryRegionTranslation) WHERE ((LanguageId = N'EN-US')); SELECT * FROM Currency(Currency); SELECT * FROM LogisticsAddressCountryRegion(LogisticsAddressCountryRegion) TYPE: QueryRun

How do I join using QueryBuilder?

Thanks

*This post is locked for comments

I have the same question (0)
  • Verified answer
    Ivan (Vanya) Kashperuk Profile Picture
    Ivan (Vanya) Kashperuk on at

    You either need to use

    qbds.relations(true); if the relation is defined (which I assume it is in AX 2012)

    or manually add the necessary links

    qbds.addLink()

  • Community Member Profile Picture
    Community Member on at

    Thanks for the answer. Is this what I was supposed to do:

    dataSource.relations(true);
dataSource2.relations(true);
dataSource3.relations(true);


    If so, it didn't work the way I wanted... The only DS that was joint was the 1st and 3rd.

    Any suggestions?

    Thanks again.

  • Ivan (Vanya) Kashperuk Profile Picture
    Ivan (Vanya) Kashperuk on at

    Yes. Pretty much.

    You also need to set

    qbds.fetchMode(QueryFetchMode::One2One)

    because of your complex query

  • Community Member Profile Picture
    Community Member on at

    I get it, but the answer I got at C# is

    DataSet.Tables
{
    DataSource1.Rows[],
    DataSource2.Rows[],
    DataSource3.Rows[]
}


    Do you understand what I mean? They don't come as a "single result", as you would expect if you'd do a SQL query to project data from multiple tables. The result of that SQL query would be one new table with the fields you selected. The way I got the result, I'd have to iterate over each row (assuming they are in order) and with that huge leap of faith create my C# object for each row.

    Do you understand my specs? Is there a way to solve this?

    Thanks.

  • Suggested answer
    Ivan (Vanya) Kashperuk Profile Picture
    Ivan (Vanya) Kashperuk on at

    OK, I looked at your query more closely.

    Can you write us your expectation what the query should look like?

    There is no relation between Currency and LogisticsAddressCountryRegionTranslation... Do you want all the rows?

    SELECT * FROM LogisticsAddressCountryRegionTranslation(LogisticsAddressCountryRegionTranslation_1) WHERE ((LanguageId = N'EN-US')) JOIN * FROM Currency(Currency_1) JOIN * FROM LogisticsAddressCountryRegion(LogisticsAddressCountryRegion_1) ON Currency.CurrencyCode = LogisticsAddressCountryRegion.CurrencyCode

    In your code you used the adding of data sources incorrectly. Dependent data sources need to be added as child to the already added qbds:

    static void Job1(Args _args)
{
    Query query;
    QueryRun queryRun;
    QueryBuildDataSource dataSource;
    QueryBuildDataSource dataSource2;
    QueryBuildDataSource dataSource3;
    str textDesc = "";
 
    query = new Query();
    dataSource = query.addDataSource(tableNum(LogisticsAddressCountryRegionTranslation));
    dataSource2 = dataSource.addDataSource(tableNum(Currency));
    dataSource2.relations(true);
    dataSource3 = dataSource2.addDataSource(tableNum(LogisticsAddressCountryRegion));
    dataSource3.relations(true);
    SysQuery::findOrCreateRange(dataSource, fieldNum(LogisticsAddressCountryRegionTranslation, LanguageId)).value("EN-US");
 
    if (textDesc != "")
    {
        SysQuery::findOrCreateRange(dataSource, fieldNum(LogisticsAddressCountryRegionTranslation, ShortName)).value(SysQuery::valueLike(textDesc));
    }

    info(query.dataSourceNo(1).toString());
    queryRun = new QueryRun(query);
}


  • Community Member Profile Picture
    Community Member on at

    Thanks, I really appreciate the time you spent trying to help.

    This is my current "working?" code:

    QueryBuildDataSource dataSource;
    QueryBuildDataSource dataSource2;
    QueryBuildDataSource dataSource3;
    str textDesc = this.getArgs().parmDescription();

    query = new Query();
    dataSource = query.addDataSource(tableNum(LogisticsAddressCountryRegionTranslation), "LogisticsAddressCountryRegionTranslation");
    
    SysQuery::findOrCreateRange(
        dataSource, fieldNum(LogisticsAddressCountryRegionTranslation, LanguageId)
    ).value(
        "EN-US"
    );

    if (textDesc != "")
    {
        SysQuery::findOrCreateRange(
            dataSource, fieldNum(LogisticsAddressCountryRegionTranslation, ShortName)
        ).value(
            SysQuery::valueLike(textDesc)
        );
    }
    
    dataSource.joinMode(JoinMode::InnerJoin);
    dataSource.relations(false);
    
    dataSource = dataSource.addDataSource(tableNum(LogisticsAddressCountryRegion), "LogisticsAddressCountryRegion");
    dataSource.addLink(fieldNum(LogisticsAddressCountryRegionTranslation, CountryRegionId), 
        fieldNum(LogisticsAddressCountryRegion, CountryRegionId));
    
    dataSource = dataSource.addDataSource(tableNum(Currency), "Currency");    
    dataSource.addLink(fieldNum(LogisticsAddressCountryRegion, CurrencyCode), 
        fieldNum(Currency, CurrencyCode));

    queryRun = new QueryRun(query);


    Now at C#, the response I got from QueryService was the one I said at the previous post, a DataSet (as usual) with 3, not 1 table, as you'd expect if you would've ran a SQL query using JOIN. The way the result is given to me at C# with 3 tables, not 1, I can only assume with the result. I have to iterate the rows and check for every single table to be able to transform that result into my C# object. I don't feel confident doing that as I already said in my previous post. I have to trust Dynamics gave me the correct results in order for each separate table! I think you must agree with me on that.

    That way what I do today (after you helped me with the relations issue) is this:

    int rowCount = dataSet.Tables[0].Rows.Count;

for (int i = 0; i < rowCount; i++)
{
    check Table1.Rows[i]
    check Table2.Rows[i]
    check Table3.Rows[i]
    create C# POCO object
}

return List of C# POCO objects.

    So this is my problem. I don't even know if it can be done (joint results being returned as one single table to C#), but I think I'd at least try, right :) I hope I was clear, if not please let me know.

  • Suggested answer
    Ivan (Vanya) Kashperuk Profile Picture
    Ivan (Vanya) Kashperuk on at

    That seems about right to me.

    I don't think you can return them as one large "row", because these are 3 different tables

    In X++, you would also be doing something like:

    while (queryRun.next())

    {

    queryRun.getNo(1) = 1st table

    queryRun.getNo(2) = 2nd table, etc.

    }

    I think you can safely trust that Table1, Table2 and Table3 will return the right record when you access a row with a particular index.

  • Community Member Profile Picture
    Community Member on at

    Ok then! Again, thanks for your help. I'm going to keep it that way, then.

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…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

#1
Martin Dráb Profile Picture

Martin Dráb 4 Most Valuable Professional

#1
Priya_K Profile Picture

Priya_K 4

#3
MyDynamicsNAV Profile Picture

MyDynamicsNAV 2

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans