SQL improvements in the next version of Dynamics AX

Ordering and grouping Data

Previously, the order by and group by clauses acted on the table in the current join or select clause. So, the following

CustTable ct;
CustTrans transactions;

 select  *  from ct  order by AccountNum
 join transactions  order by AmountCur
 where ct.AccountNum == transactions.AccountNum;

would order by the account number, then on the amount. Note that the order by syntax doesn't include the table to which the field belongs, because it is implicit in the order by clauses position. This way of doing things works fine so long as you do not have requirements to order in a different order than what is mandated by the join structure. For instance, if you wanted to order by amount and then by accountnum you'd have to reverse the select/join hierarchy. To get rid of these problems, we have introduced a way of specifying an order by (and a group by) list that is not tied to a particular place in the join hierarchy. This is done by specifying a list of qualified names of fields in a single order by or group by clause:

 select *  from ct  join transactions  order by transactions.AmountCur, ct.AccountNum
 where ct.AccountNum == transactions.AccountNum;

There can be only one such augmented order by clause in a selection.


Improvements to the update statement

The update statement may now have a join clause attached to it, as shown in the code snippet below:

 // Three Table Join

  update_recordset salestable setting currencycode = 'USD' join custtable
  where  custtable.accountnum = salesTable.custaccount   join custgroup
  where custgroup.name == 'Retail' && custgroup.custgroup == custTable.custgroup;

We have also added some diagnostics that will cause the compiler to complain if the user tries to use the value of fields coming from tables that are given in exists and non exists joins (The database backend is not required to fetch values for these, merely to find out if there are matching records or not).

The Insert statement.

The insert statement is generally used to insert data from one table into another. The programmer typically writes a selection of fields matching the fields to insert into the target table and delimits the data by using suitable where and join clauses:

 insert_recordset MyTargetTable(targetField1, targetField2)  select sourceField1, sourceField2 from MySourceTable where ...

This is indeed suitable for the case where all the data to be inserted into the target table is already stored in the source table, but that is not always the case. The new feature allows the user to insert values that are given by variable references, not field references.