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

asked 23 Mar '13, 18:22

Volker%20Barth's gravatar image

Volker Barth
40.1k361549819
accept rate: 34%

edited 23 Mar '13, 18:23


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.

permanent link

answered 23 Mar '13, 22:19

Mark%20Culp's gravatar image

Mark Culp
24.9k10141297
accept rate: 41%

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?

(24 Mar '13, 10:49) Volker Barth
Replies hidden
2

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

(24 Mar '13, 11:13) Breck Carter
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×438
×70
×5

question asked: 23 Mar '13, 18:22

question was seen: 2,264 times

last updated: 24 Mar '13, 11:24