Tuesday, March 23, 2010

Union queries

In AX 2009 a great enhancement was introduced to the queries – support for union operation was added. A new property was added to the AOT queries – QueryType. It has two values:
  • Join – regular query

  • Union – union query

In the case of union query several datasources of the same type (the same table) can be placed on the query datasource root node. Note, that all datasources except the first one will have a property called UnionType, which specifies what should be done with duplicated records that may appear because of union:
  • Union – remove duplicated records

  • UnionAll – keep duplicated records

Additional datasources can be added to the deeper levels of the query under any datasource that participates in union. Those will be translated into joins. The only allowed join types for the union query are exists and notexists join.

17 comments:

  1. Could you post an example where this is done using QueryRun (aka programmatic)

    ReplyDelete
  2. Sure. Something like this:

    Query query = new Query();
    QueryRun queryRun;
    ;

    query.queryType(QueryType::Union);
    query.addDataSource(tablenum(InventTable), identifierstr(InventTable_1));
    query.addDataSource(tablenum(InventTable), identifierstr(InventTable_2), UnionType::UnionAll);

    queryRun = new QueryRun(query);

    while (queryRun.next())
    {
    info(int642str(queryRun.get(tablenum(InventTable)).RecId));
    }

    ReplyDelete
  3. This I have to try. Thanks! :-)

    ReplyDelete
  4. I'm a little confused by the JoinMode here. Will exists be translated into inner joins and NotExists be translated to Outerjoins?

    ReplyDelete
  5. this will not work if you need to group on any of the nested table fields, like you would if ie the third nested table contains aggregated fields.

    ReplyDelete
  6. Reg JoinMode - joins in union queries can serve only as filters, not for the fetching additional data from related table. That's why only exists and notexists joins are supported.
    The same reason explains why group by doesn't work on the deep levels.
    To be able to implement union on several joined or to group by you can create view first that will contain fields from several tables and then use this view in the union query.

    ReplyDelete
  7. I spent a day digging in this, and found out exactly that; I needed to make separate views and even in my case I needed to make a wrapping view combining the inner views. A pretty tedious task, but thats what you get when the database is abstracted the way it is. ;-)

    ReplyDelete
  8. Not to mention AX 2009 also added Group By functionality. I'm sitting here in AX 4.0 without grouping options, what a pain!

    ReplyDelete
  9. 4.0 also has group by functionality .see order mode on query.. anyone succeded in union between 2 different tables?

    regards
    Kim

    ReplyDelete