topic Re: What is the proper SQL syntax for including a field alias when creating a view in ArcCatalog? in ArcGIS Enterprise Questions

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

IndyHurt1 — Tue, 23 Oct 2018 19:09:09 GMT

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.

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

ManviLather1 — Wed, 24 Oct 2018 15:08:31 GMT

Hello Indy,

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

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

IndyHurt1 — Wed, 24 Oct 2018 15:24:25 GMT

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.

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

AndresCastillo — Wed, 24 Oct 2018 19:31:27 GMT

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

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

IndyHurt1 — Wed, 24 Oct 2018 19:56:47 GMT

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.

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

AndresCastillo — Fri, 26 Oct 2018 13:35:48 GMT

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.