Advanced Topics

Periodic Group Fields in Selection Criteria

For an ADABAS periodic group data field, the ACCESS procedure automatically creates a SAS variable for the occurrence number within the periodic group. For example, the NATURAL DDM named CUSTOMERS has a periodic group field named SIGNATURE-LIST, which groups data fields LIMIT and SIGNATURE. The ACCESS procedure creates a SAS variable named SL_OCCUR for the occurrence numbers in LIMIT and SIGNATURE.

By including the _OCCUR variable in a view descriptor, you can retrieve the occurrence numbers for the periodic group. You can also include the _OCCUR variable in SAS WHERE clauses to qualify data, but the condition is processed by SAS after ADABAS has completed its selection processing. You cannot update the occurrence values, and you cannot use the _OCCUR variable in a view WHERE clause.

The following table lists whether you can use periodic group SAS variable names, periodic group occurrence syntax, and a periodic group's corresponding _OCCUR variable in SAS and view WHERE clauses.

Periodic Group Fields in WHERE Clauses
Periodic Group Field	SAS WHERE Clause	View WHERE Clause
SAS variable name	yes	yes
ADABAS data field name and occurrence syntax	no	yes
_OCCUR variable	yes	no

WHERE Clause Examples

Using the periodic group data field LIMIT from the CUSTOMERS DDM, the following examples illustrate using a periodic group data field in WHERE clauses.

WHERE Clause Example 1

You can use the SAS variable name of a data field within a periodic group in both a SAS WHERE clause and a view WHERE clause. However, they will not always produce the same results because the SAS WHERE clause post-processes the results and, using the following example, looks at the value of variable LIMIT to determine whether it's equal to 5000. The view WHERE clause is not post-processed; when you use a periodic group field, ADABAS qualifies all periodic group occurrence values if any one meets the WHERE clause criteria.

For example, you can include the following WHERE clause in a view descriptor, and you can issue it as a SAS WHERE clause:

where limit = 5000

Stored in a view descriptor, the WHERE clause produces the results in the following output:

Results of Referencing a Periodic Group Data Field in View a WHERE Clause

           OBS    CUSTNUM      SL_OCCUR                 LIMIT

             1    12345678            1               5000.00
             2    14324742            1               5000.00
             3    14324742            2              25000.00
             4    14569877            1               5000.00
             5    14569877            2             100000.00
             6    19783482            1               5000.00
             7    19783482            2              10000.00
             8    26422096            1               5000.00
             9    26422096            2              10000.00
            10    27654351            1               5000.00
            11    29834248            1               5000.00

However, as a SAS WHERE clause, the results in the following output are produced.

Results of Referencing Periodic Group Data Field in SAS WHERE Clause

           OBS    CUSTNUM      SL_OCCUR                 LIMIT

             1    12345678            1               5000.00
             2    14324742            1               5000.00
             3    14569877            1               5000.00
             4    19783482            1               5000.00
             5    26422096            1               5000.00
             6    27654351            1               5000.00
             7    29834248            1               5000.00
             8    43459747            2               5000.00

WHERE Clause Example 2

You can qualify a specific occurrence of a periodic group with a view WHERE clause, but only by using the periodic group occurrence syntax. However, all of the periodic group occurrence values for the qualified records are returned, not just the individual occurrence specified in the view WHERE clause. You cannot specify the occurrence syntax in a SAS WHERE clause. For example, this view WHERE clause produces the results in the following output.

where limit(2) = 5000

Results of Qualifying Periodic Group Occurrence Syntax in View WHERE Clause

           OBS    CUSTNUM      SL_OCCUR                 LIMIT

             1    43459747            1               1000.00
             2    43459747            2               5000.00

WHERE Clause Example 3

If you include the _OCCUR SAS variable in the view descriptor, you can use it in a SAS WHERE clause to specify an occurrence. However, you cannot use the _OCCUR variable in a view WHERE clause.

For example, this SAS WHERE clause produces the results shown in the following output.

where sl_occur = 2

Results of Including _OCCUR Variable in SAS WHERE Clause

           OBS    CUSTNUM      SL_OCCUR                 LIMIT

             1    14324742            2              25000.00
             2    14569877            2             100000.00
             3    14898029            2              50000.00
             4    18543489            2              50000.00
             5    19783482            2              10000.00
             6    19876078            2              25000.00
             7    26422096            2              10000.00
             8    43459747            2               5000.00

To qualify the data even further, you could use this SAS WHERE clause, which produces the results in the following output.

where sl_occur = 2 and limit = 5000

Results of Including _OCCUR Variable and Occurrence Syntax in SAS WHERE Clause

           OBS    CUSTNUM      SL_OCCUR                 LIMIT

             1    43459747            2               5000.00

Top of Page