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.