You are here: LANSA for i User Guide > 3. Files > 3.12 Logical Views/Files Associated with File Definition > Examples - Create Logical Vew
Examples - Create Logical Vew

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:

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):

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:

    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.

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.

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.

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.

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)______