database index filter routines

You pass in the index-fieldname of the "lowest" index field you want to start the filter with. Below is an example of using the filter to return all HRDEPBEN records for a specific company/employee filtered by the plantype/plancode where the hdb-start-date = ben-start-date.

Note that WS-DB-BEG-RNG is set to HDBSET4-EMPLOYEE because we want this limited by the company/employee based on the index, but then filtered from there:

I hope this helps!

Kelly

---

* Identify the fields to filter by
STRING "((HDB-PLAN-TYPE = ?) AND "
           "(HDB-PLAN-CODE = ?) AND "
           "(HDB-EMP-START = ?))" DELIMITED BY SIZE
INTO FILTER-STRING.

* Establish the filter
PERFORM 890-CREATE-FILTER. 

* Populate the Plan Type to filter (for the first "?")
MOVE BEN-PLAN-TYPE TO ALPHANUM-FILTER-VALUE.
PERFORM 890-SET-ALPHANUM-FILTER-VALUE. 

* Populate the Plan Code to filter (for the second "?")
MOVE BEN-PLAN-CODE TO ALPHANUM-FILTER-VALUE.
PERFORM 890-SET-ALPHANUM-FILTER-VALUE. 

*Populate the Start Date to filter by (for the third "?")
MOVE BEN-START-DATE TO DATETIME-FILTER-VALUE. 

*Populate the index fields
MOVE BEN-COMPANY TO DB-COMPANY.
MOVE BEN-EMPLOYEE TO DB-EMPLOYEE. 

* Identify Employee as the lowest index field to use to start the filter
MOVE HDBSET4-EMPLOYEE       TO WS-DB-BEG-RNG.

* Find the first record
PERFORM 850-FILTER-BEGRNG-HDBSET4.
 
*Cycle though the records until no more are found.
IF (HRDEPBEN-FOUND) 

*         Process the record
           PERFORM 1070-WHATEVER
           THRU 1070-END

*         Get the next record
           PERFORM 860-FIND-NXTRNG-HDBSET4.
           UNTIL (HRDEPBEN-NOTFOUND)
END-IF.


 

Got it, thank you. I want to get all closed transactions for a specific employee and plan.

**Create Filter closed trans and tran type 21
***
MOVE SPACES TO FILTER-STRING.
STRING
"(ETT-STATUS =?) AND "
"(ETT-PLAN-NAME =?) AND "
"(ETT-TYPE =?) AND "
"(ETT-DATE >=?) "
DELIMITED BY SIZE
INTO FILTER-STRING.
PERFORM 890-CREATE-FILTER.
MOVE 9 TO NUMERIC-FILTER-VALUE.
PERFORM 890-SET-NUMERIC-FILTER-VALUE.

MOVE WS-ETT-PLAN-NAME TO ALPHANUM-FILTER-VALUE.
PERFORM 890-SET-ALPHANUM-FILTER-VALUE.

MOVE "ME" TO ALPHANUM-FILTER-VALUE.
PERFORM 890-SET-ALPHANUM-FILTER-VALUE.

MOVE 20121002 TO DATETIME-FILTER-VALUE.
PERFORM 890-SET-DATETIME-FILTER-VALUE.

MOVE ETTSET1-EMPLOYEE TO WS-DB-BEG-RNG.

Don't forget - if you are going to set WS-DB-BEG-RNG to ETTSET1-EMPLOYEE, then before that you will need to populate DB-COMPANY and DB-EMPLOYEE with the proper values. The idea behind the filter is that it uses as much of an index as it can, and then filters the results of the index query based on the filter fields.

Index Filters are great tools.... just takes a little getting use to, but once you understand them, they will become your friend.  

What the WS-DB-BEG-RNG does is hold the lowest field of the index you are using, as Kelly stated.   The value that actually goes into this field is actually a numeric value tellig the system the lowest field you are using in the index.  Since  you are using Comany & Employee the value that will go into the WS-DB-BEG-RNG would be the numeric value of 2, since the employee value is the second field of the index.