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

808
5
10-23-2018 12:09 PM
IndyHurt1
New Contributor II

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.

5 Replies
ManviLather1
Occasional Contributor

Hello Indy,

--Registering  the view with the geodatabase would resolve the issue.

IndyHurt1
New Contributor II

Hi, 

I’ve tried that and it hasn’t worked but I will try again. I noticed something else yesterday, too. When trying to include the alias, ArcCatalog is taking what I think I’ve configured correctly as an alias and using it as the actual field name. As you can imagine, that’s causing problems because what I’m writing as aliases have spaces and special characters.

Alias being used as field name.

0 Kudos
AndresCastillo
MVP Regular Contributor

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...

0 Kudos
IndyHurt1
New Contributor II

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 - 

  1. Create db view in ArcCatalog without alias designations in sql
  2. Register view w/ db
  3. Add the field aliases via model builder 

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

0 Kudos
AndresCastillo
MVP Regular Contributor

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.

0 Kudos