Syntax @Relate(, (Read|Write)) Arguments Identifies the relation definition to execute. Relations are defined by Relate RELATION_DEF statements. Range: Must Be Defined Indicates the direction of the relation. Read means that @Relate will read data from the relational tables into the feature when it is invoked on a destination transfer specification line (or on a factory input or output clause). It will write data when it is invoked on a source line. Note: In previous FME versions, this was called DestReadSrcWrite. Write means the opposite. Note: In previous FME versions, this was called DestWriteSrcRead. Range: Read | Write Configuration Relate RELATION_DEF TABLE [UNIQUE([,]+) | NOTUNIQUE] [JOIN TO ]+ [SORT_BY [ (NUMERIC|ALPHA)]+] [SORT_DIRECTION (ASCENDING|DESCENDING)] [MATCHED_RECORDS_ATTR ] [LOG_ROWS] [TRANSFER TO ]* [TRIM_TRAILING (YES|NO)]* [REJECTABLE] []* Tip: The JOIN clause identifies the key fields that must match in both the FME feature and the table row. The TRANSFER clause identifies fields which should be copied between the FME feature and the table row. If no TRANSFER clauses are specified, then all the fields are copied from the table row to the feature. The name of the relation defined by the configuration statement. This name is used as the argument when the @Relate function is called. Range: Character String Defines the cardinality of the relation. 1:0..1 indicates that there is either 0 or 1 row in the table for each feature. 1:0..1+ indicates that there may be 0 or more matching rows but only the first matched row will be extracted. 1:1 implies that there is exactly 1 row in the table for each feature. 1:M implies that there is 0 or more rows in the table for each feature. 1:M relations make use of feature attribute lists to store the attribute data in FME features. When a 1:M relation is used, the optional SORT_BY and SORT_DIRECTION clauses specify the order that the rows will be added to the attribute list. Range: 1:0..1 | 1:0..1+ | 1:1 | 1:M The logical name of the table used by this relation. The table's structure and location are defined by TABLE_LOCATION and TABLE_DEF statements. Range: Character String Table field names are used in JOIN and TRANSFER clauses to identify the fields in the table that are joined or transferred to the corresponding fields in the FME feature. As well, in these clauses an attribute function may be used in place of the table field name. The join between attribute values and table fields takes place after all trailing space characters have been removed. This sometimes requires a TRIM operation to be performed on the database side, which can defeat the database indexes in some cases. If it is known that the database fields contain no trailing spaces, specifying TRIM_TRAILING NO on the relation definition may provide a performance boost. Range: The name of any field in the table, or an attribute value function. The UNIQUE clause requires a list of table field names. The values of these fields are considered to form a unique key that the FME uses when outputting to the table. This is used by some 1:1 relations to avoid writing duplicate records to tables. 1:M relations by definition are NOTUNIQUE. An FME feature attribute name. If the relation is 1:M, and the feature attribute name contains an empty set of parenthesis {}, the parenthesis will be filled with the current matching row number. Rows are numbered starting at 0. In JOIN and TRANSFER clauses, an attribute function may be used in place of the feature attribute name. Range: The name of any FME feature attribute, or an attribute value function. One or more feature functions may be specified at the end of a relation definition. These functions are called after the JOIN and TRANSFER processing is complete. If the relation is 1:M, then these functions will be called once for each row that is processed. Range: Any Valid Feature Function. Any feature function can be called, including other @Relate functions. This allows relations to cascade across several tables. Specifies the name of an attribute that will receive the number of database records that the feature matched. This value can be used in a a 1:0..1 relation to determine whether a feature matched a record in the database, or in 1:0..1+ or 1:M relation to determine how many database records a feature matched. Range: Any valid attribute name REJECTABLE Rejects features if this keyword is specified. The following fme_rejection_code values can occur INVALID_NUMBER_OF_MATCHES Range: N/A Relate TABLE_DEF [DATABASE_SERVER_TYPE ] [DATABASE_USER_NAME ] [DATABASE_PASSWORD ] [DATABASE_NAME ] [DATABASE_USE_SSPI ] [DATABASE_PORT ] [DATABASE_VERSION ] [ ]+ The logical name of the table being defined. This name is used in RELATION_DEF and TABLE_LOCATION statements. Range: Character String The type of the table being defined. ASCII:Repeating free form ASCII CAT:Column-Aligned Text (FORTRAN-style file) CSV:Comma-separated value file DBF: dBASE III file DATABASE: Connection to a live database For reader keywords, see the CSV, DBF and Database chapters of the FME Readers and Writers manual. Range: ASCII|CAT|CSV|DBF|DATABASE The type of server being used. Range: ODBC|ORACLE|ORACLE8|MDB|POSTGRES|POSTGIS|DB2|EXCEL|MSSQL_ADO The username that accesses the database. Some ODBC sources ignore this value. Range: Character String The password that accesses the database. Range: Character String To use Windows Authentication instead of username and password with SQL Server, set this parameter to YES. Range: YES | NO This is the name of the database. ODBC, ORACLE, and ORACLE8 databases ignore this setting. For ArcSDE, this is the ArcSDE dataset. Range: Character String This is the TCP/IP port for access to a remote PostGIS/PostgreSQL database. ODBC, ORACLE, and ORACLE8 and many other databases ignore this setting. For ArcSDE, this is the ArcSDE instance. Range: Character String For ArcSDE, this specifies the ArcSDE version. See the VERSION_NAME directive in the ESRI Legacy ArcSDE Reader/Writer documentation for more details. Range: Character String The name of the field. For DBF files, the field names must be in uppercase and 10 characters or less in length. Certain table types may reserve specific field names for special purposes. Range: Character String The type of the field. The allowable field types depend on the type of database file. If the tableType is ASCII, CAT, CSV, or DBF, refer to the Relational Table Reader/Writer in the FME Readers and Writers manual for the allowable field types. If the tableType is DATABASE, refer to the table below. Certain table types may reserve specific field types for special purposes. number(,) Number fields store floating point values. The width parameter is the total number of characters allocated to the field, including the decimal point. The decimals parameter controls the precision of the data and is the number of digits to the right of the decimal. char() Character fields store fixed length strings. The width parameter specifies the number of characters that can be stored. When a character field is written, it is right-padded with blanks, or truncated, to fit the width. When a character field is retrieved, any padding blank characters are stripped. logical Logical fields store TRUE/FALSE data. Data read or written from/to such fields must always have a value of either true or false. date Date fields store dates as character strings with the format YYYYMMDD. Range: Varies depending on tableType. Relate TABLE_LOCATION The logical name of the table whose location is being set. The table's structure is defined by a TABLE_DEF statement. For Open DataBase Connectivity (ODBC) data sources, this tableId is also the name of the table in the database. Range: Character String The location of the table. If the tableType specified in the corresponding TABLE_DEF statement is not DATABASE, this field specifies the table location on the file system. If the tableType is DATABASE and the DATABASE_SERVER_TYPE is ODBC, this field specifies the ODBC data source name. If the DATABASE_SERVER_TYPE is ORACLE or ORACLE8, then it is the SQL*Net service name of the database. If the DATABASE_SERVER_TYPE is MDB or EXCEL, then it is the location of the database file on the file system. For ArcSDE, this is the server name. Range: Character String Relate CACHE_SIZE Specifies the size for the local query result cache, in rows. Results returned from the database are stored locally in order to speed up queries against remote databases. The default cache size is 5000. If the number of rows that will be joined is known before translation, this number may be changed to match it. This parameter is only available if tableType is DATABASE. Range: Integer Relate PREFETCH_QUERY Range: Any valid fully formed SQL query, which may include references to FME feature attributes via the "value of" (&) operator. Specifies a query of the database, the results of which will be added to the cache of database information. The cache is intended to speed up subsequent access of the database during the read operations required to perform a join. By default, no prefetch query will be performed. In addition, the prefetch query will be ignored if the tableType is not Database. The PREFETCH_QUERY specifies an SQL SELECT statement which will be used to query the database for rows that will likely be requested later. The results of the query will be added to the database cache, which will reduce the number of individual queries placed against the database as features request their corresponding rows. Because performing a database query round-trip can be expensive, using this mechanism to preload a number of result rows into the local database cache can result in substantial performance improvements. If the number of rows in the database is relatively few, then a query like: SELECT * from TABLENAME would result in all the records from the database being extracted, in a single query, and held locally. Then as features have @Relate executed on them, no visit to the database is needed to satisfy the query. If the number of records in the table is excessive, it is not practical to cache them all locally during a translation run. In this case, a subset of the records can be extracted by adding a WHERE clause to the SELECT statement: SELECT * from TABLENAME where BASENAME = '92b034' In this case, it is known that for the entire run, all the features will be related to rows in the database where the BASENAME column had the value 92b034, and so only these records should be held in the local cache. The prefetch query can include references to feature attributes. This is typically used to preload the cache with rows likely related to a set of features who share a common attribute. Whenever the attributes referenced in the prefetch query change, the cache is cleared and the prefetch query will be re-executed. Therefore, it is important that when this facility is used, the features arrive in an order that minimizes the changes in their values. The FME's 'value of' operator (&) is used to de-reference feature attribute values in the query. For example, a prefetch query like: SELECT * from TABLENAME where BASENAME = '&igds_basename' would initially cache all the records from the table whose BASENAME column had a value matching the value held in the igds_basename attribute of the first feature encountered by @Relate. It would also cause the cache to be reloaded each time the igds_basename attribute changed from one feature to the next. A note about prefetch queries: Normally, a prefetch query exists to satisfy potential database matches; a request to the database will still be required if a match is not found in the prefetch cache. It is also possible to mark a prefetch query as "exhaustive", which means that it describes all possible matches; in this case, a failure to form a match in the resulting cache means that there is no possible match in the database, and thus a round trip to the database is avoided. Any query may be marked as being exhaustive by prepending a "plus" sign to the query. (e.g. "+SELECT * from TABLENAME where BASENAME = '&igds_basename'") Additionally, queries of the form "SELECT * FROM " are always assumed to be exhaustive. Description The @Relate function combines relational data held in auxiliary databases with FME features. The function can be configured to perform simple single- table joins, or complex, multi-table, multi-record joins. The relational data may be held in one or more of the supported file formats, or in a live database. The @Relate function is a feature function that adds data from a relational table to the FME feature when it is reading. When it is writing, it extracts data from the FME feature and writes it to a relational table. This function requires considerable configuration before it may be invoked. The location of the table must be specified using the TABLE_LOCATION configuration option. The structure of each table used by the relation definition must be specified using the TABLE_DEF configuration option. The relation itself must be defined. The relation operates on a logical table name and is independent of the actual physical structure of the table. The relation is defined using the RELATION_DEF configuration option. Relations may be one-to-one (1:1), in which case a single record is extracted from the table and added to the FME feature. If there is no matching record or there is more than one matching record, then the translation will be terminated. A one-to-zero or one (1:0..1) relation is similar to a 1:1 relation, but when no matching database record is found, no action is taken. If more than one matching record is found then the translation is terminated. A one-to-zero or one plus (1:0..1+) relation may match to multiple records in the database, but only attributes from the first matching record are extracted. No error is returned if no matching record or more than one matching records found. One-to-many (1:M) relations add 0 or more table records to the FME feature. These records may be sorted before being added to the FME feature, and for debugging purposes they may be logged. The table field and feature attribute names specified in the JOIN clause of the relation definition select the record(s) from the table. The FME feature must have previously had values for any attributes named in a JOIN clause. Once a record is selected, the TRANSFER clause moves the table fields into the named FME feature fields. Once this is done, the feature functions, if any, attached to the relation are executed. The most common feature function used is another @Relate call, to allow the relation to pull information from several tables in the FME feature. Attribute value functions can be used in place of table field and feature attribute names in JOIN and TRANSFER clauses. This allows attribute value functions to be used to calculate field values. The values of table fields and feature attributes can be passed to commands by putting the value-of operation (an ampersand &) in front of their names. Depending on their placement and the direction of @Relate, the attribute functions operate according to the following rules: When the relation is executed, the left (table) side of the TRANSFER clause is the source of attribute values. Any attribute functions on this side are executed. The right (feature) side of the TRANSFER clause is the destination of the table values. The value stored in the feature attribute is the result of the execution of the function. The JOIN clause works in the opposite way when @Relate is executed in the forward direction. This is because the JOIN looks in the table for the values specified by the feature. In this case, then, the right-hand (feature) side of the JOIN clause is the source of the key values. The left (table) side of the JOIN clause is the destination of the key values. Any attribute functions on this side are executed. The value computed is then searched for in the table to identify the record to be extracted. When the relation is 1:M, several rows may be pulled from the relational table. The TRANSFER clauses and feature functions of the relation are executed for each row. Any attribute list field names on the feature side are expanded to include the row number in their {}. Rows are numbered starting at 0. Tip: Use the Relate option on FME_DEBUG to log all the rows read by a query. A cache is used to improve efficiency when repeatedly fetching the same rows from the database. This cache is used automatically when reading rows, but may also be prepopulated by an SQL query of the database, called a prefetch query. By using a prefetch query to retrieve a number of database records in a single query, performance will be improved by avoiding database round-trip queries. See the PREFETCH_QUERY in the configuration section above.