Code highlighting

Thursday, April 23, 2009

Be careful with join clauses when writing complex queries

Join clause evaluation is dependent on its position in the sql statement. In SQL Management Studio, if you try to specify the conditions incorrectly, you will receive the following error message:

SQLstatement

In X++, you will not receive any compilation errors, nor any runtime errors. In complex scenarios with a lot of queries, this might go unnoticed, and will be extremely hard to weed out at a later stage, when data inconsistencies crawl in.

Take, as an example, the following job. At first glance, the 2 methods look exactly the same, and it seems as if they should work just fine. But in reality, the second method will return incorrect results, because inventSum.InventDimId will be treated as a constant (empty string as the default value) and not as a table field used in the same select statement.

QueryBuild classes have a major advantage in this situation, as you cannot easily add join clauses (links) unless adding to the child (joined) queryBuildDataSource.

static void JoinClauseWarningJob(Args _args)
{
#define.ItemId("ESB-005")

void testCorrectJoin()
{
InventTable inventTable;
InventSum inventSum;
InventDim inventDim;

select inventTable
where inventTable.ItemId == #ItemId
join inventSum
where inventSum.ItemId == inventTable.ItemId
join inventDim
where inventDim.inventDimId == inventSum.InventDimId;

info(strfmt("Correct join where clause: %1", inventSum.AvailPhysical));
}

void testIncorrectJoin()
{
InventTable inventTable;
InventSum inventSum;
InventDim inventDim;

select inventTable
where inventTable.ItemId == #ItemId
join inventDim
where inventDim.inventDimId == inventSum.InventDimId
join inventSum
where inventSum.ItemId == inventTable.ItemId;

info(strfmt("Incorrect join where clause: %1", inventSum.AvailPhysical));
}
// Actually execute the code
testCorrectJoin();
testIncorrectJoin();
}

So, the suggestion is simple: Use QueryBuild classes (or AOT queries) whenever possible, and pay attention to the order of tables and join clauses in the select statements that you write.

5 comments:

  1. Waw ... thanks i ll try it.

    btw ... how we can know about array's length?

    for example in java we can know about it by (name of array).length

    thank u for ur reply.

    ReplyDelete
  2. A very good description that answers all your questions and more can be found on MSDN:
    http://msdn.microsoft.com/en-us/library/aa597117.aspx

    ReplyDelete
  3. Waw Vanya thanks for ur Info.

    What a surprise for me ... so there's kind of library at MSDN for AX?!
    coz now iam working as a junior programer for ax, n usually i used the Totorial AX. But its kind of little information that ive got from that tutorial.

    thanks ...

    ReplyDelete
  4. i have created a lookup on smmopportunity table where i have lookup of my custom fields from other table
    this is the code
    public void lookup(FormControl _formControl, str _filterStr)
    {
    //AccommodationTable AccommodationTable;
    SysTableLookup Lookup = SysTableLookup::newParameters(tableNum(AccommodationTable),_formControl);
    Query query = new Query();
    QueryBuildDataSource queryBuildDataSource ,qbr1;

    QueryBuildRange queryBuildRange;
    ;
    // Lookup.addLookupField(fieldNum(AccommodationTable,FacilityCode));
    Lookup.addLookupField(fieldNum(AccommodationTable,UnitNumber));
    Lookup.addLookupfield(fieldnum(AccommodationTable,AccommodationType));
    Lookup.addLookupfield(fieldnum(AccommodationTable,Room));
    queryBuildDataSource = query.addDataSource(tableNum(AccommodationTable));
    qbr1 = query.addDataSource(tablenum(Smmopportunitytable));
    querybuilddatasource.addRange(fieldnum(AccommodationTable,Facilitycode)).value(smmopportunityTable.RFacility);
    // queryBuildDataSource.addRange(fieldNum(AccommodationTable,HoldStatus)).value(this.equal(smmopportunitytable.OpportunityId));

    // qbr1.addRange(fieldnum(AccommodationTable,Holdstatus)).value(AccommodationTable.HOpportunityId);

    queryBuildDataSource.addRange(fieldNum(AccommodationTable,HoldStatus)).value(queryvalue(NOYes::No));// || queryvalue(NoYes::Yes));//&& queryvalue(smmOpportunityTable.OpportunityId)));
    queryBuildDataSource.addRange(fieldnum(AccommodationTable,ActiveInactive)).value(queryvalue(activeinactive::Active));
    queryBuildDataSource.addRange(fieldnum(AccommodationTable,CompleteIncomplete)).value(queryvalue(CompleteIncomplete::Complete));
    queryBuildDataSource.addRange(fieldNum(AccommodationTable,reservestatus)).value(queryvalue(NOYes::No)) ;

    Lookup.parmQuery(query); // Query to be executed
    Lookup.performFormLookup();


    }

    if i have hold for this opportunity i want to have that unit of that opportunity and all the units which are not on hold and not on reserve for that opportunity. but i am getting only the unhold ones and unreserved . i want to have the hold unit of that opportuniy in my lookup.


    luvchoc@rediffmail.com

    ReplyDelete

Please don't forget to leave your contact details if you expect a reply from me. Thank you