Sunday, 3 June 2012

View computed column – switch statement [AX 2012]

I was trying to write a switch statement for a computed column recently. Below is the code I used in the end.
public static server str StartDate()
{
    tableName   viewName                    = identifierStr(MyView);
    Map         comparisonExpressionMap     = SysComputedColumn::comparisionExpressionMap();
    str         fieldNameActualStartDate    = SysComputedColumn::returnField(viewName,identifierStr(MyDataSource_1),fieldStr(EAMWorkOrderLine,ActualStartDate));
    str         fieldNamePlannedStartDate   = SysComputedColumn::returnField(viewName,identifierStr(MyDataSource_1),fieldStr(EAMWorkOrderLine,PlannedStartDate));
    str         fieldNameProjectedStartDate = SysComputedColumn::returnField(viewName,identifierStr(MyDataSource_1),fieldStr(EAMWorkOrderLine,ProjectedStartDate));

    comparisonExpressionMap.insert(
        SysComputedColumn::notEqualExpression(
            SysComputedColumn::comparisonField(viewName, identifierStr(MyDataSource_1), fieldStr(MyTable, ActualStartDate)),
            SysComputedColumn::comparisonLiteral(DateTimeUtil::minValue())),
        fieldNameActualStartDate);

    comparisonExpressionMap.insert(
        SysComputedColumn::notEqualExpression(
            SysComputedColumn::comparisonField(viewName, identifierStr(MyDataSource_1), fieldStr(MyTable, PlannedStartDate)),
            SysComputedColumn::comparisonLiteral(DateTimeUtil::minValue())),
        fieldNamePlannedStartDate);


    comparisonExpressionMap.insert(
        SysComputedColumn::notEqualExpression(
            SysComputedColumn::comparisonField(viewName, identifierStr(MyDataSource_1), fieldStr(MyTable, ProjectedStartDate)),
            SysComputedColumn::comparisonLiteral(DateTimeUtil::minValue())),
        fieldNameProjectedStartDate);

    return SysComputedColumn::switch(
        '',
        comparisonExpressionMap,
        SysComputedColumn::comparisonLiteral(DateTimeUtil::minValue()));
}

 
It translates to SQL
 
CAST((CASE 
            WHEN (T1.ACTUALSTARTDATE) != ('1900-01-01T00:00:00') 
                THEN T1.ACTUALSTARTDATE 
            WHEN (T1.PLANNEDSTARTDATE) != ('1900-01-01T00:00:00') 
                THEN T1.PLANNEDSTARTDATE 
            WHEN (T1.PROJECTEDSTARTDATE) != ('1900-01-01T00:00:00') 
                THEN T1.PROJECTEDSTARTDATE 
            ELSE '1900-01-01T00:00:00' 
          END) AS DATETIME) 
   AS STARTDATE

1 comment:

Blogger said...

BlueHost is ultimately the best web-hosting company with plans for all of your hosting needs.