(un)expected behaviour "FOR EACH"

Just a point of note on your query: any FOR EACH with a function in the left of the predicate will always do a whole index read. In your case above that's not an issue, but as soon as you hit a table with any significant volume of data you will have major performance hits.

You can add a AND NUM-ENTRIES(f1,"-") > 1 as part of your criteria

FOR EACH tt

WHERE NUM-ENTRIES(f1, "-") > 1

 AND ENTRY(2,f1,"-") EQ "A":

DISPLAY tt.

END.

Or alternatively do the NUM-ENTRIES and ENTRY check inside the FOR block.

FOR EACH tt:

 IF NUM-ENTRIES(f1, "-") > 1 AND ENTRY(2,f1,"-") EQ "A" THEN

   DISPLAY tt.

END.

The num-entries option is indeed one that I would do if it were a static for each. I'm now moving to a situation where I go through the for each dynamically (with as few criteria as possible) and do a find first (dynamically) on the found record (where I can add the no-error) to see of the additional stuff (like the entry(2,...)) validates correctly. Fortunately it is already a stripped and optimized subset of a 100 million plus number of records :)

Let's think about this. If someone gave us an arbitrary buffer tt, and we needed to determine whether a particular record in that buffer met our processing criteria, we'd certainly structure our tests differently:

1) Is there a record in the buffer? available tt

2) Does field F1 have a non-blank, non-unknown value? tt.F1 gt ""

3) Does F1 have a second entry value in a dash delimited list? num-entries ( tt.F1, "-" ) gt 1

4) Is the second entry in the list an "A"? entry ( 2, tt.F1, "-" ) eq "A"

Unless you have all of these tests, either explicitly or implicitly, you're not writing valid code.

Hi Greg,

I moved the critical stuff from the "for each" in the query to a find-first of the buffer (where I have a no-error available).

This application is a track&trace application (for an ESB) where specifically named attributes on  a message are grouped in a temp-table record for selection. So for example the attribute attrib.flow and attrib.objectID and attrib.domain are three fields in such a record in the temp-table "atttribs" (together with some fixed fields) and maybe some other not yet known fields (based on the fact that the attribute starts with "attrib."), all attributes are stored in a 1:N related table (by a different application that consumes all Track&Trace data from the ESB).

With this structure I now can create a query to select specific messages based on the attributes (values). A lot of different messages are possible in the Track and Trace database. If I want to see messages of a purchase order with a specific ID I need to hope that it is contained in a specific attribute (for example attribs.OrderID) but in some cases it is part of some other attrib (attribs.objectID for example).

FOR EACH attribs WHERE attribs.flow = 'orders' AND attribs.OrderID = 'EF23011'

or 

FOR EACH attribs WHERE attribs.flow = 'orders' AND ENTRY(2,attribs.ObjectID,"/") = 'EF23011'

I use some configuration to specify where the order ID is stored and what specifies an orderID (everything dynamically so new message specifications can be added) So if I specify that the order (entity) is entry 2 of ObjectID and for some reason some of the attributes were not correctly assigned the number of entries of a field is not correctly. But if I have to parse the ENTRY(2,attribs.ObjectID,"/")  to prevent errors that would be hard because now it is an ENTRY statement but what if it becomes something else (sometimes we don't have enough influence on the attributes).

I specifically use this to see if all states of an order are processed correctly by the ESB before I can cleanup the Track&Trace information of that specific order. As you can imagine it is a little more complex in the real solution because of dependencies.

Anyway it would have been great if the ABL just would have allowed the ENTRY(2 in the for each :)

The workaround I thought of workes so I'm happy for now.

> In fixed queries it is no real problem to add a test on the number of entries just in front of the "entry(2" part but

> because the query I use is dynamically generated it is a lot harder to control the order of tests in the for each.

...

> The num-entries option is indeed one that I would do if it were a static for each.

In what way do you generate the FOR EACH query "dynamically"? If you have some configuration that says the query needs to check entry number N (e.g 2) of field F, then why can't you also generate the guard check NUM-ENTRIES(F) >= N too and insert it before the ENTRY() clause?

Furthermore, FIND FIRST NO-ERROR does NOT prevent the error "entry N is outside of the range of  F". At least not when I tried it with an actual field with too few entries. (But it might allow you to trap that error inside the block where you have the FIND, in stead of aborting it.)

> Anyway it would have been great if the ABL just would have allowed the ENTRY(2 in the for each :)

The question is: would we be happy if ABL will silently ignore the errors in the functions inside the WHERE clause or should it inform us when something goes wrong?

The simplified example:

FUNCTION MyFunc RETURNS CHARACTER.
  /* RETURN ENTRY(2,"A,a"). */
  /* RETURN ERROR. */
  RETURN ENTRY(2, "a").
/*
  DEFINE VARIABLE f AS CHARACTER NO-UNDO.
  ASSIGN f = ENTRY(2,"a") NO-ERROR.
  IF ERROR-STATUS:ERROR
  THEN RETURN ERROR.
  ELSE RETURN f.
*/
END FUNCTION.

DEFINE TEMP-TABLE tt
  FIELD f1 AS CHARACTER.
DO TRANSACTION:
  CREATE tt.
  ASSIGN f1 = "a".
END.
FOR EACH tt WHERE f1 EQ MyFunc():
  DISPLAY tt.
END

The rationale is that if we can't evaluate the expression we are trying to compare to, we cannot tell if it satisfies the WHERE clause or not.  We can't assume the programmer knew what they were doing if they give us something that really can't be evaluated.  So just concluding that the record doesn't satisfy the query is a bit dangerous.  It is better to give an error and make the programmer give us a valid expression than to give the wrong query results.  The results of a query  can have significant real-world consequences!  We need to be sure the answer is correct.

Whoever taught you that "NO-ERROR" is a solution to this (or, for that matter, most any) problem did you a disservice.

My point, however opaquely stated, was that you should be using the tests I outlined:

FOR EACH attribs WHERE attribs.flow = 'orders' and num-entries ( attribs.objectID, "/" ) gt 1 AND ENTRY(2,attribs.ObjectID,"/") = 'EF23011'

I know that for each will implicitly fulfill the available test, I suspect that num-entries is sufficient to implicitly test attribs.objectID ne ? and attribs.objectID gt "" . Under the rules of short circuiting, the num-entries test must come before the ENTRY test. If we were nitpicky, we might require ... 'orders' and ( ( ) and ( ) ) but I'm reasonably sure we're OK without the additional parens.

Hi Greg,

I guess you missed my explanation above. It is possible that attributes are not correctly filled for whatever reason. The NO-ERROR is excellent for handling those exceptions (because the alternative is a STOP condition and a program that will halt each time because of a rare exception that can be handled by me without any problem. Because I do not type in the query but it is generated it is also hard to solve the issue to eliminate all exceptions in the query.

Anyway. I have a perfect solution for my much more complex problem then the example I added to reproduce the error.

Hi Laura,

I understand you'r point (partly :) ). Especially when including more complex stuff but I expected it to behave almost identical as a f1 = "A" statement. The thing is that if it was about entry(1,...) there would be no problem (In my case it was also solved if I would make sure that in that case the entry(1,... ) (which was also in the query) was added first to the for each. That one failed in this case so the rest of the where clause was not tested. But this order is not always the same so that was not a solution for the future as well.

But as stated elsewhere I found a solution that works for me and doesn't add complex parsing of the freeform definitions of the elements in the query.

Just a little remark: if you don't want to use NUM-ENTRIES for some reason, you can also append a delimiter to make ENTRY function work without errors:

WHERE ENTRY(2, f1 + "-", "-") EQ "A"

Also a very nice solution!