donderdag 26 mei 2011

Joins: InnerJoin / OuterJoin combined ?

SELECT STATEMENT
Consider this statement:

while
select  *
from    prodTable
    where prodTable.ADUProdClusterId == "CL00000001"
join    *
from    clusterTable
    where clusterTable.ClusterId == prodTable.ADUProdClusterId
outer join *
from    prodTableRef
    where prodTableRef.ProdId == prodTable.CollectRefProdId

It results in an error:

Cannot select a record in Works orders (ProdTable).
The join does not contain any link between the joined tables in the WHERE clause.

The cause is that we use a combination of inner join and outer join on the same source (prodTable)
Axapta doesn’t seem to be able to handle this.

The solution is to use all inner or all outer joins.
In our case, we changed the inner join to an outer join, and the query ran fine.

QUERY OBJECT
We also tried to replicate this query in a Query object.
(click pictures to enlarge)

image

ProdTable properties:
image

ADUProdClusterTable properties:
image

ProdTableRef properties:
image

We found that AX ignores the inner join when combined with an outer join (ProdTableRef enabled)
When we disabled ProdTableRef, effectively making this an inner join between just 2 tables, AX took the join into account correctly.

See the queries AX builds when executing through the Query object:

ProdTableRef ENABLED
ProdTable:           SELECT * FROM ProdTable
                     WHERE ((ADUProdClusterId = N'CL00000001'))
ADUProdClusterTable: SELECT * FROM ADUProdClusterTable
                     WHERE ProdTable.ADUProdClusterId = ADUProdClusterTable.ClusterId
ProdTableRef:        SELECT *
                     FROM ProdTable
                     WHERE ProdTable.CollectRefProdId = ProdTable.ProdId

ProdTableRef DISABLED
ProdTable:           SELECT * FROM ProdTable 
                     WHERE ((ADUProdClusterId = N'CL00000001'))
                     JOIN * FROM ADUProdClusterTable 
                     WHERE ProdTable.ADUProdClusterId = ADUProdClusterTable.ClusterId
ADUProdClusterTable: SELECT * FROM ADUProdClusterTable 
                     WHERE ProdTable.ADUProdClusterId = ADUProdClusterTable.ClusterId
ProdTableRef:        (ignored)

Changing the ADUProdClusterTable JoinMode property to OuterJoin doesn’t seem to make a difference:

ProdTableRef ENABLED
ProdTable:           SELECT * FROM ProdTable
                     WHERE ((ADUProdClusterId = N'CL00000001'))
ADUProdClusterTable: SELECT * FROM ADUProdClusterTable
                     WHERE ProdTable.ADUProdClusterId = ADUProdClusterTable.ClusterId
ProdTableRef:        SELECT *
                     FROM ProdTable
                     WHERE ProdTable.CollectRefProdId = ProdTable.ProdId

ProdTableRef DISABLED
ProdTable:           SELECT * FROM ProdTable 
                     WHERE ((ADUProdClusterId = N'CL00000001'))
                     OUTER JOIN * FROM ADUProdClusterTable 
                     WHERE ProdTable.ADUProdClusterId = ADUProdClusterTable.ClusterId
ADUProdClusterTable: SELECT * FROM ADUProdClusterTable 
                     WHERE ProdTable.ADUProdClusterId = ADUProdClusterTable.ClusterId
ProdTableRef:        (ignored)

CONCLUSION:

  1. The select statement in X++ will work as long as you don’t mix inner and outer joins.
  2. The select statement cannot be built with a Query object, since the resulting select statements are wrong,
    regardless of whether or not you mix inner and outer joins.

Form Splitters

See http://dynamicsaxgyan.wordpress.com/2011/03/29/splitters-in-dynamics-ax-x-sysformsplitter_y-sysformsplitter_ybottombound/

vrijdag 20 mei 2011

Convert Set to Container

Put this method in the Global class.

public static container set2Con(Set _set)
{;
    return condel(_set.pack(),1,3);
}

adding a DynaLink through code

example:

public void init() //on form datasource reqPO
{
    QueryBuildDatasource    QBDSReqPO;
    ;

    super();

    QBDSreqPO = this.query().dataSourceTable(tablenum(reqPO));
    QBDSreqPO.clearDynalinks();
    QBDSreqPO.addDynalink( fieldnum(reqPO,ADUcollectrefid), ADUQRMWipTable, fieldnum(ADUQRMWipTable,refid));
    QBDSreqPO.addRange(fieldnum(reqpo,reqplanid)).value(reqparameters::find().CurrentReqPlanIdSchedStatic);
}

woensdag 18 mei 2011

Form DataSource Query: show Select statements

Add these 2 methods to the Global Class:

public static void showFormQueries(FormRun _fr)
{
    Counter         dsCount = _fr.dataSourceCount();
    Counter         c;
    Name            frName = _fr.name();
    ;
    setPrefix(strfmt("@SYS76681", "@SYS6890", frName, strfmt("@SYS76498", dsCount, strlwr(dsCount==1?"@SYS56294":"@SYS26774"))));
    for (c=1; c <= dsCount; c++)
        showFormDataSourceQuery(_fr.dataSource(c));
}

public static void showFormDataSourceQuery(FormDataSource _fds)
{
    Name    fdsName     = _fds.name();
    Name    tableName   = tableId2Name(_fds.table());
    ;
    setPrefix(strfmt("%1%2", fdsName, (fdsName!=tableName)?strfmt(" (%1: %2)", "@SYS8868", tableName):""));
    info(_fds.query().dataSourceNo(1).toString());
}

 

As an example,  we can use it in the form SalesTable.run method:

void  run()
{
    int designatedTabNo = 1;
    ;

    if (!advanced)
    {
        designatedTabNo = 2;
    }
    else if (this.isCalledFromListPage())
    {
        designatedTabNo = 3;
    }

    SysListPageHelper::handleRunPreSuper(element, tabHeader, designatedTabNo, designatedTabNo);
    super();
    SysListPageHelper::handleRunPostSuper(element, tabHeader);
    global::aduShowFormQueries(element);
}

Which yields:

Form SalesTable (11 data sources)
SalesTable
    SELECT * FROM SalesTable USING INDEX SalesIdx WHERE ((NOT (ReturnStatus = 4) AND NOT (ReturnStatus = 1)))
SalesLine
    SELECT * FROM SalesLine USING INDEX SalesLineIdx WHERE ((((SalesLine.ReturnStatus != 1) || (SalesLine.ExpectedRetQty < 0)&&(SalesLine.ReturnStatus == 1)))) AND SalesTable.SalesId=SalesLine.SalesId JOIN * FROM InventDim WHERE SalesLine.InventDimId = InventDim.inventDimId
InventDim
    SELECT * FROM SalesLine USING INDEX SalesLineIdx WHERE ((((SalesLine.ReturnStatus != 1) || (SalesLine.ExpectedRetQty < 0)&&(SalesLine.ReturnStatus == 1)))) AND SalesTable.SalesId=SalesLine.SalesId JOIN * FROM InventDim WHERE SalesLine.InventDimId = InventDim.inventDimId
InterCompanyPurchSalesReference
    SELECT * FROM InterCompanyPurchSalesReference WHERE SalesTable.SalesId=InterCompanyPurchSalesReference.SalesId JOIN * FROM PurchTable WHERE InterCompanyPurchSalesReference.PurchId = PurchTable.PurchId
PurchTable_Reference (Table: PurchTable)
    SELECT * FROM InterCompanyPurchSalesReference WHERE SalesTable.SalesId=InterCompanyPurchSalesReference.SalesId JOIN * FROM PurchTable WHERE InterCompanyPurchSalesReference.PurchId = PurchTable.PurchId
SalesTable_Reference (Table: SalesTable)
    SELECT * FROM SalesTable WHERE ((SalesId = N'PO000024'))
ShipCarrierAddress (Table: Address)
    SELECT FIRSTONLY * FROM Address USING INDEX TypeIdx WHERE ((type = 11)) AND SalesTable.RecId=Address.AddrRecId AND SalesTable.TableId=Address.AddrTableId
ShipCarrierAddressInLine (Table: Address)
    SELECT FIRSTONLY * FROM Address USING INDEX TypeIdx WHERE ((type = 11)) AND SalesLine.RecId=Address.AddrRecId AND SalesLine.TableId=Address.AddrTableId
DPInventSum (Table: InventSum)

DPInventDim (Table: InventDim)

ADUPriceDiscDetail1 (Table: ADUPriceDiscDetail)
    SELECT * FROM ADUPriceDiscDetail WHERE SalesLine.ADUPriceDiscDetailId=ADUPriceDiscDetail.ADUPriceDiscDetailId

dinsdag 3 mei 2011

Relations and DeleteActions

Relations
To be placed on Child table, or defined on EDT.
Indicates a Foreign Key relation: Child table uses data defined in Master table

DeleteActions
To be placed on Master table.
Indicates what should happen to Child records when the Master record is deleted.
Restricted = don’t delete Master record if Child records still exist.
Cascade = delete Child records when Master record is deleted.