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.
I believe what you want is the equivalent below.
If you had any STATUS values of O, P, Q, R your query would get all of them…
FIND … WITH
STATUS-TYPE-DEADLINE = N888820201201
THRU N888820201231
OR
STATUS-TYPE-DEADLINE = S888820201201
THRU S888820201231
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.
1 Like
A few points to start:
- 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.
1 Like
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'
...
or, with variables for the keys:
STATUS-TYPE-DEADLINE-INI-N := 'N888820201201'
STATUS-TYPE-DEADLINE-FIN-N := 'N888820201231'
STATUS-TYPE-DEADLINE-INI-S := 'S888820201201'
STATUS-TYPE-DEADLINE-FIN-S := 'S888820201231'
FIND CTRL-FILE WITH
STATUS-TYPE-DEADLINE = STATUS-TYPE-DEADLINE-INI-N THRU STATUS-TYPE-DEADLINE-FIN-N
OR STATUS-TYPE-DEADLINE = STATUS-TYPE-DEADLINE-INI-S THRU STATUS-TYPE-DEADLINE-FIN-S
...
1 Like
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.
1 Like
The point is that for any status i need to ensure the deadline within the limits, avoiding the numeric parts that are off the range.
This superdescriptor, as designed, will NOT give you what you are looking for.
If the DEADLINE is the primary criterion you will need to move it in front of STATUS and TYPE.
2 Likes
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
I thought that SORTED could improve the performance. I think I can remove it.
I didn’t know we can use WITH … OR .
Maybe this is a good approach for now, because I don’t want to change a lot the program.
Thanks for the tip. Maybe I’ll write a test version using READ.
You will either need two READS: 1 for N series and 1 for s seriers, or
A READ with a reposition to “S…” when it gets to end of N series……
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.
1 Like
Just remember to use OR not AND.
In English we say AND but in logic we need OR.
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.
1 Like
SORTED does internal ADABAS SORT which sometimes is costly.
If you need data sorted I recommend READ LOGICAL as per other response (READ… REPOSITION).
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.
1 Like
Thank you for the clarification on ADABAS processing versus NATURAL processing. It is a very important point to consider.