What is the proper SQL syntax for including a field alias when creating a view in ArcCatalog?

Question

The data is stored in Microsoft SQL Server.  The query tested in Microsoft Management Studio is valid, but ArcCatalog and ArcMap are no longer tolerating the spaces and special characters in the aliases.

ArcGIS & ArcCatalog 10.5

Microsoft SQL Server 2017

I do have ArcGIS & ArcCatalog 10.6 on another VM... haven't tried there.

I also have a support ticket.  Thought I'd see if the community had some ideas, too.

AndresCastillo · Answer

Spaces and special characters aren't allowed in field names, but they are acceptable in the alias.

While that may be true in ArcCatalog, I've found it not true when creating a view.

View sql queries do not like the spaces.

Great alterative you provided though.

IndyHurt1 · Answer

Thanks, Andres

For the screen shot above, the SQL was something like:

select tableName.current_period AS [CURRENT PERIOD],

Originally, the query didn't even have the "AS" in there and it worked fine, but that no longer works and I haven't been able to determine what has changed.  Now, what you see in the square brackets is treated as both the field name and the alias.  Spaces and special characters aren't allowed in field names, but they are acceptable in the alias.

As a work around, I've stripped the aliases from the SQL when creating the view in ArcCatalog and I've created a model builder model to use the Alter Field tool to add the aliases after the fact.  It's an extra step that allows the original spaces and special characters we've always had in our field aliases, but I'd prefer to return to including the aliases in the view definition.

New work flow is as follows -

* Create db view in ArcCatalog without alias designations in sql
* Register view w/ db
* Add the field aliases via model builder

Hoping to eliminate that last step we've recently had to add to the process.

AndresCastillo · Answer

Yes, you are right. The correct SQL syntax to use in arcCatalog is to not use spaces. You might try to replace the spaces with underscores. For example, SELECT fc.field as 'field_alias' from database.schema.featureclassname_evw as fc‍‍ To expand on what Manvi said, If you are using a database view, it may not like to participate with geodatabase objects. You might try to register the view with the geodatabase, starting at 10.5, or ArcGIS Pro. https://community.esri.com/groups/geodatabase/blog/2016/12/14/new-at-105-registering-a-database-view-with-the-geodatabase