Different ResultSet when using SQL and AOT Query object

This question is not answered

Hello,

I have weird problem when using direct SQL and AOT Query from X++ code in my AX 2009 Application.

My SQL is like below and I thing it returns rigth result but when using AOT Query object where Ranges and Relations are defined exaclty same as in pure SQL returns different count of rows. Can anyone give a hint why this occurs?

Br

Michael

// Pure SQL

    str dataarea = "tk";
    str accountnum = "4015";
    str moduletype = "2";
    str unitid = "kpl";
    
    str querySQL = strfmt(
        "SELECT InventTableModule.UnitId, custtable.AccountNum, custTable.Name, " +
            "custtable.dataareaid, " + 
	   "priceDiscTable.Percent1, priceDiscTable.Percent2, inventTable.Itemid, " +
            " inventTable.ItemName " +
        "FROM CustTable, PriceDiscTable, InventTable, InventTableModule where " +
        "CustTable.AccountNum = PriceDiscTable.AccountRelation and " +
        "PriceDiscTable.ItemRelation = InventTable.ItemId  and " +
        "InventTable.ItemId = InventTableModule.ItemId and " +
        "custtable.dataareaid = '%1' and " +
        "CustTable.AccountNum = '%2' and " +
        "InventTableModule.ModuleType = '%3' and " +
        "InventTableModule.UnitId = '%4'",
        dataarea, accountnum, moduletype, unitid);
    
    permission = new SqlStatementExecutePermission(querySQL);
    Con = new Connection();
    permission = new SqlStatementExecutePermission(querySQL);
    permission.assert();
    Stmt = Con.createStatement();
    R = Stmt.executeQuery(querySQL);

 

// Versus AOT Query object where tables are define hierarchial way with Ranges and Relations

CustTable
        PriceDiscTable
	         InventTable
	               InventTableModule 
Verified Answer
  • Imagine that you (inner) join table A (1 record) and table B (3 records). SQL Server returns three joined records where all fields from table A are repeated, because the one A record has been joined to each of B records. (In case of outer joins, some fields will have NULL values if no joined record exists.)

    AX presents that not as a single table with fields from A and B, it gives you objects for table A and table B with fixed schema. If you iterate through the query, table A will always contain the same values (because there is just one record), only values in B will change. If you had more A records, the A buffer would change after you iterated through joined B records (i.e. after 3rd iteration in our case). You may want to know when the buffer changes to trigger some logic but not to repeat it multiple times for the same buffer - that's what QueryRun.changed() is used for.

    Martin "Goshoom" Dráb | Freelancer | Goshoom.NET Dev Blog

All Replies
  • Please show us the query string generated from your AOT query definition. You can see it in AOT as tooltip of the root datasource or you can get in in code by calling toString() method on the root QueryBuildDataSource instance.

    Martin "Goshoom" Dráb | Freelancer | Goshoom.NET Dev Blog

  • Hello,

    SQL is like below

    SELECT * FROM CustTable OUTER JOIN * FROM PriceDiscTable WHERE

    CustTable.AccountNum = PriceDiscTable.AccountRelation OUTER JOIN * FROM

    InventTable WHERE PriceDiscTable.ItemRelation = InventTable.ItemId OUTER JOIN * FROM

    InventTableModule WHERE InventTable.ItemId = InventTableModule.ItemId AND ((ModuleType = 2)) AND ((UnitId = N'kpl'))

    I also use SysQueryForm with X++ code below.

    SysQuery::findOrCreateRange(queryBuildDataSource, fieldnum(CustTable, AccountNum));

    queryRun = new SysQueryRun(query);

    queryRun.prompt();

    When I watch criterias in SysQueryForm ModulType and UnitId are like in Query.

    Br

    Michael

  • Your statement used in executeQuery() doesn't include DataAreaId in join conditions, so you may get data from other companies (AX handles that for you). Also the outer joins may include data that would be filtered out by inner joins.

    Martin "Goshoom" Dráb | Freelancer | Goshoom.NET Dev Blog

  • Hello,

    In Pure SQL there is dataareaid but isn't it so, that AOT Query object looks that automatically or should it be placed in X++ code?

  • There is DataAreaId, but only as filter of CustTable. When you look for AccountNum references to PriceDiscTable, it looks for AccountNum across companies, because you didn't specify that the relation should be AccountNum+DataAreaId. The same problem exists with all your joins in the T-SQL query.

    Things would get even more compilated if you had any virtual companies there.

    Martin "Goshoom" Dráb | Freelancer | Goshoom.NET Dev Blog

  • Yes, you are right about that!

    First time when I started to wonder Queyry object  used in X++ code was situation  where it returned rows that contained data with empty values.

    So I found method QueryRun.changed which should be used in hierarchical queries.

    Is it really so that four level dataset AOT Query object should use this QueryRun.changed method when used in X++ code?

    Br

    Michael

  • Imagine that you (inner) join table A (1 record) and table B (3 records). SQL Server returns three joined records where all fields from table A are repeated, because the one A record has been joined to each of B records. (In case of outer joins, some fields will have NULL values if no joined record exists.)

    AX presents that not as a single table with fields from A and B, it gives you objects for table A and table B with fixed schema. If you iterate through the query, table A will always contain the same values (because there is just one record), only values in B will change. If you had more A records, the A buffer would change after you iterated through joined B records (i.e. after 3rd iteration in our case). You may want to know when the buffer changes to trigger some logic but not to repeat it multiple times for the same buffer - that's what QueryRun.changed() is used for.

    Martin "Goshoom" Dráb | Freelancer | Goshoom.NET Dev Blog

  • Okei, thanks for answering!

    Br

    Michael

  • Remember to mark the answer, Michael. ;-)

    Tommy Skaue | Dynamics AX Developer from Norway | http://yetanotherdynamicsaxblog.blogspot.no/ | www.axdata.no