CODEWITHSUNDEEP
sql-serverjoinaxaptamicrosoft-dynamics
Bongo
Bongo

Reputation: 3153

Microsoft Dynamics AX SQL JOIN Translation

I was wondering how Axapta translates its SQL Statements and came across this: how-joins-in-x-select-statement-are-translated-into-t-sql

This is the first example given on the site.

JOIN in X++:

select AccountNum from custTable
    join TaxGroupId from custGroup
    where custGroup.CustGroup == custTable.CustGroup;

CROSS JOIN in T-SQL:

SELECT T1.ACCOUNTNUM, T1.RECID, T2.TAXGROUPID, T2.RECID
FROM CUSTTABLE T1 CROSS JOIN CUSTGROUP T2
WHERE ((T1.PARTITION=?) AND (T1.DATAAREAID=?))
  AND (((T2.PARTITION=?) AND (T2.DATAAREAID=?)) 
   AND (T2.CUSTGROUP=T1.CUSTGROUP))

I was now wondering what the question mark means in this statement. The ? is marked as error because of wrong syntax.

What does WHERE ((T1.PARTITION=?) AND (T1.DATAAREAID=?)) mean ?

Upvotes: 0

Views: 535

Answers (2)

FH-Inway
FH-Inway

Reputation: 5107

The question marks are placeholders to enable the SQL server to compute execution plans for queries where the search values change often.

Microsoft Dynamics AX may pass either parameters (placeholders) or literals (actual values) in queries.

• Parameters allow Microsoft Dynamics AX and the database server to reuse the query when search values change. They are preferred for high-frequency queries.

• Literals allow the database server to optimize the query for a specific piece of information. This provides an optimal query for that piece of information, but the database server must perform the optimization for every query executed. Literals may be used for long running queries such as complex joins.

A developer can override the default use of literals by specifying parameters in their code, or an administrator can override the use of literals in the Server Configuration Utility.

Upvotes: 1

Maxim Lazarev
Maxim Lazarev

Reputation: 1254

These fields are part of Data Isolation implementation in Dynamics AX. More info here

Field T1.PARTITION relates to Partitions:

Partitions divide and isolate the business data of an installation by using special processing that the AOS applies to data queries. This special processing occurs immediately before the queries are sent to the underlying Microsoft SQL Server database when a system field named Partition is present in a queried table.

Field T1.DATAAREAID relates to company or legal entity:

Each partition contains at least one company or legal entity. A legal entity occurs in only one partition. When you create a legal entity, the system assigns it to the current partition. The legal entity can never be moved to another partition. However, its data can be exported from the partition and then imported to another company in another partition.

The question mark would be eventually substituted by the actual values of Partitions and Companies, depending of the context the statement was called from.

Upvotes: 1

Related Questions