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.
A database link can be created on the server itself through the Oracle client software using the following syntax:
create database link STRDATA_LINK connect to USER identified by PASSWORD using 'Oracle(2) database alias'
create synonym STRDATA_SYN for USER.STRDATA@STRDATA_LINK
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:
select * from STREETS, STRDATA_SYN where STREETS.ROAD_CODE = STRDATA_SYN.RD_CD
SDE layers cannot be accessed remotely using a database link and/or synonym.
Joining remote tables to an SDE layer requires the use of a synonym.
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:
select * from STREETS, STRDATA@STRDATA_LINK where STREETS.ROAD_CODE =
Although using the database link is an option, the synonym method is recommended.
Get help from ArcGIS experts
Download the Esri Support App