Page History
These examples apply to creating a logical view within a file definition.
The file definition used in these examples is called CUSMST (Customer master file) and contains these fields:
Field | Description | Type | Len | Dec |
|---|---|---|---|---|
CUSTNO | Customer number (primary key) | S | 7 | 0 |
NAME | Name of person / organization | A | 30 | |
ADDR1 | Address line 1 | A | 35 | |
ADDR2 | Address line 2 | A | 35 | |
ADDR3 | Address line 3 | A | 35 | |
POSTCD | Post code | A | 4 | |
STATE | State mnemonic | A | 3 | |
ACTIVE | Active / inactive flag | A | 1 | |
CREDIT | Credit limit | P | 11 | 2 |
AMTDUE | Current amount due all accounts | P | 11 | 2 |
ACCTYP | Customer account type | P | 1 | 0 |
...
Example 1
Create a logical view/file called CUSMSTV1 that will order CUSMST by NAME and allow generic searching by NAME:
| No Format | ||
|---|---|---|
| ||
Logical view name : CUSMSTV1 |
...
Desc of logical view : Customer master by customer name |
...
Access path maint opt : IMMED Unique? NO Dynamic select? NO |
...
Key field details |
...
Field Description A/D S/U/A |
...
NAME__ A ______ |
...
_ + |
...
______ |
...
_ + |
...
+ |
Example 2
Create a logical view/file called CUSMSTV2 that will order CUSMST by STATE then POSTCD (i.e.POSTCD within STATE):
| No Format | ||
|---|---|---|
| ||
Logical view name : CUSMSTV2 |
...
Desc of logical view : Cust master by state and postcode |
...
Access path maint opt : IMMED Unique? NO Dynamic select? NO |
...
Key field details |
...
Field Description A/D S/U/A |
...
STATE___ |
...
_ _ POSTCD__ _ _ _ _ + |
Note that no ascend/descend value has been specified. In this case the value will default to ascend for both keys.
Example 3
Create a logical view/file called CUSMSTS1 that will order CUSMST by CUSTNO. Only records with ACTIVE = 'Y' are to be "visible" when using this logical view:Logical view name : CUSMSTS1
Desc of logical view : Active customers by customer no
Access path maint opt : IMMED Unique? NO Dynamic select? NO
Key field details:
| No Format | ||
|---|---|---|
| ||
Logical view name : CUSMSTS1 Desc of logical view : Active customers by customer no Access path maint opt : IMMED Unique? NO Dynamic select? NO Key field details: Field Description A/D S/U/A |
...
CUSTNO_ _ _ _______ _ |
...
_ + _______ _ |
...
_ + + Select/Omit criteria: AND/OR SELECT/OMIT Field Operation(s) |
...
SELECT____ |
...
ACTIVE__ COMP( |
...
EQ 'Y')_____ |
Example 4
Create a logical view/file called CUSMSTS2 that will order CUSMST by AMTDUE. Only records with CREDIT greater than 100000 and AMTDUE greater than or equal to 20000 are to be visible via this view. Records are to be ordered from highest amount due to lowest amount due. Use the dynamic select feature as well.
| No Format | ||
|---|---|---|
| ||
Logical view name : CUSMSTS2 |
...
Desc of logical view : High credit, large debt customers |
...
Access path maint opt: IMMED Unique? NO Dynamic select? YES |
...
Key field details: |
...
Field Description A/D S/U/A |
...
AMTDUE__ D S |
...
________ _ _ + |
...
________ _ _ + |
...
+ |
...
...
Select/Omit criteria : |
...
AND/OR SELECT/OMIT Field Operation(s) |
...
SELECT___ |
...
CREDIT__ |
...
COMP(GT 100000)______ |
...
AND _________ |
...
AMTDUE__ |
...
COMP(GE 20000)_______ |
Note that since no entry was made in the SELECT/OMIT column for the second select/omit statement the 2 statements are ANDed together. In this case only customers with CREDIT greater than 100000 AND AMTDUE greater than or equal to 20000 are selected.
Example 5
Create a logical view/file called CUSMSTS3 that will order CUSMST by CREDIT. Only records with CREDIT greater than 100000 or AMTDUE greater than or equal to 20000 are to be visible via this view.
| No Format | ||
|---|---|---|
| ||
Logical view name : CUSMSTS3 |
...
Desc of logical view : High credit or large debt customers |
...
Access path maint opt : IMMED Unique? NO Dynamic select? NO |
...
Key field details: |
...
Field Description A/D S/U/A |
...
CREDIT_ _ _ |
...
_______ _ |
...
_ + |
...
_______ _ |
...
_ + |
...
+ |
...
...
Select/Omit criteria: |
...
AND/OR SELECT/OMIT Field Operation(s) |
...
SELECT___ |
...
CREDIT__ |
...
COMP(GT 100000)_______ |
...
OR SELECT___ |
...
AMTDUE__ |
...
COMP(GE 20000)________ |
Note that since an entry was made in the SELECT/OMIT column for the second select/omit statement the 2 statements are read together. In this case only customers with CREDIT greater than 100000 OR . AMTDUE greater than or equal to 20000 are selected.
Example 6
Create a logical view/file called CUSMSTS4 that will order CUSMST by STATE and CUSTNO. Only records with a STATE of NSW, VIC or QLD are to be visible via this view.
| No Format | ||
|---|---|---|
| ||
Logical view name : CUSMSTS4 |
...
Desc of logical view : East coast customers by state, cust |
...
Access path maint opt : IMMED Unique? NO Dynamic select? NO |
...
Key field details |
...
Field Description A/D S/U/A |
...
STATE__ _ _ CUSTNO_ _ _ _______ _ |
...
_ + |
...
+ |
...
...
Select/Omit criteria: |
...
AND/OR SELECT/OMIT Field Operation(s) |
...
SELECT_____ STATE___ |
...
COMP(EQ 'NSW')_______ |
...
OR SELECT_____ STATE__ |
...
_ COMP(EQ 'VIC')_______ |
...
OR SELECT_____ STATE__ |
...
_ COMP(EQ 'QLD')_______ |
...
...
An identical condition can be made using the VALUES keyword: |
...
Select/Omit criteria |
...
AND/OR SELECT/OMIT Field Operation(s) |
...
SELECT_____ STATE |
...
_ VALUES('NSW' |
...
'VIC' |
...
'QLD') |
...
|
Example 7
Create a logical view/file called CUSMSTS5 that will order CUSMST by CUSTNO. Only records with an ACCTYP value of 2, 3 or 4 are to be visible via this view.
| No Format | ||
|---|---|---|
| ||
Logical view name : CUSMSTS5 |
...
Desc of logical view : Customers with type 2, 3 or 4 accounts |
...
Access path maint opt : IMMED Unique? NO Dynamic select? NO |
...
Key field details: |
...
Field Description A/D S/U/A |
...
CUSTNO_ _ _ _______ |
...
_ _ + |
...
_______ _ |
...
_ + |
...
Select/Omit criteria: |
...
AND/OR SELECT/OMIT Field Operation(s) |
...
SELECT____ ACCTYP__ |
...
_ COMP(EQ 2)____ |
...
OR SELECT____ ACCTYP__ |
...
_ COMP(EQ 3)____ |
...
OR SELECT____ ACCTYP__ |
...
_ COMP(EQ 4)_____ |
...
The select /omit condition could also be expressed as: |
...
SELECT____ ACCTYP___ COMP(GE 2)_______ |
...
AND ACCTYP___ COMP(LE 4)________ |
...
or : |
...
SELECT____ ACCTYP__ VALUES(2 3 4)_____ |
...
or : |
...
SELECT____ |
...
ACCTYP___ |
...
RANGE(2 4)_____ |
...
|