Create a spatially enabled view

Summary

Views can be created with the 'create_view' option for sdetable. The views created are stored in the relational database management system (RDBMS) just like any other view created with a Structured Query Language (SQL) editor. Views can be created that join several tables together, up to the limit imposed by the RDBMS. However, just as ArcSDE imposes the limit of one spatial column per table, that limit also applies to views created by the 'create_view' operation.

Procedure

1. Use the 'sdetable' command at the SDE Server command prompt. The 'sdetable' command has an option at ArcSDE 8.x to allow server-side views to be delivered as ArcSDE layers.
   
   The syntax is:
   
   

   Code:
   sdetable -o create_view  
     -T <view_name> 
     -t <table1,table2...tablen>
     -c <table_col1,table_col2...table_coln>
    [-a <view_col1,view_col2...view_coln>]
    [-w <"where_clause">]
    [-i <service>]
    [-s <server_name>]
    [-D <database>]
     -u <DB_User_name> 
    [-p <DB_User_password>] [-N] [-q]
   
   where
     -T <new-view-name>
     -t <tables-to-join>
     -c <unambiguous-join-column-names>
     -w <where-clause-for-join>
     -u <sde-username>
     -p <sde-password>
   
   Example:
   Sdetable - o create_view -T world_view ^ 
     -t "world, countries" ^
     -c "countries.shape, world.population, world.gnp, countries.name" ^
     -a "population, Gross_National_Product, Name, Feature" ^
     -w "world.cname = countries.name" ^
     -u av -p mo
   

   
   
   If a 'shape' column is included, the view is automatically added as a layer to the database. If the shape column is not supplied, a view of a table results.
   
   

   Note:
   At ArcSDE 8.0.2, for the view to appear as a feature class, the shape field must be the first column in the -c argument. This is not the case at ArcSDE 8.1.

   
   
2. Use the delete operation to delete a view created by the 'create_view' operation. In this case, the delete operation removes the view. The delete operation does not drop the underlying business tables that the view was accessing.
   

   Code:
   sdetable -o delete -t world_view -u av -p mo
   

   
   
3. To create views that can be used through ArcGIS desktop applications, do the following:
   
   Use 'sdetable -o create_view' to create the view. Include the ObjectID field from the feature class in the view's field. This ensures that the view is registered with the geodatabase correctly.
   
   If the spatial column is included in the create_view statement, an entry is made to the SDE.LAYERS table, and consequently the view is treated as a spatial view.
   
   NOTE
   
   
   · Spatial views are read only. Edits cannot be made to a spatial view.
   
   · If using versioning, note that the spatial view is only on the base table. It does not show changes that are held in the delta tables, nor is it a view of the default version.
   
   · If creating a view using this method, use sdetable -o delete to delete the view so that the ArcSDE geodatabase tables are cleaned up.
   
   · Views containing database links are untested and, therefore, unsupported. They may or may not work.
   
   
   To create a very complex view, use the 'sdetable -o create_view' statement. This creates the very basic view, containing the shape column and the ObjectID column at the minimum. Once this is working, use Oracle tools (DBA Studio, Enterprise Manager, or SQL) to replace the view with a new view containing the more complex structure.
   
   The view that ArcSDE creates is an Oracle view. Ensure that the ObjectID and the shape column are included in the replaced view.