Results 1 to 13 of 13
  1. #1
    Join Date
    Jun 2003
    Location
    Wakefield, UK
    Posts
    8

    Question Unanswered: Simple SELECT * (easy one)

    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`;

  2. #2
    Join Date
    Jun 2003
    Location
    Wakefield, UK
    Posts
    8
    actually, i know that would kind of work, but i don't want the duplicated field name

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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"
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    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 *
    George
    Home | Blog

  5. #5
    Join Date
    Jun 2003
    Location
    Wakefield, UK
    Posts
    8
    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?

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    If you add new columns to your table, they will not appear on your view.
    George
    Home | Blog

  7. #7
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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.
    Code:
    CREATE VIEW "test" AS
    SELECT t.*, "test_field" as "test_type" FROM "test_table"
    Another alternative is to give the names of columns like this in the CREATE VIEW statement:
    Code:
    CREATE VIEW "test"("col1", "col2", ..., "test_type") AS
    SELECT t.*, "test_field" FROM "test_table"
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by georgev
    If you add new columns to your table, they will not appear on your view.
    Is that part of the standard or a SQL Server implementation issue? (Just flying the ANSI flag ).
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Quote Originally Posted by pootle flump
    Is that part of the standard or a SQL Server implementation issue? (Just flying the ANSI flag ).
    That's in the standard.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by stolze
    That's in the standard.
    Thank you
    Testimonial:
    pootle flump
    ur codings are working excelent.

  11. #11
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    ummmmmmmmmm

    Don't use SELECT *
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  12. #12
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Quote Originally Posted by Brett Kaiser
    ummmmmmmmmm

    Don't use SELECT *
    That is common sense, one would think.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  13. #13
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Actually two or three would think, but we still have to repeat that little litany over, and over, and over again!

    -PatP

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •