Skip to main content

Notifications

Announcements

Check out the New Agent Creator Community
Announcing new navigation menus and the Engage with the Community forum!
News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics AX (Archived) / while select ... limit
Microsoft Dynamics AX (Archived)

while select ... limit

(0) ShareShare
ReportReport
Posted on by Smartus 620

Hello

I am developing in x++ recently so I don't know alot of things. I was trying to limit my rows that I get from a while loop with a select. I already searched the forum here, but I only found an unsolved question in terms of what I was searching (http://community.dynamics.com/product/ax/f/33/p/64433/116870.aspx#116870).

I only want to get back 10 addresses for example, but limit doesn't seem to work. I also found something like firstOnly10, but that doesn't work either.

So here is my code that I am using:

while select AssetId from mcsConnectionTable
          join mcsAssetTable
          where mcsConnectionTable.AssetId == mcsAssetTable.Id
          join mcsAssetLocationTable
          where mcsAssetTable.RecId == mcsAssetLocationTable.Asset
          join logisticsLocationTable
          where mcsAssetLocationTable.Location == logisticsLocationTable.RecId
          join logisticsPostalAddressTable
          where logisticsLocationTable.RecId == logisticsPostalAddressTable.Location
{

...

}


I hope someone can help me out.
Thanks in advance.

 

Vincent

*This post is locked for comments

  • Verified answer
    Vilmos Kintera Profile Picture
    Vilmos Kintera 46,149 on at
    RE: while select ... limit

    This is the other solution, with firstonly10 working as intended:

    static void testFirstonly10(Args _args)

    {

       CustTable       custTable;

       int             i;

       select firstOnly10 AccountNum from custTable;

       while (custTable)

       {

           i++;

           info(strFmt('%1 - %2', custTable.AccountNum, i));

           next custTable;

       }

    }

  • Suggested answer
    Community Member Profile Picture
    Community Member on at
    RE: while select ... limit

    Hi Kent,

    Great Solution, I spend so much time trying to use firstonly10 to get the top 10 customers from table, as per client need. I came across this post and was very glad to find a suitable solution. I am curious to know ,  then what is the purpose of firstonly10.

    Thank you

  • DaleB Profile Picture
    DaleB 165 on at
    Re: while select ... limit

    Found it eventually in the help; just not as obvious as it could have been. The firstOnly modifiers optimize the query to return so many rows, but that's it. There's a note that there is no guarantee that the query only returns one row.

    [Note that this is different from ANSI SQL SELECT with a fetch-first clause. In SQL, FETCH FIRST n ROWS sets a maximum on how many rows can be retrieved, and it gives end of data if you try to read past that, even if more rows are available.]

  • Ryuji Mita Profile Picture
    Ryuji Mita 465 on at
    Re: while select ... limit

    If what's written this page is true, it seems that firstonly10, firstonly100, firstonly1000 is only supported on the Oracle platform.  Microsoft should have this written in the help...

    guides4it.com/.../Microsoft-Dynamic-AX-2009---Syntax-(part-2)---Statements---Data-Aware-Statements.aspx

  • DaleB Profile Picture
    DaleB 165 on at
    Re: while select ... limit

    If that's the case, that the firstOnly modifers don't work in a while select, it's certainly not clear from the help. But, of course, the counter option always works.

  • Community Member Profile Picture
    Community Member on at
    Re: while select ... limit

    Dale,

    Try this job.  It doesn't stop at 10 records.

    static void Job40(Args _args)
    {
        CustTable c;
        ;

        while select firstOnly10 AccountNum from c
        {
          
            print c.AccountNum;

        }
    }

     

    However "select firstOnly10 c" would.

    You cannot use it in a while select statment, only in select.  (at least in 2009 and i believe the same in 2012)

    http://msdn.microsoft.com/en-us/library/aa661012.aspx

    firstOnly

    Used in select statements to fetch only the first record.

    Select Statement Syntax

    firstOnly10

    Same as firstOnly, except returns 10 rows instead of one.

  • DaleB Profile Picture
    DaleB 165 on at
    Re: while select ... limit

    How do you mean you couldn't get firstOnly10 to work? That will limit it to return only 10 rows, but the syntax is different from regular SQL. See "Select Statement Syntax" in the help, but basically it works out to:

     while select firstOnly10 AssetId from ...

    That is, the firstOnly10 is part of the FindOptions clause, which comes after "select" and before the field list. You can also use firstOnly (return at most 1 row), forstOnly100, and firstOnly1000. If you need a count other than 1, 10, 100, or 1000, you must use a counter as already suggested.

  • Community Member Profile Picture
    Community Member on at
    Re: while select ... limit

    I would agree that its a slighty dirty solution..  but I'm not sure of another way to accomplish this.  FirstOnly10 will not work because that keyword is for select statements and not while select statements.

  • Smartus Profile Picture
    Smartus 620 on at
    Re: while select ... limit

    Hi

    I changed my name, because it's a professional forum here :-)

    Well I used that solution before you suggested it. But it's a rather dirty solution or am I wrong ? Isn't there something like a limit or range thing in x++ ?

    Anyway it's a solution, so I will mark it. But I hope there is a nicer solution then breaking out of a loop.

    Thanks!

    Vincent

  • Verified answer
    Community Member Profile Picture
    Community Member on at
    Re: while select ... limit

    Hi Smartus,

    Why not use a counter and break out of the while loop when you hit 10?

    For example:

    int myCounter;
    ;

    while select AssetId from mcsConnectionTable
              join mcsAssetTable
              where mcsConnectionTable.AssetId == mcsAssetTable.Id
              join mcsAssetLocationTable
              where mcsAssetTable.RecId == mcsAssetLocationTable.Asset
              join logisticsLocationTable
              where mcsAssetLocationTable.Location == logisticsLocationTable.RecId
              join logisticsPostalAddressTable
              where logisticsLocationTable.RecId == logisticsPostalAddressTable.Location
    {
      myCounter++;
      if (myCounter == 10)
      {
         // we have 10 records
         break;
      }
    ...

    }

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

News and Announcements

Check out the New Agent Creator Community
Announcing new navigation menus and the Engage with the Community forum!

Quick Links

Jainam Kothari – Community Spotlight

We are honored to recognize Jainam Kothari as our June 2025 Community…

Congratulations to the May Top 10 Community Leaders!

These are the community rock stars!

Announcing the Engage with the Community forum!

This forum is your space to connect, share, and grow!

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

#1
Mohamed Amine Mahmoudi Profile Picture

Mohamed Amine Mahmoudi 100 Super User 2025 Season 1

#2
Community Member Profile Picture

Community Member 50

#3
shanawaz davood basha Profile Picture

shanawaz davood basha 6

Last month Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans