Summary
This document will provide an example of how to use a relate within an AML cursor.
Procedure
- Create two INFO tables containing key items that can be used to relate the tables together. The key item in the following sample tables is ID.
Arc: list countrydata
Record COUNTRY ID
1 USA 10
2 Canada 20
3 Mexico 30
Arc: list citydata
Record ID CITY
1 10 Los Angeles
2 20 Toronto
3 30 Mexico City
4 10 New York
5 20 Ottawa
6 30 Tijuana
How to create the sample tables:
Arc: tables
Tables: define countrydata
1
Item Name: country 10 10 c
11
Item Name: id 3 3 i
14
Tables: add
1
COUNTRY: USA
ID: 10
2
COUNTRY: Canada
ID: 20
3
COUNTRY: Mexico
ID: 30
Tables: define citydata
1
Item Name: id 3 3 i
4
Item Name: city 15 15 c
19
Tables: add
1
ID: 10
CITY: 'Los Angeles'
2
ID: 20
CITY: Toronto
3
ID: 30
CITY: 'Mexico City'
4
ID: 10
CITY: 'New York'
5
ID: 20
CITY: Ottawa
6
ID: 30
CITY: Tijuana
Tables: quit
- Create a relate between the two tables.
Arc: relate add
Relation Name: cityrel
Table Identifier: citydata
Database Name: info
INFO Item: id
Relate Column: id
Relate Type: linear
Relate Access: ro
- The relationship between COUNTRYDATA and CITYDATA is a one-to-many relationship. Each record in COUNTRYDATA has multiple related records in CITYDATA. The goal of this example will be to list each country and the multiple cities that are related to it.
Copy and paste the following script and save it as an AML file.
/* Usage: CURSOR <cursor> DECLARE <cover> <feature_class> {RO | RW}{logical_expression}
/* Usage: CURSOR <cursor> DECLARE <info_file> <INFO> {RO | RW} {logical_expression}
/* Usage: CURSOR <cursor> <OPEN | CLOSE | INSERT | DELETE | REMOVE>
/* Usage: CURSOR <cursor> <NEXT | FIRST | PREVIOUS | record_number>
/* Usage: CURSOR <cursor> RELATE <relate> <NEXT | FIRST | INSERT | DELETE>
/* Declare a cursor to loop through each
/* record of the COUNTRYDATA info file.
CURSOR cur1 DECLARE countrydata INFO rw
CURSOR cur1 open
/* The outside DO LOOP will continue to process records until the last record
/* has been read. When the last COUNTRYDATA record is reached, the variable
/* %:cur1.AML$NEXT% will be set to FALSE, and the loop will exit.
&do &while %:cur1.AML$NEXT%
/* For the current COUNTRYDATA record being processed
/* in the outside loop, loop through each of the related
/* records in the CITYDATA info file. When the last
/* related record is processed, %:cur1.cityrel//AML$NEXT%
/* will be FALSE, and the inner DO LOOP will pass control
/* back to the outer DO LOOP.
&do &while %:cur1.cityrel//AML$NEXT%
/* Display to the command line the current COUNTRY,
/* ID and related CITY value being processed.
&type %:cur1.country% %:cur1.id% %:cur1.cityrel//city%
CURSOR cur1 RELATE cityrel NEXT
&end
CURSOR cur1 NEXT
&end
/* Once all COUNTRYDATA records have been
/* looped through, close and remove the cursor.
CURSOR cur1 CLOSE
CURSOR cur1 REMOVE
&return
- Run the AML.
Arc: &run sample.aml
Feature cursor CUR1 now declared using file
COUNTRYDATA with Read Write access
Feature cursor CUR1 now opened with
3 reselected records out of 3
Fetched record 1 for Feature cursor CUR1
USA 10 Los Angeles
USA 10 New York
No more related records for relate CITYREL
Fetched record 2 for Feature cursor CUR1
Canada 20 Toronto
Canada 20 Ottawa
No more related records for relate CITYREL
Fetched record 3 for Feature cursor CUR1
Mexico 30 Mexico City
Mexico 30 Tijuana
No more related records for relate CITYREL
No more records to fetch for Feature cursor CUR1
Feature cursor CUR1 now removed