Using database views |
|
Release 9.3 |
NOTE: Applies to geodatabases created with an ArcGIS Server Enterprise license only
Essentially, views are stored queries that select data from specified tables. The difference between a view and a regular query executed by a user or client application is that views are stored in the database, and the underlying tables can be indexed to improve performance. Since views are stored in the database, view functionality varies from one database management system (DBMS) product to another. These differences are described in the DBMS-specific topics listed here: With ArcSDE, it is possible to define views against a single table, between two feature classes, or between a feature class and a table, or you can create more complex views containing subqueries or spanning databases. ArcSDE views are created with the sdetable –o create_view command. A spatial ArcSDE view includes the spatial column of a feature class. This view appears as a feature class to ArcSDE clients.NOTE: When you create a view on a versioned dataset, you will only see the business table and not the edits in the delta tables.
Views can be created using the sdetable administration command. The syntax of sdetable –o create_view issdetable –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]
NOTE: You must have proper permissions in the database to create a view to execute this command.
You must list the columns (–c) you want in the view as well as a WHERE clause (–w) within the sdetable command. To create a spatial view, be sure to specify the spatial column with the –c option column list. To remove a view, you can use the sdetable –o delete command. Each DBMS product also provides ways to create database views, usually by issuing SQL commands. Since this is DBMS specific, consult the documentation provided with your DBMS product. Views created outside ArcSDE need to be registered with the geodatabase before they can be used by an ArcGIS client. Specific permissions are needed to allow a user to create a view; for example, the user must be granted CREATE VIEW privileges in Oracle or SQL Server databases. Also, with some DBMSs, if you create a view on a feature class or table for which you are not the owner, you will not be able to grant other users rights to the view unless the owner of the underlying tables has granted you permission to grant privileges to other users. Tipssdetable –o create_view –T view_parcel_owner –t parcel
–c owner,site_address –u manager –p fl
sdetable –o create_view –T view_adrs_owner –t address,view_parcel_owner
–c address.objectid,address.site_address,address.shape, view_parcel_owner.owner,
view_parcel_owner.site_address
–w "address.site_address = view_parcel_owner.site_address" –u manager –p fl