FirstOnly property work and influence SQL Server query? It seems obvious, but in fact it is not.FirstOnly property.FirstOnly property is a hint for database that only one record is required. Let's run several tests to analyse how it actually works. You can also jump to the conclusion section.
FetchMode property sometimes was a magic solution for report development in Axapta 3.0. Since then I wanted to run several tests and describe the feature.FetchMode property.FetchMode property is available on an embedded datasource and determines a relation between parent and child datasources.FetchMode values are 1:1 and 1:n, but there is no description of them.
LogisticsPostalAddress table. Address Primary setting is defined in DirPartyLocation.IsPrimary and is duplicated in DirPartyTable.PrimaryAddressLocation.notexists join and crossCompany keyword to find missing records. Such approach has a problem, which is described in the previous post.
while select crossCompany text in methods. Continue reading for the solution.InventItemGroup table in company USMF must be created in company USRT (provided the records are missing in USRT company). It seems to be possible to use cross-company query like this:InventItemGroup inventItemGroup, inventItemGroupJoin;
while select crossCompany inventItemGroup
where inventItemGroup.dataAreaId == 'USMF'
notExists join inventItemGroupJoin
where inventItemGroupJoin.dataAreaId == 'USRT'
&& inventItemGroupJoin.ItemGroupId == inventItemGroup.ItemGroupId
{
// create new record in USRT company
}
|
static void CrossCompanyTest(Args _args) { InventItemGroup inventItemGroup, inventItemGroupJoin; select generateonly forceLiterals crossCompany ItemGroupId from inventItemGroup where inventItemGroup.dataAreaId == 'USMF' notExists join inventItemGroupJoin where inventItemGroupJoin.dataAreaId == 'USRT' && inventItemGroupJoin.ItemGroupId == inventItemGroup.ItemGroupId; info(inventItemGroup.getSQLStatement()); } |
Query class instead of select statement.InventBatch table what exactly is added and why.static void Test_UniqueIndex_SQL(Args _args) { InventBatch inventBatch; select generateonly description from inventBatch; info (inventBatch.getSQLStatement()); } |
static void Test_101090_SQL(Args _args) { InventBatch inventBatch; select generateonly description from inventBatch; info (inventBatch.getSQLStatement()); } |
exists join in X++. Let's analyze how joins in select statement in X++ are translated into T-SQL statement sent to SQL Server.ValidFrom field default value set by kernel when a new record is createdValidFrom and ValidTo ranges added by kernel into a select statement.ValidTimeStateFieldType property set to Date and UtcDateTime. Try to change session date and time, client machine date and time, user time zone and compare default values of new record in different cases.Dynamic property on query datasource Fields. I made simplified and theoretical example to demonstrate problem and solution.Fields node on query datasource has no field and Dynamic property is set to No.
static void CustTableBankAccountCountry(Args _args) { Query q = new Query(queryStr(CustTableBankAccountCountry)); QueryRun qr; info (q.dataSourceNo(1).toString()); qr = new QueryRun(q); qr.next(); } |
SP:SmtCompleted event and filter results by CustTable (I plan to write a separate post on SQL Server Profiler hints for Dynamics AX tracing)
SELECT AccountNum FROM CustTable(CustTable) OUTER JOIN FROM CustBankAccount(CustBankAccount) ON CustTable.AccountNum = CustBankAccount.CustAccount OUTER JOIN CountryRegionId FROM LogisticsPostalAddress(LogisticsPostalAddress) ON CustBankAccount.Location = LogisticsPostalAddress.Location |
SELECT T1.ACCOUNTNUM,T1.RECID, T2.ACCOUNTID,T2.CUSTDOMNUMBER_BE,T2.NAME,T2.ACCOUNTNUM,T2.BANKCIN, T2.BANKACCOUNTTYPE,T2.LOCATION,T2.CURRENCYCODE,T2.REGISTRATIONNUM, T2.PHONE,T2.TELEFAX,T2.CONTACTPERSON,T2.EMAIL,T2.URL,T2.TELEX, T2.CELLULARPHONE,T2.PHONELOCAL,T2.CUSTACCOUNT,T2.BANKGROUPID, T2.CUSTPAYMENTTEXTCODE,T2.SWIFTNO,T2.MSGTOBANK,T2.EXCHRATE, T2.EXCHRATEREF,T2.BANKCONTRACTACCOUNT,T2.PAGER,T2.SMS,T2.BANKCODETYPE, T2.BANKIBAN,T2.CORRESPONDENTBANKNAME_LT,T2.CORRESPONDENTBANKADDRESS_LT, T2.CORRESPONDENTBANKSWIFT_LT,T2.INTERMEDIATEBANKNAME_LT, T2.INTERMEDIATEBANKADDRESS_LT,T2.INTERMEDIATEBANKSWIFT_LT, T2.CORRESPONDENTBANKACCOUNT_LT,T2.INTERMEDIATEBANKACCOUNT_LT, T2.FOREIGNBANK_RU,T2.FOREIGNACCOUNT_RU,T2.FOREIGNSWIFT_RU, T2.BANKSORTCODE_RU,T2.CORRACCOUNT_W,T2.LVDEFAULTBANK,T2.BANKCONSTANTSYMBOL, T2.SPECIFICSYMBOL,T2.RECVERSION,T2.PARTITION,T2.RECID, T3.COUNTRYREGIONID,T3.RECID FROM CUSTTABLE T1 LEFT OUTER JOIN CUSTBANKACCOUNT T2 ON (((T2.PARTITION=@P1) AND (T2.DATAAREAID=@P2)) AND (T1.ACCOUNTNUM=T2.CUSTACCOUNT)) LEFT OUTER JOIN LOGISTICSPOSTALADDRESS T3 ON ((T3.PARTITION=@P3) AND ((T2.LOCATION=T3.LOCATION) AND ((T3.VALIDFROM<=@P4) AND (T3.VALIDTO>=@P5)))) WHERE ((T1.PARTITION=@P6) AND (T1.DATAAREAID=@P7)) ORDER BY T1.ACCOUNTNUM |
CustBankAccount table based on query definition. Query statement in AX proves it, but actual SQL statement selects and returns all fields from CustBankAccount. As no field is actually required from CustBankAccount table, such useless select should be omitted.Fields node.
CreateNewTimePeriod update mode lead me to the question - how does it actually work?CreateNewTimePeriod update mode?DirPartyTable forminsert and update methods of LogisticsPostalAddress table.
DatabaseLog table for CustTable before and after step 2.