Using REPEAT after GET

u382514

I'm specifically using 2013 R2 and have not tried this in other versions:

If you do a GET on a record it should bring back ONE record if it finds it. At least that's what I thought. If you then do a REPEAT on that record after the GET it will start at the Record it got and then repeat until the end of the table (using UNTIL NEXT=0).

Not really a big deal, but seems counterintuitive to what the GET should be used for which is to get ONE record.

"Why are you putting a REPEAT after a GET anyways?", you may ask. If a value is passed to a specific function I attempt to do a GET. If the value is blank it uses a SETRANGE/FINDSET to get a list of records.

What do you think?

aavio

i think its a wrong way of coding.
if you use GET, dont use repeat until.

ppavuk

aavio wrote:

i think its a wrong way of coding.
if you use GET, dont use repeat until.

Absolutely! Can't imagine a case when you repeat after get. GET is to get 1 specific record by primary key, nothing else!

vaprog

In general, with NAV, you should not think in terms of data sets (the relational way of looking at data). C/AL was designed for the native database, which uses an index sequential model.

With the SQL Server option, this did not change. NAV just tries to utilize data sets most efficiently behind the scenes. Operations such as FINDSET, FINDFIRST, FINDLAST, ISEMPTY, that have been introduced with SQL Server in mind, are just a way to allow you to give hints to that behind-the-scene-logic.

So any of the FIND... or GET operations primarily set a position on a record. Any NEXT, FIND('>'), FIND('<') OR FIND('='), even MODIFY, DELETE, RENAME do not even depend on any previous FIND or GET, but on the field values of the record variable you perform the operation on.


But beware: not keeping relational data sets in mind will have a performance penalty.

DenSter

The only way that GET gives you more than one record is when the table has a compound primary key and you don't specify a value for all of those fields. Normally, you would only ever do GET when you know all primary key field values, when you can uniquely identify the record. When you specify a value for all primary key values, by definition you uniquely identify the record, in which case the filters set in the query will ALWAYS return ONE SINGLE record.

Using GET for anything else, while it unfortunately compiles, is incorrect coding.

vaprog

DenSter wrote:

The only way that GET gives you more than one record is ...

No. GET always returns exactly one record, returns FALSE or throws an error.

DenSter wrote:

... in which case the filters set in the query will ALWAYS return ONE SINGLE record.

No filters are set or used with GET.

C/SIDE Help wrote:

GET Function (Record)
Finds a record based on values stored in primary key fields.
...
Parameters
...
Value,…
Type: any
Values in primary key fields. The data type of this parameter must match the data type of the corresponding primary key field. For example, if you enter an integer, the data type of the corresponding primary key field must also be an integer. The value 0 (zero) or an empty string is assigned to unspecified fields.
...
Remarks
This function always uses the primary key for the table and ignores any filters. The current key and filters are not changed after you call this function.

Emphasis by italics by me.

So, all fields of the unique primary key are defined on any GET you call, whether you specified it or not. Zero or one record match.

Have you ever done e.g. a

GLSetup.GET;

?
Actually you needed to code this as

GLSetup.GET('');

but that's uncommon.

A consecutive NEXT will return the next record in sorting order within the filter regardless of whether you have a simple or compound key and regardless of whether you specified values for all, some or none of the primary key fields as parameters to GET, given such record exists.

DenSter

Yeah you don't have to quote F1 help for C/AL keywords to me, I've probably read through all of it over the years

Help is very good explaining what it's SUPPOSED to do, but there are a bunch of side effects that are unfortunately not documented. This is one of those cases where the compiler falls short in catching whether the developer takes care of ALL primary key fields. As you've already stated GLSetup.GET works, where we should be forced by the compiler to actually specify GLSetup.GET('') and explicitly code for the blank value. The C/AL compiler does NOT force you to specify ALL primary key field values.

Let's say we have a table called MyTable with primary key fields A and B, and we'll do a MyTable.GET(1,2). The SQL Query will be SELECT * FROM MyTable WHERE A=1 AND B=2. The WHERE clause is here the filters go, so YES there ARE definitely filters when using GET, they are just not specified by a specific C/AL FILTER keyword. Since we have a value for both primary key values, we now uniquely identify the record, and we get just one record back.

If you do MyTable.GET(1) on this same table (and only specifying one field, even though the table has a compound primary key), the query will be SELECT * FROM MyTable WHERE A=1. This SHOULD not work, but unfortunately it does, at least the last time I tested this it did, and I do have to admit this was probably a few versions ago. Since we now only have a value for one out of two primary key fields, there is a chance that you get a whole bunch of records with only the filter on field A. In this case (let's say you have records for (1,1) and (1,2) and (1,3) GET returns three records, and a REPEAT actually DOES loop through those records.

Try it, you'll see. I did this probably back in 5.0 maybe even before that. I'm not exactly sure what I did to test it, but I definitely got more than one record back, which I was very surprised to see.

Kishorm

DenSter wrote:

If you do MyTable.GET(1) on this same table (and only specifying one field, even though the table has a compound primary key), the query will be SELECT * FROM MyTable WHERE A=1.

Does it REALLY do this - I'd be surprised. I would expect the query to be SELECT * FROM MyTable WHERE A=1 AND B=0 because my understanding is that when not all values are supplied on a GET then NAV uses default values, I.e. 0 for Integer and Decimal, '' for Code and Text, etc... So in this instance. GET(1) is exactly the same as a GET(1,0)

Also, back to the original question, it doesn't matter what record is found with a GET, doing a REPEAT..UNTIL NEXT=0 will iterate through all remaining records (from the position of the GET onwards) which match explicit filters set by SETRANGE and SETFILTER - which I think is exactly what it should do.

DenSter

Kishorm wrote:

Also, back to the original question, it doesn't matter what record is found with a GET, doing a REPEAT..UNTIL NEXT=0 will iterate through all remaining records (from the position of the GET onwards) which match explicit filters set by SETRANGE and SETFILTER - which I think is exactly what it should do.

You're missing the point. When you do GET(1,2), the WHERE clause has a value for both fields. Doing GET with ALL primary key fields sets filters on ALL primary key fields, and the record set will have just one record, because you have just set filters on all primary key fields, uniquely identifying the record. Doing a REPEAT on a record set of one record will loop through just one record. There ARE no subsequent records after a GET with all primary key field values specified, because there is only one record.

Therefore, if a GET returns more than one record, then the filters did not uniquely identify one record, which means that not all primary key fields were properly filtered to uniquely identify the record. I was just adding my two cents on what I've seen when not all primary key fields were properly filtered. You don't believe me, but you are making assumptions in your argument. Try it, see for yourself. If I weren't swamped with work I would do this test again and show you in a YouTube video, but I just don't have the time for it.

None of the people replying to this topic seem to understand what "uniquely identify a record" means.

Kishorm

I understand what you are saying but I think you're looking at this from a purely SQL point of view - yes, when you do a GET the NAV client does issue a SQL Select with a WHERE clause but this is just for the purposes of fetching the appropriate record in NAV. The WHERE clause is irrelevant after that from a NAV point of view. I am thinking in terms of NAV. My way of thinking is that if you go back to the Native ISAM backend - doing a GET simply positions the record position and retrieves that record (without applying a filter which affects any remaining database access). Now with a SQL backend I think the results of the GET should do the same from a NAV point of view - the fact that in order to achieve this the NAV client has to issue a SQL SELECT with a WHERE clause is irrelevant.

I just think we are looking at this from different points of view

Regarding what happens when you don't specify all primary key fields in a GET - you're right I didn't test this at the time as I was not in a position to do this. However I have tried this out now (on 4.0 SP3 Build 23305 and also 2009 R2 build 32012) and it does do what I thought it should do, i.e.

NAV Code..

SalesLine.GET(SalesLine."Document Type"::Order,'1234'); - i.e. I purposely have not specified a value for the "Line No." field

Resulting SQL Code...

SELECT * FROM "CRONUS Company$Sales Line" WITH (READUNCOMMITTED) WHERE "Document Type"=1 AND "Document No_"='1234' AND "Line No_"=0

...i.e. the NAV Client has included the "Line No_" field in the WHERE clause (with a default 0 value) even though the GET did not specify a value for the "Line No." field.

BTW - it wasn't that I didn't believe you (I know you know NAV better than most) - it was that I was astounded that the NAV client would do such a thing (as this would result in unpredictable results). Maybe there was a bug in the NAV build that you tested this on.

vaprog

DenSter wrote:

Yeah you don't have to quote F1 help for C/AL keywords to me, I've probably read through all of it over the years

Yes, I know that you are very capable professional. All the more reason to back my claims with quotes from authoritative sources.

DenSter wrote:

Help is very good explaining what it's SUPPOSED to do, but there are a bunch of side effects that are unfortunately not documented.

I agree.

DenSter wrote:

This is one of those cases where the compiler falls short in catching whether the developer takes care of ALL primary key fields. As you've already stated GLSetup.GET works, where we should be forced by the compiler to actually specify GLSetup.GET('') and explicitly code for the blank value. The C/AL compiler does NOT force you to specify ALL primary key field values.

This appears to be "by design", at least it is specified that way.

DenSter wrote:

Let's say we have a table called MyTable with primary key fields A and B, and we'll do a MyTable.GET(1,2). The SQL Query will be SELECT * FROM MyTable WHERE A=1 AND B=2. The WHERE clause is here the filters go, so YES there ARE definitely filters when using GET, they are just not specified by a specific C/AL FILTER keyword. Since we have a value for both primary key values, we now uniquely identify the record, and we get just one record back.

I think it is confusing to talk about filters when referring to the conditions in the WHERE clause of an SQL statement, even if you may look at it as a filter. In the NAV world, a filter is always something you set explicitly (through a statement or a property) and persists until you clear it explicitly.

DenSter wrote:

If you do MyTable.GET(1) on this same table (and only specifying one field, even though the table has a compound primary key), the query will be SELECT * FROM MyTable WHERE A=1.

My NAV 2009 R2 client does a SELECT * FROM MyTable WHERE A=1 AND B=0. Exactly as specified. Values for all primary key fields are provided, at most one row matches.

DenSter wrote:

... and a REPEAT actually DOES loop through those records.

Yes, REPEAT does loop, but that has nothing to do with any ambiguity in a preceding GET. It does exactly the same following a GET with as many parameters as fields in the primary key.

DenSter wrote:

Try it, you'll see. I did this probably back in 5.0 maybe even before that. I'm not exactly sure what I did to test it, but I definitely got more than one record back, which I was very surprised to see.

Well, I did. And NAV fortunately behaves as specified, which it wouldn't, if it behaved as you say (unless NAV would locate the proper record from within the set retrieved from SQL Server before GET returns).

DenSter wrote:

None of the people replying to this topic seem to understand what "uniquely identify a record" means.

This seems to me to be an entirely unfounded perception, probably caused by your persuasion that key fields not provided for in the parameter list of a GET statement remain unspecified. You challenged us to try it several times, so now I return this challenge to you (or just believe Kishorm and me, who did). But that's still just the SQL side of it, witch, after all, is completely irrelevant to the initial question.

DenSter wrote:

There ARE no subsequent records after a GET with all primary key field values specified, because there is only one record.

So, you have never fetched an Item with a GET command? did all other Items suddenly disappear? Or did GET set a filter on your Item Table? to filter on all primary key fields? None of this ever happened. Instead NAV behaves as I detailed in my first post in this thread as far as this pertains to C/AL.

DenSter

I'm glad you went back and actually tried it instead of making assumptions. In my test (as I stated this was year and several versions ago), when you didn't specify all primary key fields in a GET, it could result in more than one record. As I've also stated I don't remember exactly how I tested it, I don't even remember which version. I'm glad they at least fixed it so that all primary key fields get a default value for their filters.

I still say that neither one of you two (vaprog and kishorm) have demonstrated what "uniquely identify" means. The notion that the SQL filters are irrelevant is absurd. When you uniquely identify a record, you only get one record. There is not more than one record, so there is no looping through any more than one record, which is technically a loop of one, but it really has nowhere else to go. If you do a GET, and you can loop from that record in the same variable, there is something else going on.

My initial point to the OP was to check and make sure that they specify all primary key fields to make sure that all of them are specified. As you have demonstrated, this is no longer a problem, so therefore the problem is somewhere else.

I didn't say anyone was wrong, YOU are telling ME that I am wrong, which I am not. We're really saying the same thing mostly, except my point about uniqueness, you really don't seem to get that.

vaprog

DenSter wrote:

I still say that neither one of you two (vaprog and kishorm) have demonstrated what "uniquely identify" means.

I think you are not right with this assesment, as the following quotes and remarks, i believe, show. And furtermore i think it is not relevant to the question (see below and see my first answer in this thread).

vaprog wrote:

GET always returns exactly one record, returns FALSE or throws an error.
...
So, all fields of the unique primary key are defined on any GET you call, whether you specified it or not. Zero or one record match.

Kishorm wrote:

Does it REALLY do this - I'd be surprised. I would expect the query to be SELECT * FROM MyTable WHERE A=1 AND B=0 because my understanding is that when not all values are supplied on a GET then NAV uses default values, I.e. 0 for Integer and Decimal, '' for Code and Text, etc... So in this instance. GET(1) is exactly the same as a GET(1,0)

Also Kishorm's second post would not make much sense if he was not convinced that GET did not retrieve exactly one, fully identified (i.e. unambiguously identified) record.
So I really don't see why you think we did not get the concept of "uniquely identify". Where do you get that idea from?

DenSter wrote:

The notion that the SQL filters are irrelevant is absurd. When you uniquely identify a record, you only get one record.

Obviously, the WHERE conditions are not irrelevant to SQL server, but they do not have any effect on what data there will be in your C/AL record variable after any GET or whatever database operation in C/AL, as long as the C/AL operations adhere to their specification. And there has not been even an explicit claim in this thread, that NAV does not do that in regard to all those things we were talking about in this thread.

DenSter wrote:

There is not more than one record, so there is no looping through any more than one record, which is technically a loop of one, but it really has nowhere else to go. If you do a GET, and you can loop from that record in the same variable, there is something else going on.

I repeat: none of the C/AL database related operations depend on what data has been fetched before. They ONLY depend on the data present in the record variable at the time the operation is executed. What happens if you create a record variable in the Integer table and do

CLEAR(IntegerRec);
IntegerRec.NEXT;

IntegerRec.Number will be 1. If you now do

IntegerRec.Number := 10;
IntegerRec.NEXT;

IntegerRec.Number will now be 11. Whatever would have happened on the SQL Server end if the Integer table was a SQL table is entirely implementation dependent.

DenSter wrote:

My initial point to the OP was to check and make sure that they specify all primary key fields to make sure that all of them are specified. As you have demonstrated, this is no longer a problem, so therefore the problem is somewhere else.

Yes exactly. But the only problem here is a problem of understanding. The question arises only from not understanding that a loop (using NEXT) is entirely independent from the preceding GET with the exception that NEXT will use the data GET retrieved into the record variable, if not altered inbetween.

DenSter wrote:

I didn't say anyone was wrong, YOU are telling ME that I am wrong, which I am not. We're really saying the same thing mostly, except my point about uniqueness, you really don't seem to get that.

The thing we differ on in my understanding is

DenSter wrote:

The only way that GET gives you more than one record is ...

and the implication that this would affect in any way the records returned by a subsequent REPEAT ... UNTIL NEXT=0 loop. (The way I differ with the quote might even be entirely in our understanding. GET always fills in field values of exactly one row of data into the record variable it is executed on, unless no matching record was found. Whatever else C/AL does has no consequences on the logic of the C/AL program.)

FDickschat

Guys, please, so easy to give it a try:

ProgressBar.open('#1########');

CustLedgEntry.get(2701);
repeat
  cnt += 1;
  Progressbar.update(1, CustLedgEntry."Entry No.");
until CustLedgEntry.next = 0;

message('Cnt %1', cnt);

In a German Cronus 2009 SP1 there are 57 Customer Ledger Entries. Entry 2701 is number 5. The Progressbar inside the repeat until of course displays all Entry numbers starting from 2701 and the cnt at the end is 53 which is exactly as expected

The .get issues a "select * from ... where Entry No = 2701".
The .next will then fire another "select * from ..." but of course without any where clause as there are no filters set in C/AL.

You would need to add a

CustLedgEntry.SETRECFILTER;

after the .get so that the repeat until does not do something.

Kishorm

DenSter wrote:

I still say that neither one of you two (vaprog and kishorm) have demonstrated what "uniquely identify" means.

I think that's a totally unfair statement, please re-read our posts.

DenSter wrote:

The notion that the SQL filters are irrelevant is absurd.

You're twisting what I said/meant. I was trying to say that the WHERE clause generated in the SQL statement relating to the C/AL "GET" statement is irrelevant to the subsequent data access - i.e. the C/AL "NEXT" statement. I.e. assume the following C/AL Code...

Customer.RESET;
Customer.GET('30000');
Customer.NEXT;

In terms of what happens in SQL...

Yes the GET statement generates a SQL Statement with a WHERE clause on the "No." field which as you say uniquely identifies the record (I have never disputed this fact)

In terms if SQL you have a dataset containing 1 record, but in terms of NAV your Customer variable does not have any filters and all records are accessible via NEXT statements. I.e. the NEXT statement will move to the next Customer after 30000.

This is why I say the WHERE clause in the SQL SELECT issued when the GET is done is not relevant any more (i.e. for the NEXT statement) - as I'm sure you know already (but for the benefit of clarity) what NAV will do is generate a new SQL SELECT statement when the C/AL NEXT is performed

DenSter wrote:

My initial point to the OP was to check and make sure that they specify all primary key fields to make sure that all of them are specified. As you have demonstrated, this is no longer a problem, so therefore the problem is somewhere else.

And my initial post was questioning what you said happens in SQL when you do a GET without specifying all primary key fields. Yes, I made an assumption - but I was actually correct in my assumption, so no harm done - I didn't spread any mis-information.

DenSter wrote:

I didn't say anyone was wrong, YOU are telling ME that I am wrong, which I am not. We're really saying the same thing mostly, except my point about uniqueness, you really don't seem to get that.

Not true. I questioned whether NAV actually did generate a SQL SELECT without specifying all primary key fields when you have a GET that doesn't contain all primary key fields. I repeat - I didn't say you were wrong, I was just questioning whether it did really do that. My subsequent tests also show that it worked in the way I expected - so I feel justified in questioning this. Also, what is it exactly that makes you think I/we don't understand uniqueness - please re-read our posts.

rocatis

On a related note, I came across this peculiar behaviour on 2009:

If you have a table where the primary key is Integer, you (I) would assume that a GET without parameters would retrieve the record where integer is 0.

You would be correct - assuming you ran your code on the classic client. If you executed the exact same code on RTC, you got an error. For it to work on both platforms, you needed to execute a GET(0).

The problem does not exist in 2013 and may even be fixed for some 2009 builds.