During debugging a FIND … WITH … THRU loop I’ve gotten unexpected and undesired records.
I’d like to understand the arranging behind the THRU option when the superdescriptor has an initial letter and numbers.
Here’s an example:
DEFINE DATA LOCAL
1 STATUS-TYPE-DEADLINE-INI (A13) 1 REDEFINE STATUS-TYPE-DEADLINE-INI
2 STATUS (A01) /* N = NOT PROCESSED YET , S = SUCESSFULY PROCESSED
2 TYPE (N04)
2 DEADLINE (N08) /* AAAAMMDD
1 STATUS-TYPE-DEADLINE-FIN (A13) 1 REDEFINE STATUS-TYPE-DEADLINE-FIN
2 STATUS (A01)
2 TYPE (N04)
2 DEADLINE (N08)
...
STATUS-TYPE-DEADLINE-INI := N888820201201
STATUS-TYPE-DEADLINE-FIN := S888820201231
F3X. FIND CTRL-FILE WITH
STATUS-TYPE-DEADLINE = STATUS-TYPE-DEADLINE-INI
THRU STATUS-TYPE-DEADLINE-FIN
SORTED BY STATUS-TYPE-DEADLINE
...
END-FIND
The undesired record that I got:
N888820210120
(That is, this deadline is off the range)
Ultimately, my main doubt is why this record was selected even if the “numeric part of the string” is a larger number than the limit of the range.
If someone could give an example of this arrangement, I appreciate it. I didn’t found this in the documentation.
Comments:
- I can’t use another superdescriptor and I don’t have subdescriptor
- I can’t change the statuses
- I’m considering using " WHERE DEADLINE GT … AND LT … " to avoid this record but I don’t know if this could work.
- If the WHERE doesn’t work I think I could use REJECT or ACCEPT or IF … ESCAPE TOP .
Any deliberation is welcome. Thanks in advance.
Eugene (Gene) Miklovich
ADABAS/NATURAL Systems Support and DBA
Cell: 916-202-7047
Normal Hours: 8am-4pm E.T.
Please call or text if assistance needed outside these hours.
a READ on the super you have will perform the selection and sort far more efficiently than a FIND THRU SORTED.
There is no difference between WHERE, ACCEPT/REJECT or IF ESCAPE TOP from the database processing point of view - the 3 approaches do the same database processing. The differences in Natural processing are that ACCEPT/REJECT or IF ESCAPE TOP can do other processing on the records before returning to the database statement (READ or FIND). The WHERE clause is applied before any other processing is done in the loop, so rejected records are not processed and are not counted in *COUNTER (the other 2 are).
The records are selected based on the entire superdescriptor, not the components of the super. That is FIND file WITH STATUS = ‘N’ OR = ‘S’ and TYPE = ‘8888’ and DEADLINE = 20201201 thru 20201231 is NOT the same the FIND on the superdescriptor. The value “N888820210120” is between “N888820201201” and “S888820201231”, so it is selected - any “N” or “S” status value less than “S88882021231” will be selected
If you are looking for everything with the deadline between 20201201 and 20201231 and can only use the superdescriptor you have, then I would suggest trying this:
for #I = 1 to 2
if #i = 1
STATUS-TYPE-DEADLINE-INI.STATUS := ‘N’
else
STATUS-TYPE-DEADLINE-INI.STATUS := ‘S’
end-if
STATUS-TYPE-DEADLINE-FIN.STATUS := STATUS-TYPE-DEADLINE-INI.STATUS
READ CTRL-FILE WITH STATUS-TYPE-DEADLINE = STATUS-TYPE-DEADLINE-INI TO STATUS-TYPE-DEADLINE-FIN
…
END-READ
END-FOR
Or Gene’s FIND/OR works but with more database work.
Tomas, the record N888820210120 is being returned because it is within your specified range, “N888820201201” through “S888820200123”. Your FIND as written would also return any records that had a status of O, P, Q, or R, as well as all records with status S with types lower than 8888.
I can only guess that you want records with Status = N or S, AND Type = 8888, AND Deadline between 12/1/2020 and 12/31/2020. If that is the case you could write it like this:
FIND CTRL-FILE WITH STATUS-TYPE-DEADLINE = 'N888820201201' THRU 'N888820201231'
OR STATUS-TYPE-DEADLINE = 'S888820201201' THRU 'S888820201231'
...
Adabas is giving you what you asked for:
N888820201201 thru N999999999999
all O, P, Q, R records
S000000000000 thru S888820201231
I think what you want is
FIND CTRL-FILE WITH STATUS-TYPE-DEADLINE = 'N888820201201'
THRU 'N888820201231'
OR STATUS-TYPE-DEADLINE = 'O888820201201'
THRU 'O888820201231'
OR STATUS-TYPE-DEADLINE = 'P888820201201'
THRU 'P888820201231'
OR STATUS-TYPE-DEADLINE = 'Q888820201201'
THRU 'Q888820201231'
OR STATUS-TYPE-DEADLINE = 'R888820201201'
THRU 'R888820201231'
OR STATUS-TYPE-DEADLINE = 'S888820201201'
THRU 'S888820201231'
By the way, the SORTED BY clause is never a good idea. It is likely to cause performance issues. I think you need a subdescriptor on DEADLINE or TYPE-DEADLINE.
The domain of the status field for now is just [N, S].
Now I understand that ADABAS will bring all N records up to the limit (N999999999999) before moving to the next status