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
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.
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.
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:
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
Pete Alberts True
Pete Sage False
2) option 2
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.
Never mind I think I got it.
Here's an example of two more or less equivalent statements:
//normally this is as far as you would go, ignoring any subsequent records
//OR the "normal way"....
while select cust
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.
Business Applications communities