No Error Not Working As Expected

[Pooja]

Hello,
I am a newbie to Progress 4GL. I got an ERROR on FIND statement which I feel I should not get;

Statement #1: FIND customer NO-LOCK WHERE customer.name = ENTRY(3,"Pooja") NO-ERROR.
Statement #2: FIND customer NO-LOCK WHERE ENTRY(3,customer.name) = "Pooja" NO-ERROR.

I understand statement #2 looks stupid but there are code like this in our code base where there are multiple 'AND' conditions and one among them is like 'ENTRY(15,Field_Name)'.

Question - NO-ERROR captures the error on statement #1 but doesn't capture error on statement #2. Why? I was expecting NO-ERROR to capture 'Entry outside range' error.

 

[Rob Fitzpatrick]

This is more than just an issue of appearances. Statement #2 doesn't just look bad, it is bad. Using a function in a WHERE clause, where the function takes a field of the record being retrieved as an argument, prevents the ABL query engine from being able to bracket on an index. It results in a table scan.

Code that uses this pattern should be changed. Developers who write such code should be reeducated.

 

[Pooja]

Agreed.

But why is NO-ERROR not suppressing this ERROR on statement #2? If at all am forced to have the statement this way by my seniors how do I suppress and catch this error?

 

[Stefan]

I've been bitten by stuff like this in the past - the ENTRY function is being evaluated on it's own - this applies to any other function too, for example:

define temp-table tt field cc as char.

create tt. tt.cc = "hello".

find tt where int( tt.cc ) > 1 no-error.

You can prevent it with the rather ugly index breaking:

FIND customer NO-LOCK
   WHERE ( IF NUM-ENTRIES( customer.name ) >= 3 THEN ENTRY( 3, customer.name ) = "Pooja" ELSE FALSE )
NO-ERROR.

Note that attempting NUM-ENTRIES( customer.name ) >= 3 AND ENTRY( 3, customer.name ) will not work since you cannot influence the order of evaluation.

But as Rob mentioned - queries like these are ghastly.

 

[Pooja]

Thanks Stefan.

If that's the case why is NO-ERROR able to catch error on statement #1, there also function should expanded separately. Isn't it?

I tested out the combinations and looks like only for the left hand side operand the error is not captured (note: the left hand side operand should be the db field of the table which uses the WHERE clause). [Be it the field is indexed or non-indexed the error is not captured]

If the db field is right hand operand not pertaining to the table that is been queried NO-ERROR catches it properly.

 

[Cringer]

I think it's to do with the fact that for #2 the function needs to be evaluated in order to create a valid expression, whereas in the first it is evaluated as part of the execution of the expression. I hope that makes sense.

 

[TomBascom]

The way that it works is that NO-ERROR refers to the result of the query. Errors that occur while "preparing" the the query (i.e. the WHERE clause) are not caught.

You could argue that it ought not to be like that but that is how it is. I seriously doubt that it will ever change.

You could probably wrap the whole thing in a DO ON ERROR block if you really want to catch it. But I haven't actually tried that.