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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s