How to use a proxy table with locally different column names?

[VolkerBarth]

When creating a proxy table to an existing remote table via CREATE EXISTING TABLE MyLocalTable(...) AT ..., I surely

• can choose a different name for the local table than for the remote table,
• can restrict the local column list to a subset of the remote columns,
• can choose different data types for the local columns as long as they are compatible to the remote ones,
• cannot add "local-only columns" (for obvious reasons).

However, what I like to be able to do - for reasons of compatibility - is using a different column name for (some of) the local columns, i.e. a mapping between MyRemoteTable.ColumnX and MyLocalTable.ColumnY.

Unfortunately, that seems not possible with proxy tables, as the syntax of CREATE EXISTING TABLE does not allow for that - and I guess the system catalog does not have a "place" for aliased remote columns, either.

---

Question:

Is there any better alternative than to use the proxy table with the (undesired) original columns names and use a view based on the proxy table to map thoses column names to the desired ones, such as

create view MyLocalView as
   select ..., ColumnX as ColumnY, ... from MyLocalTable

[MarkCulp · Accepted solution]

You are correct. You cannot rename a column when using CREATE EXISTING COLUMN to access a remote table and this is clearly documented as the first bullet in the Remarks section on the page that you linked.

So using a view is the only method that I can think of that would accomplish what you are wanting to do - the view could exist either in the remote database or in the local database. A use of a view to rename a column has very small performance impact (likely not even measurable) in SQL Anywhere.

[VolkerBarth · Accepted solution]

Thanks for the clarification - so am I right that the working view approach would be seen as sufficient, and as a consequence there won't be much sense in asking for an enhancement to supply a "column alias" matching facility for CREATE EXISTING TABLE?

[Breck_Carter · Accepted solution]

Yes... keep your powder dry, save it for another occasion :)