here is what I’m trying to do- I have a table with lots of columns and want to create a view with one of the column reassigned based on certain combination of values in other columns, e.g.
Name, Age, Band, Alive ,,, <too many other fields)
And i want a query that will reassign one of the fields, e.g.
Select *, Age =
CASE When "Name" = 'BRYAN ADAMS' AND "Alive" = 1 THEN 18
ELSE "Age"
END
FROM Table
However, the schema that I now have is Name, Age, Band, Alive,,,,<too many>,, Age
I could use ‘AS’ in my select statment to make it
Name, Age, Band, Alive,,,,<too many>,, Age_Computed.
However, I want to reach the original schema of
Name, Age, Band, Alive.,,,, where Age is actually the computed age.
Is there a selective rename where I can do SELECT * and A_1 as A, B_1 as b? (and then A_1 completely disappears)
or a selective * where I can select all but certain columns? (which would also solve the question asked in the previous statement)
I know the hacky way where I enumerate all columns and create an appropriate query, but I’m still hopeful there is a ‘simpler’ way to do this.
Advertisement
Answer
Sorry, no, there is not a way to replace an existing column name using a SELECT * construct as you desire.
It is always better to define columns explicitly, especially for views, and never use SELECT *. Just use the table’s DDL as a model when you create the view. That way you can alter any column definition you want (as in your question) and eliminate columns inappropriate for the view. We use this technique to mask or eliminate columns containing sensitive data like social security numbers and passwords. The link provided by marc_s in the comments is a good read.