Select statements noob question

(1) Share

Report

Posted on by Pete Alberts

3,542

The reason for this question is probably because I don't know SQL well enough, so it probably doesn't belong on the dynamics forums.

In any case, this link https://docs.microsoft.com/en-us/dynamics365/unified-operations/dev-itpro/dev-ref/xpp-data-query mentions that a select statement only returns one record. Which doesn't make sense to me for two reasons: 1) I understand it is good practice to use the firstonly keyword (in some cases), which implies that the select statement can return more than one record. 2) I understand the while select statement loops through the records returned by the select statement.

In the case that a select statement does return more than one record, there are other things that don't make sense. For example working with the table buffer:

if (tablebuffer.field1 == "") does not make sense if the buffer could contain more than one record, as one of the record may have an empty field1 and the other may not.

In short: I am confused on whether a table buffer can contain more than one record, which relates back to how the select statement works.

Thanks for reading

*This post is locked for comments

I have the same question (0)

All responses (6)

Answers (3)

Verified answer

István Orosz 2,137 on at

Like (0)

Report

Hi Pete,

Usually the Query object is used if we need more record from a query, it is very handly to use it from X++. The simple select is usually used with a firstonly when restricted to only the first record found, or while select if all the records are needed. When more records are needed, the simple select keyword is rarely used, as it is inconvinient to handle more records.

Kind regards,

István

Was this reply helpful? Yes No
Verified answer

André Arnaud de Cal... 300,911 Super User 2025 Season 2 on at

Like (0)

Report

Hi Pete,

The select statement can return more than one record, but you can only handle one by one record. Indeed the firstonly keyword will retrieve only one record.

So, the tablebuffer.field1 does make sense as you can evaluate this record per record. However, if there is only one if-statement, you could also use the same if-statement as where clause.

If you need more clarifications, please ask. Then also provide some examples what you want to achieve as the link you provided is a very broad topic.

Was this reply helpful? Yes No
Pete Alberts 3,542 on at

Like (0)

Report

Thanks for the quick reply.

I am not busy with something specific, I am asking in general.

So in summary:

1) select can return more than one

2) buffer can contain more than one

So if we intended a select statement to return only one record, but it did not (for some buggy reason, which shouldn't have happened), what will be the result? for example:

NameTable

Name Surname Edited

Pete Alberts False

Pete Sage False

//wrongly assumed there is only one record with name "Pete"

select nameTable where nameTable.Name == "Pete";

nameTable.Edited = True;

What will the result of the code be? (I guess I can go test it myself :) )

1) option 1

NameTable

Name Surname Edited

Pete Alberts True

Pete Sage False

2) option 2

NameTable

Name Surname Edited

Pete Alberts True

Pete Sage True

I know that I could use while select to edit all the "Pete" records, but that is not the point of the question.

Was this reply helpful? Yes No
Verified answer

Pete Alberts 3,542 on at

Like (0)

Report

Never mind I think I got it.

Was this reply helpful? Yes No
Jonathan Halland 11,310 on at

Like (0)

Report
Here's an example of two more or less equivalent statements:

CustTable cust; select cust; //normally this is as far as you would go, ignoring any subsequent records while (cust) { //[logic here] next cust; }
//OR the "normal way".... while select cust { //[logic here] }

Was this reply helpful? Yes No
Pete Alberts 3,542 on at

Like (0)

Report

I think this question of mine originated from many developers that think (and told me) that a buffer "contains" more than one record.

That is incorrect. A buffer contains a single record.

You can select more than one record, but still there will only be one record in the buffer. When you call next, the next record is placed in the buffer.

I've discussed this with many developers with varying degrees of experience. And all of them believe that the buffer contains more than one record. Which is absurd. It is the same as saying that the variable i in a for-loop contains more than one value.

Was this reply helpful? Yes No