Показать сообщение отдельно
Старый 12.04.2021, 19:11   #1  
Blog bot is offline
Blog bot
Участник
 
25,459 / 846 (79) +++++++
Регистрация: 28.10.2006
Switch in View Computed column
Источник: http://alexvoy.blogspot.com/2021/04/...ed-column.html
==============

If you need to compare multiple fields in your view while populating a computed column, take into consideration that the standard implementation of method SysComputedColumn::switch uses a map enumerator.



public static client server str switch(str _controlExpression, Map _comparisonExpressionMap, str _defaultExpression)
{
MapEnumerator mapEnumerator;
str caseExpression = '';

caseExpression ='CASE ' + _controlExpression;
mapEnumerator = _comparisonExpressionMap.getEnumerator();
while (mapEnumerator.moveNext())
{
caseExpression += ' WHEN ' + mapEnumerator.currentKey() + ' THEN ' + mapEnumerator.currentValue();
}
caseExpression += ' ELSE ' + _defaultExpression;
caseExpression += ' END';

return caseExpression;
}



It means that your given order will be replaced in final SQL clause by alphabetical one of your keys.



CASE
WHEN (T4.PROJID1) != ('') THEN T4.PROJNAME1
WHEN (T4.PROJID2) != ('') THEN T4.PROJNAME2
WHEN (T4.PROJID3) != ('') THEN T4.PROJNAME3
ELSE T4.PROJNAME END



For example, we want to populate field mgcProjInvoiceGLTransView.ParentProjectName for a given project id up to three level up in the project hierarchy. Let's assume that they are prepopulated in mgcProjInvoiceOnAccView view: projId1 is the parent of projId, projId2 is the parent of projId1, and so on.

Once we reference this computed column to the following method, we will get a new order in CASE construction on the SQL side.



private static str projNameParent()
{
str ret;
SysComputedColumnBase::switch cannot keep a given order while using Map enumerator; so we put the SQL string as a string constant
tableName viewName = identifierStr(mgcProjInvoiceGLTransView);
tableName tableName = identifierStr(mgcProjInvoiceOnAccView);
str compareValue = '';


Map comparisonExpressionMap = SysComputedColumn::comparisionExpressionMap();
str fieldNameProjName3 = SysComputedColumn::returnField(viewName, tableName, fieldStr(mgcProjInvoiceOnAccView, ProjName3));
str fieldNameProjName2 = SysComputedColumn::returnField(viewName, tableName, fieldStr(mgcProjInvoiceOnAccView, ProjName2));
str fieldNameProjName1 = SysComputedColumn::returnField(viewName, tableName, fieldStr(mgcProjInvoiceOnAccView, ProjName1));
str fieldNameProjName = SysComputedColumn::returnField(viewName, tableName, fieldStr(mgcProjInvoiceOnAccView, ProjName));

comparisonExpressionMap.insert(
SysComputedColumn::notEqualExpression(
SysComputedColumn::comparisonField(viewName, tableName, fieldStr(mgcProjInvoiceOnAccView, ProjId3)),
SysComputedColumn::comparisonLiteral(compareValue)),
fieldNameProjName3);

comparisonExpressionMap.insert(
SysComputedColumn::notEqualExpression(
SysComputedColumn::comparisonField(viewName, tableName, fieldStr(mgcProjInvoiceOnAccView, ProjId2)),
SysComputedColumn::comparisonLiteral(compareValue)),
fieldNameProjName2);

comparisonExpressionMap.insert(
SysComputedColumn::notEqualExpression(
SysComputedColumn::comparisonField(viewName, tableName, fieldStr(mgcProjInvoiceOnAccView, ProjId1)),
SysComputedColumn::comparisonLiteral(compareValue)),
fieldNameProjName1);

ret = SysComputedColumn::switch(
'',
comparisonExpressionMap,
fieldNameProjName);
return ret;
}



Of course, it always returns the name of the parent on the first level, even though it had its own parent, which is incorrect result.

Therefore, the easiest way is to replace the aforementioned construction with a simple string.

private static str projNameParent()
{
str ret;
ret = "CASE WHEN (T4.PROJID3) != ('') THEN T4.PROJNAME3 WHEN (T4.PROJID2) != ('') THEN T4.PROJNAME2 WHEN (T4.PROJID1) != ('') THEN T4.PROJNAME1 ELSE T4.PROJNAME END";
return ret;
}





Источник: http://alexvoy.blogspot.com/2021/04/...ed-column.html
__________________
Расскажите о новых и интересных блогах по Microsoft Dynamics, напишите личное сообщение администратору.