Select Top in X++ code

(0) Share

Report

Posted on by Community Member

Hi everybody,

I have a problem that needs to select top x records like SQL in X++ code.

I found that X++ has firstonly, firstonly10, firstonly100 and firstonly1000.

but i need to select x records, and x is a variable.

What can i do for this case.

Thank for your help.

*This post is locked for comments

I have the same question (0)

All responses (5)

Answers (2)

Verified answer

DavidGunawan 1,381 on at

Like (0)

Report

RE: Select Top in X++ code

Hi,

As you know that there is no select top x in the select statement in x++. However, you can do while select or query and put a variable as a counter in the loop. Then use if else to validate the counter and exit the loop once counter reach the limit.

Example:

int counter;

int limit = 5;

CustTable custTable;

;

while select custTable

order by custTable.AccountNum

{

counter++;

if(counter>=limit) break;

}

Was this reply helpful? Yes No
Suggested answer

startax 1,845 on at

Like (0)

Report

RE: Select Top in X++ code

@ Tu Ngyen ,

As David says you have to use Counter in this case ,

community.dynamics.com/.../222422

Was this reply helpful? Yes No
Suggested answer

Luan Nguyen 689 on at

Like (0)

Report

RE: Select Top in X++ code

you may have 2 ways to do it:

1. use Counter then break;

2. use a temporary record buffer then remove the records that you don't need.

Was this reply helpful? Yes No
Verified answer

Martin Dráb 236,513 Most Valuable Professional on at

Like (2)

Report

RE: Select Top in X++ code

Select Statement Syntax documents what's supported, and you can see that there is no TOP or anything similar. It's unfortunate, but that's all what we have.

Selecting more records and ignoring what you don't need (e.g. selecting with firstonly10 and taking just 7) is usually a reasonable approach.

Another solution is using a view with a computed column with RANK() or ROW_NUMBER(). Then you can filter by this computed column (WHERE RowNumber < 8). It's more complicated, but it's useful in some situations.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: Select Top in X++ code

Thank you very much for your help.

I have resolved this problem using counter.

Was this reply helpful? Yes No