1-N links handled on data access layer

CRM does not perform linking on the database server for 1-N links and instead it treats each table in the query as a separate query then link the results in the data access layer of the CRM.Core. The workaround in this case is to modify the query statement as explained below:

UQL:


select (Company, Country, CountryCode, WR.BeginDate) from (FI)
plus (WR)
orderby (WR.BeginDate)

SQL

Note: In case of SQL, two separate queries are used and their results are combined together in the CRM.Core:


SELECT  TOP 101 Firma,Land,FTZ,Ftz1,Ftz2,ID FROM CRM_FI WHERE LosKZ=0


SELECT  TOP 101 Vertragsbeginn,ID,ID_FI,ID_PE FROM CRM_WR WHERE ID_FI=8589934592001 AND LosKZ=0 ORDER BY Vertragsbeginn

This method has some limitation and performance impact. One limitation is that the sorting process is not applied to the final result, which leads to unexpected outcome in some cases.

The workaround, in this case, is to modify the UQL query statement to be N-1. For example, in the above UQL, do the following changes:


select (Company, Country, CountryCode, WR.BeginDate) from (WR)
plus (FI)
orderby (WR.BeginDate)