Wednesday, September 19, 2012

Select statement where condition formatting and placement in Dynamics AX

In Dynamics AX X++, there are a few ways to format a select statement and place the where clauses when using multiple joins. See the two examples below. Both do exactly the same thing but note the difference in placement of the where conditions. Just making sure people know this. Apparently some people do not so at least now its out on the internet somewhere.

I don't believe there is a best practice around this but it should be consistent in your development either way. I prefer option 1 as it reads better in my opinion. The where conditions can be separated a little more and linked back to their initial data source.


OPTION 1:
public static Name storeOperatingUnitName(RecID _storeRecId)
{

    RetailChannelTable      retailChannelTable;
    DirPartyTable           dirPartyTable;
    OMOperatingUnit         operatingUnit;
    ;

    select firstOnly recId from retailChannelTable
        where retailChannelTable.RecId == _storeRecId
        join recId from operatingUnit
            where operatingUnit.RecId               == retailChannelTable.OMOperatingUnitID
               && operatingUnit.OMOperatingUnitType == OMOperatingUnitType::RetailChannel
            join name from dirPartyTable
                where dirPartyTable.RecId == operatingUnit.RecId;                  

    return dirPartyTable.Name;
}


 OPTION 2:
public static Name storeOperatingUnitName(RecID _storeRecId)
{

    RetailChannelTable      retailChannelTable;
    DirPartyTable           dirPartyTable;
    OMOperatingUnit         operatingUnit;
    ;      
   
    select firstOnly RecId from retailChannelTable       
        join RecId from operatingUnit           
            join Name from dirPartyTable
                where retailChannelTable.RecId           == _storeRecId
                    && operatingUnit.RecId               == retailChannelTable.OMOperatingUnitID
                    && operatingUnit.OMOperatingUnitType == OMOperatingUnitType::RetailChannel
                    && dirPartyTable.RecId               == operatingUnit.RecId;

    return dirPartyTable.Name;
}

1 comment:

  1. Is there any performance differences in the two approaches?

    Having the where statements before the join conditions (option 1) compared to having all the where clauses at the end (option 2) is it more efficient or the translated SQL query and execution plan is the same?

    Regards

    ReplyDelete