Alfanumeric superdescriptor assortment on FIND .. WITH ..THRU

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.

This topic was automatically closed 180 days after the last reply. New replies are no longer allowed.