Exploring Alternatives for SELECT TOP in X++ - D365 Finance and Supply Chain
Recently, I had to write a query that retrieves Batches with the oldest Manufacturing Date in D365 Finance and Supply Chain, utilizing the "SELECT TOP" functionality. However, as you may be aware, the TOP keyword is natively supported in SQL but not when composing a select statement in D365 and X++. Nevertheless, there are alternative techniques to accomplish the same objective. Additionally, I have included some performance metrics to assist you in choosing the most appropriate method based on your specific requirements. Scenario : Write a query to get the top 3 batches and locations which have the oldest Manufacturing Date for an item and have Available Physical > 0, grouped by the Item, Batch Id, Manufacturing Date and the location. Assumptions : One legal entity, with one site and warehouse Here's how the SELECT TOP Clause would look like in SQL: Method 1 : Select statement using a break Once we reach the required number of records, we break from th...