X++, ANSI SQL Comparison: SQL Select

Applies To: Microsoft Dynamics AX 2012 R3, Microsoft Dynamics AX 2012 R2, Microsoft Dynamics AX 2012 Feature Pack, Microsoft Dynamics AX 2012

In X++, the SQL select statement syntax differs from the American National Standards Institute (ANSI) specification.

Single Table Select

The following table lists differences between the select statements of X++ SQL and ANSI SQL.

Feature	X++ SQL	ANSI SQL	Comments
Table name on the from clause.	The from clause lists a record buffer instance that is declared from a table, such as from the CustTable table.	The from clause lists a table name, not the name of a buffer.	The record buffer has all the methods that the xRecordclass has in X++.
Syntax sequence of the order by versus where clauses.	The order by clause must appear before the where clause. The order by clause must appear after the from or join clause. The group by clause must follow the same syntax positioning rules that the order by follows.	The order by clause must appear after the where clause. The where clause must appear after the from or join clause.	In both X++ and ANSI SQL, the from and join clauses must appear before the order by and where clauses.
Condition negation.	The exclamation mark ('!') is used for negation.	The not keyword is used for negation.	X++ does not support the syntax !like. Instead, you must apply the ! operator to a clause.
Wildcard characters for the like operator.	0 to many – Asterisk ('*') Exactly 1 – Question mark ('?')	0 to many – Percent sign ('%') Exactly 1 – Underbar ('_')	For more information, see Relational Operators.
Logical operators in the where clause.	And – && Or – ||	And – and Or – or	For more information, see Relational Operators.

Code Example

The following code example illustrates features in the previous table.

    static void OByWhere452Job(Args _args)
    {
        // Declare the table buffer variable.
        CustTable tCustTable;
        ;
        while
        SELECT * from tCustTable
            order by tCustTable.AccountNum desc
            where (!(tCustTable.Name like '*i*i*') && tCustTable.Name like 'T?e *')
        {
            info(tCustTable.AccountNum + " , " + tCustTable.Name);
        }
    }
    /*** InfoLog output
    Message (04:02:29 pm)
    4010 , The Lamp Shop
    4008 , The Warehouse
    4001 , The Bulb
    ***/

X++ SQL Keywords

The following X++ SQL keywords are among those that are not part of ANSI SQL:

• crosscompany

• firstonly100

• forceliterals

• forcenestedloop

• forceplaceholders

• forceselectorder

• validtimestate

Join Clause

The following table lists differences about the join keyword of X++ SQL and ANSI SQL.

Feature	X++ SQL	ANSI SQL	Comments
Columns list.	The columns in the columns list must all come from the table listed in the from clause, and not from any table in a join clause. Columns in the list cannot be qualified by their table name.	The columns in the columns list can come from any table in the from or join clauses. It helps others to maintain your code when you qualify the columns in the list with their table name.	For more information, see Select Statements on Fields.
Join clause syntax.	The join clause follows the where clause.	The join clause follows a table in the from clause.	In the X++ code example, the join criteria is an equality of SalesPoolId values.
Inner keyword.	The default join mode is inner join. There is no inner keyword.	The default join mode is inner join. The inner keyword is available to make the code explicit.	The outer keyword exists in both X++ SQL and ANSI SQL.
Left and right keywords.	The left and right keywords are not available. All joins are left.	The left and right keywords are available to modify the join keyword.	No comments.
Equality operator.	The double equal sign operator ('==') is used to test for the equality of two values.	The single equal sign operator ('=') is used to test for the equality of two values.	No comments.

Code Example

The following code example illustrates the join syntax in X++ SQL.

    static void OByWhere453Job(Args _args)
    {
        // Declare table buffer variables.
        CustTable tCustTable;
        SalesPool tSalesPool;
        ;
        while
        SELECT
                // Not allowed to qualify by table buffer.
                // These fields must be from the table
                // in the from clause.
                AccountNum,
                Name
            from tCustTable
                order by tCustTable.AccountNum desc
                where (tCustTable.Name like 'The *')
            join tSalesPool
                where tCustTable.SalesPoolId == tSalesPool.SalesPoolId
        {
            info(tCustTable.AccountNum + " , " + tCustTable.Name);
        }
    }

Aggregate Fields

The following table lists some differences in how aggregate fields in the select column list are referenced between X++ SQL and ANSI SQL. Aggregate fields are those that are derived by functions such as sum or avg.

Feature	X++ SQL	ANSI SQL	Comments
Aggregate field name alias.	The aggregate value is in the field that was aggregated.	You can use the as keyword to tag an aggregate field with a name alias. The alias can be referenced in subsequent code.	For more information, see Aggregate Functions: Differences Between X++ and SQL

Code Example

In the following code example, the call to the info method illustrates the way to reference aggregate fields (see tPurchLine.QtyOrdered).

    static void Null673Job(Args _args)
    {
        PurchLine tPurchLine;
        ;
        while
        select
            // This aggregate field cannot be assigned an alias name.
            sum(QtyOrdered)
            from tPurchLine
        {
            info(
                // QtyOrdered is used to reference the sum.
                "QtyOrdered:  " + num2str(tPurchLine.QtyOrdered, 
                3,  // Minimum number of output characters.
                2,  // Required number of decimal places in the output.
                1,  // '.'  Separator to mark the start of the decimal places.
                2   // ','  The thousands separator.
                ));
        }
        info("End.");
    }
    /***
    Message (12:23:08 pm)
    QtyOrdered:  261,550.00
    End.
    ***/

Other Differences

The following table lists other differences of the select statement between the X++ SQL and ANSI SQL.

Feature	X++ SQL	ANSI SQL	Comments
The having keyword.	There is no having keyword.	The having keyword enables you to specify filter criteria for rows that are generated by the group by clause.	No comments.
Null results.	In a while select statement, if the where clause filters out all rows, no special count row is returned to report that.	In a select, if the where clause filters out all rows, a special count row is returned. The count value is 0.	No comments.
Cursors for navigating returned rows.	The while select statement provides cursor functionality. The alternative is to use the next keyword.	You can declare a cursor for looping through the rows that are returned from a select statement.	For more information, see Methods in X++.
From clause.	The from keyword is optional when no columns are listed and only one table is referenced. The following two syntax options are equivalent: select * from tCustTable; select tCustTable;	A select statement cannot read from a table unless the from clause is used.	In X++ SQL, the simple select statement fills the table buffer variable with the first row that was returned. This is illustrated by the following code fragment: select * from tCustTable; info(tCustTable.Name);

See also

X++, C# Comparisons

Announcements: New book: "Inside Microsoft Dynamics AX 2012 R3" now available. Get your copy at the MS Press Store.