Outer Joins

Using Water Customers as an Example of Doing Outer Joins

sdetable -o delete -t WaterCustomerView3 -i sdeprod -u RHC -p rhgisadmin -N

sdetable -o create_view -T WaterCustomerView3 -t TaxParcelWithAllAddresses -c

TAXPARCELWITHALLADDRESSES.Shape,RHC.TAXPARCELWITHALLADDRESSES.KEYPIN,

RHC.TAXPARCELWITHALLADDRESSES.PIN,RHC.TAXPARCELWITHALLADDRESSES.OBJECTID 

-i SDEPROD -u RHC -p rhgisadmin

 THEN GO TO GEORGE\PROD TO DO THE OUTER JOINS

Workaround: don't use sdetable to do the join-ing... 

Use the statement: 

sdetable -o create_view -s SDESERVER -D SDE_Testing -u ***** -p ***** -i sde -T Some_View -t "TableA" -c "*" 

...to get the dbms view, f & s views created. This command will create the dbms view listing all the columns in the table using fully qualified names (not the '*' like you might expect). 

Then go in with SQL and ALTER VIEW to make the joins you need - use fully qualified nameing everywhere: 

ALTER VIEW Some_View AS SELECT TableA.FieldA, TableA.FieldB, TableA.shape, TableB.SomeField FROM TableA LEFT OUTER JOIN TableB ON TableA.PK = TableB.FK 

...run that and it seems to work - at least, it shows up in ArcMap/Catalog/IMS. 

Also, if you want to span a view across tables in different physical databases just make sure the sde user has select permission on the table and extend the fully qualified pathing to include the database name. 

ie: ALTER VIEW Some_View AS SELECT TableA.FieldA, TableA.FieldB, TableA.shape, AnotherDB..TableB.SomeField FROM TableA LEFT OUTER JOIN AnotherDB..TableB ON TableA.PK = AnotherDB..TableB.FK 

Big thanks to GZ from ESRI Tech support for the tip on the fully qualified name bug. That was the key that lead to this particular solution!!