Creating a geographic view in SDE

There are two ways to create a view:
Either via ArcSDE command line commands or ArcCatalog toolbox tool in ArcCatalog.

ArcCatalog

Using ArcCatalog is pretty straightforward:
Open the toolbox in ArcCatalog -> Data Management -> Layers and Table views -> Make Table View, and follow the wizard (for more information, check out the ArcGIS 9.2 Desktop Help.

ArcSDE

To create a view, use the sdetable command:

sdetable -o create_view 
		-T <view name> 
		-t <table list separated by comma> 
		-c <column names from the tables separated by comma> 
		-w <where condition> 
		-u <user name / schema name> 
		-p <password>

Example

We have a layer named street_lights_g which has a foreign key to a table called streets. We need a view to contain the name of the street for each street light (so it can be displayed when you perform identification.
To do so, we use this command:

sdetable -o create_view 
		-T street_lights_v
		-t street_lights_g,streets
		-c street_lights_g.shape,street_lights_g.objectid,streets.name
		-w "street_lights_g.street_code = streets.code"
		-u myusername 
		-p ...

After running the command the view will be seen in the desktop tools like a regular layer. You can add it like a regular layer to a mxd, perform queries etc...

I recommend to check out the spatial index of the view, to see if it exists (right click on the view in ArcCatalog -> Properties -> Indexes).
If all the grid values are 0 then you need to recalculate the index using the recalculate button, or enter the grid values manually with the Edit button.

Troubleshooting

Problem: DBMS view exists

When running the command the following error message is received:

Error : DBMS view exists (-238).
Error : Unable to create view <view name>

Cause

The schema in which you tried to create the view, already contains a view with the same name.

Solution

Delete the view from the schema using ArcCatalog.
Why? When a geographic view is created it will be registered in the sde.layers table. If the existing view is a geographic view, and you delete it from the database via console, the table will still stay registered, and you will not be able to recreate the view.

Problem: Table not registered

When running the command the following error message is received:

Error: Table not registered (-220).
Error: Unable to fetch registration for table <view name>

Cause

Geographic view was deleted from DB console, and is still registered in the sde.layers table.

Solution

Create a view with the same name via DB console (pl/SQL developer, SQL server management console).
Delete the view with ArcCatalog.
Rerun the command.