These examples apply to creating an index or view within a table definition.
The table definition used in these examples is called CUSMST (Customer master table) and contains these columns:
|
Example 1
Create an index/table called CUSMSTV1 that will order CUSMST by NAME and allow generic searching by NAME:
Index name : CUSMSTV1
Desc of index : Customer master by customer name
Access path maint opt : IMMED Unique? NO Dynamic select? NO
Key column details
Column Description A/D S/U/A
NAME___ A _
_______ _ _ +
_______ _ _ +
+
Example 2
Create an index/table called CUSMSTV2 that will order CUSMST by STATE then POSTCD (i.e. POSTCD within STATE):
Index name : CUSMSTV2
Desc of index : Cust master by state and postcode
Access path maint opt : IMMED Unique? NO Dynamic select? NO
Key column details
Column 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 an index/table called CUSMSTS1 that will order CUSMST by CUSTNO. Only records with ACTIVE = 'Y' are to be "visible" when using this index:
Index name : CUSMSTS1
Desc of index : Active customers by customer no
Access path maint opt : IMMED Unique? NO Dynamic select? NO
Key column details:
Column Description A/D S/U/A
CUSTNO__ _ _
________ _ _ +
________ _ _ +
+
Select/Omit criteria:
AND/OR SELECT/OMIT Column Operation(s)
SELECT_____ ACTIVE__ COMP(EQ 'Y')______
Example 4
Create an index/table 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.
Index name : CUSMSTS2
Desc of index : High credit, large debt customers
Access path maint opt: IMMED Unique? NO Dynamic select? YES
Key column details:
Column Description A/D S/U/A
AMTDUE__ D S
________ _ _ +
________ _ _ +
+
Select/Omit criteria :
AND/OR SELECT/OMIT Column 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 an index/table 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.
Index name : CUSMSTS3
Desc of index : High credit or large debt customers
Access path maint opt : IMMED Unique? NO Dynamic select? NO
Key column details:
Column Description A/D S/U/A
CREDIT__ _ _
________ _ _ +
________ _ _ +
+
Select/Omit criteria:
AND/OR SELECT/OMIT Column 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 an index/table 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.
Index name : CUSMSTS4
Desc of index : East coast customers by state, cust
Access path maint opt : IMMED Unique? NO Dynamic select? NO
Key column details
Column Description A/D S/U/A
STATE___ _ _
CUSTNO__ _ _
________ _ _ +
+
Select/Omit criteria:
AND/OR SELECT/OMIT Column 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 Column Operation(s)
SELECT_____ STATE_ VALUES('NSW' 'VIC' 'QLD')
Example 7
Create an index/table 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.
Index name : CUSMSTS5
Desc of index : Customers with type 2, 3 or 4 accounts
Access path maint opt : IMMED Unique? NO Dynamic select? NO
Key column details:
Column Description A/D S/U/A
CUSTNO__ _ _
________ _ _ +
________ _ _ +
Select/Omit criteria:
AND/OR SELECT/OMIT Column 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)______
