How to union two polygon feature classes into one with "Ceate Database View" geoprocessing tool?

[WeiweiFanAB]

Hello all,

I'm having trouble using the "Create Database View" geoprocessing to create a view to union two polygon feature classes into one.

Due to the design, we have two feature classes represent two categories of polygons, but we need a view to display the polygon features from both feature classes, I used a very simple query to create this view for experiment:

Select Field_A1, Field_A2, Shape, ObjectID from Polygon_A

union all

Select Field_B1, Field_B2, Shape, ObjectID from Polygon_B 

It gave me an error of  ERROR_160195, an invalid SQL statement was used. I ran the single query for Polygon_A and B, both of them worked; Field_A1 and B1, Field A2 and B2 all have the same field type and length. It seems that this GP tool does not support "union".  

Has anyone met the same problem and what would be the best solution?

Thanks! 

[VinceAngelo]

Just for clarity, what data storage format do the two feature classes share?  If enterprise geodatabase, which RDBMS?

- V

[WeiweiFanAB]

Thank you for the response! I am not sure I understand the term "data storage format" here, if you meant the geodatabase the two feature class are stored in, they are in a file geodatabase on my local drive, and eventually they will be in a Enterprise environment using PostgreSQL; if you mean some backend file types inside of a gdb, I have no clue. 

[VinceAngelo]

Views in file geodatabase do not support UNION ALL joins -- the SQL implementation in FGDB is a subset of those used in actual databases. You should not expect file geodatabase to be a prototyping environment for PostgreSQL (they have little in common).

Note that even in PostgreSQL, UNION ALL could have issues due to non-unique rowid values (overlapping objectid ranges).

- V

[WeiweiFanAB]

Thank you for the clarification! 

When you said "Views in file geodatabase do not support UNION ALL joins", would it also apply to "Create Database View" geoprocessing tool? In other word, if in this GP tool, I setup the "Input Workspace" to the PostgreSQL database, would it be possible to use UNION ALL? Assuming that I have an unique id/index on the two feature classes with no id overlaps

Thank you @VinceAngelo 

[VinceAngelo]

Again, PostgreSQL and file geodatabase are different, and support different SQL implementations.
CreateDatabaseView uses the native SQL support. If it's not there, it wont work.

I'm not a fan of using UNION ALL joins -- too wasteful of resources. If the tables are so compatible,
they ought to be partitions of a single table, and then the singleton "tables" are views of the parent.

- V