Selecting data with noFetch

Sometimes you would like to do different where clauses or sortings in while select depending on the circumstances and no matter what execute the same piece of business logic inside the while select.

One way of doing that is creating a method and use that within the loop. Another approach could be to use the noFetch keyword in the SELECT statement. The result is that the query is prepared and ready to be executed but the actual data load is not performed at that point. To traverse through the records you use the NEXT keyword. Below is an example listing the customer groups. Since the NEXT only refers to the first record in the SELECT you could get the idea that it would give some problems when joining tables in the SELECT. However, AX handles that as you would expect and returns the full data set.

    CustGroup   custGroup;
    TaxGroupHeading taxGroupHeading;    
    while select custGroup
        where custGroup.CustGroup like 'ic*'
        join taxGroupHeading
            where taxGroupHeading.taxgroup == custGroup.TaxGroupId
    {
        info(custGroup.CustGroup + " - " + taxGroupHeading.TaxGroupName);
    }    
    info('---');
    select noFetch custGroup
        where custGroup.CustGroup like 'ic*'
        join taxGroupHeading
            where taxGroupHeading.taxgroup == custGroup.TaxGroupId;
    next custGroup;
    while (custGroup.RecId)
    {
        info(custGroup.CustGroup + " - " + taxGroupHeading.TaxGroupName);
        next custGroup;
    }
Advertisements