Issues concerning X++: SQL improvements in the next version of Dynamics AX

[Blog bot]

Источник: http://blogs.msdn.com/x/archive/2007...t-version.aspx
==============

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.
How does this feature work?

Consider the case where a company has decided to make it mandatory for all customers in Italy to have a credit limit. Isaac would write:

1: Boolean mandatory = true;
2: CustTable custTable;
3: insert_recordset CustTable (MandatoryCreditLimit)
4:     select mandatory from CustTable where CustTable.Countryname == "Italy";

Note that the value to be inserted is specified in the form of a variable reference, mandatory in casu. The non-field values to be inserted must be variables, not expressions.

Источник: http://blogs.msdn.com/x/archive/2007...t-version.aspx