Computed column for union values from multiple outer joined data sources in view

[Blog bot]

Источник: http://alexvoy.blogspot.com/2019/03/...lues-from.html
==============

Let's say you have a multiple outer joined data sources with similar fields, which can be merged by union to one target field.

PSAActualEntity data entity(in D365)can be a good example, if you add ProjCostTrans, ProjItemTrans, and ProjEmplTrans to its root data source table ProjTransPosting, which has appropriate relations to each of them.

All aforementioned have CategoryId field, which can be present in one table only at a time.

Let's place a new computed string field with the following static method with a nested if.


private static server str transCategoryId()
{
str sRet;
tableName viewName = identifierStr(avrPSAActualEntity);
str cCategoryProjCostTrans = SysComputedColumn::comparisonField(viewName,
identifierStr(ProjCostTrans),
fieldStr(ProjCostTrans, CategoryId));
str cCategoryProjItemTrans = SysComputedColumn::comparisonField(viewName,
identifierStr(ProjItemTrans),
fieldStr(ProjItemTrans, CategoryId));
str cCategoryProjEmplTrans = SysComputedColumn::comparisonField(viewName,
identifierStr(ProjEmplTrans),
fieldStr(ProjEmplTrans, CategoryId));
str sCategoryProjCostTrans = SysComputedColumn::returnField(viewName,
identifierStr(ProjCostTrans),
fieldStr(ProjCostTrans, CategoryId));
str sCategoryProjItemTrans = SysComputedColumn::returnField(viewName,
identifierStr(ProjItemTrans),
fieldStr(ProjItemTrans, CategoryId));
str sCategoryProjEmplTrans = SysComputedColumn::returnField(viewName,
identifierStr(ProjEmplTrans),
fieldStr(ProjEmplTrans, CategoryId));
sRet =
SysComputedColumn::if(SysComputedColumn::isNotNullExpression(cCategoryProjCostTrans),
cCategoryProjCostTrans,
SysComputedColumn::if(SysComputedColumn::isNotNullExpression(cCategoryProjItemTrans),
sCategoryProjItemTrans,
SysComputedColumn::if(SysComputedColumn::isNotNullExpression(cCategoryProjEmplTrans),
sCategoryProjEmplTrans,
SysComputedColumn::returnLiteral('')
)
)
);

return sRet;
}

Источник: http://alexvoy.blogspot.com/2019/03/...lues-from.html