Hi,

Sorry I'm a bit new at this...

I'm creating a view. I want to select everything in from a table, but i want one of the columns to have an alias name. The thing is the table is likely to change, and i don't want to have to keep re-writing the SQL. Is there a say to (for example):

CREATE VIEW `test` AS
SELECT *, `test_field` as `test_type` FROM `test_table`;

actually, i know that would kind of work, but i don't want the duplicated field name

the field name wouldn't be duplicated, but the column would be

the best way around your problem is not to use the dreaded, evil "select star"

Nooch, it may be worth noting that if you make a change to your test_table your view will not reflect this - even if you use SELECT *

decided to declare each field name, hopefully there won't be that many changes with the original table.

georgev, if the table column name changes my view won't be affected then?

If you add new columns to your table, they will not appear on your view.

The syntax is not completely right. You can use "*" in the SELECT list, but then it has to be qualified with a correlation name if there are also other columns listed. The other issue is that delimited names must use double-quotes and not single quotes. And finally, the ';' is not part of the SQL statement.
Another alternative is to give the names of columns like this in the CREATE VIEW statement:

Is that part of the standard or a SQL Server implementation issue? (Just flying the ANSI flag ).

pootle flump ur codings are working excelent.

That's in the standard.

Thank you

pootle flump ur codings are working excelent.

ummmmmmmmmm

Don't use SELECT *

That is common sense, one would think.

Actually two or three would think, but we still have to repeat that little litany over, and over, and over again!

-PatP