SBX - Search With Button

SBX - Forum Post Title

Select statements noob question

Microsoft Dynamics AX Forum

Pete Alberts asked a question on 23 Apr 2018 1:50 AM
My Badges

Question Status

Verified

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

Reply
István Orosz responded on 23 Apr 2018 1:59 AM
My Badges
Verified Answer

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

Reply
Verified Answer

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.

Reply
Pete Alberts responded on 23 Apr 2018 3:09 AM
My Badges

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.

Reply
Pete Alberts responded on 23 Apr 2018 3:45 AM
My Badges
Verified Answer

Never mind I think I got it.

Reply
Jonathan Halland responded on 25 May 2018 7:17 AM
My Badges

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] }


Reply
Pete Alberts responded on 17 May 2019 2:19 AM
My Badges

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.

Reply
István Orosz responded on 23 Apr 2018 1:59 AM
My Badges
Verified Answer

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

Reply
Verified Answer

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.

Reply
Pete Alberts responded on 23 Apr 2018 3:45 AM
My Badges
Verified Answer

Never mind I think I got it.

Reply

SBX - Two Col Forum

SBX - Migrated JS