Join SDE layer attribute data to a remote database table

Summary

Currently, ArcView will not allow a DBTable or SDETable to be a destination table when contructing a join within ArcView (see DBTable.JointoVtab). To do this, the join must take place on the server. The following example describes how to join an SDE layer attribute table to a table on another database server. The example uses Oracle with SDE 3.0.1 and assumes that the user is connecting to the database server where SDE data resides.

Procedure

1. Create a database link on Oracle server 1.
   

   Note:
   A database link can be created on the server itself through the Oracle client software using the following syntax:
   

   Code:
   create database link STRDATA_LINK connect to USER identified by PASSWORD using 'Oracle(2) database alias'

   
   
2. Create a synonym on Oracle server 1 for the remote table.
   

   Code:
   create synonym STRDATA_SYN for USER.STRDATA@STRDATA_LINK

   
   
3. Join the SDE layer or table to the synonym.
   

   Note:
   This can be accomplished through Avenue, the Add Database Theme wizard, or the Theme Properties (where clause). The synonym will show up in the dialog box if the current user created the synonym. If the synonym is public, then it will not appear in the available tables (unless the current user created the public synonym) but can be entered in the From and Where sections. 
   
   The SQL statement for the example would appear as follows:
   

   Code:
   select * from STREETS, STRDATA_SYN where STREETS.ROAD_CODE = STRDATA_SYN.RD_CD

   
   Notes:
   

   Note:
   SDE layers cannot be accessed remotely using a database link and/or synonym.

   
   
   

   Note:
   Joining remote tables to an SDE layer requires the use of a synonym.

   
   
   

   Note:
   Attribute data can be joined using the database link directly. For example, the database link for the example above is referenced in a SQL statement as follows:
   

   Code:
   select * from STREETS, STRDATA@STRDATA_LINK where STREETS.ROAD_CODE = 
   STRDATA_SYN.RD_CD@STRDATA_LINK

   
   
   
   

   Warning:
   Although using the database link is an option, the synonym method is recommended.