SQL improvements in the next version of Dynamics AX

I'd like to share with you a description of some of the features that we've finished for the next release. The features described in this blog post are improvements and additions to the X++ data access statements. One deals with ordering and grouping data, another expands the possibilities of the update statement by allowing join clauses; The third feature involves allowing values (or more precisely, constants that are not field related) into the selection that is inserted into a table in an insert_recordset statement.

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

 1: CustTable ct; 
2: CustTrans transactions; 
3:  
4:  select  *  from ct  order by AccountNum  
5:  join transactions  order by AmountCur  
6:  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:

 1:  select *  from ct  
2:  join transactions  
3:  order by transactions.AmountCur, ct.AccountNum  
4:  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:

 1: // Three Table Join 
2: update_recordset salestable 
3: setting currencycode = 'USD' 
4: join custtable 
5: where  custtable.accountnum = salesTable.custaccount 
6: join custgroup 
7: 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:

 1: insert_recordset MyTargetTable(targetField1, targetField2) 
2:     select sourceField1, sourceField2 from MySourceTable where ... 
3:  

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.

Comments

• Anonymous
  September 10, 2007
  Will those sorting enhancements work on query object or still to hierarchy of the data sources needs to be changed to achieve first sorting by child table? Also example of insert mandatory limit does not make sense for me – I though insert_recordset performs row inserts and not updates? Regards, Vic

• Anonymous
  September 12, 2007
  The comment has been removed

• Anonymous
  November 28, 2007
  Regarding the "insert_recordset". Dynamics Ax 4 SP2 shuts down when trying to write to a temp table. Very anoying to say the least. Plus why is there no wild cards avalable when the two tables are identical in structure? (insert_recordset MyTable (*) select * from FromTable where ....) Hope to see a fix soon. L8r Days.