Professional Web Applications Themes

column alias - MySQL

Been Googling around, but can't find quite the answer. To massively simplify what I'm trying to do I want to have some aliased columns and then refer to those aliases later. e.g. select 1 as one, 2 as two, one+two as three which would be possible in Access but in mySQL (or at least in the query browser) gives the error "Unknown column 'one' in 'field list' Any ideas? Cheers Rob...

  1. #1

    Default column alias

    Been Googling around, but can't find quite the answer.

    To massively simplify what I'm trying to do I want to have some aliased
    columns and then refer to those aliases later.

    e.g.

    select
    1 as one,
    2 as two,
    one+two as three

    which would be possible in Access but in mySQL (or at least in the query
    browser) gives the error "Unknown column 'one' in 'field list'

    Any ideas?

    Cheers

    Rob




    LittleRob Guest

  2. #2

    Default Re: column alias

    LittleRob wrote:
    <snip> 

    IIRC the alias can only be used in the GROUP BY, ORDER BY, and HAVING
    clauses in MySQL. My suggestion: Name your columns, don't number them.

    Hope this helps.
    -Jordan Greenberg

    --
    Posted via a free Usenet account from http://www.teranews.com

    Jordan Guest

  3. #3

    Default Re: column alias

    Jordan

    Hmmm. that's what I was coming to realise. Its a bit of a pain as I can do
    it in other DB systems (well at least 1) and my actual problem is much more
    complex than my example. Its more along the lines of:

    select
    2*a as doubleA,
    2*b as doubleB,
    doubleA+doubleB as C
    from
    some_table_or_other

    The only ways round this seem to be to repeat the calculation such that

    select
    2*a as doubleA,
    2*b as doubleB,
    2*a+2*b as C
    from
    some_table_or_other

    *Does* work, except that my actual formula is *much* more complex. The other
    way that works is to use a view

    create view vwMyView as
    select
    2*a as doubleA,
    2*b as doubleB,
    from
    some_table_or_other

    then

    select
    doubleA,
    doubleB,
    doubleA+doubleB as C
    from
    vwMyview

    also works. The problem with this approach is that if I then wanted to use C
    further I'd have to create a view on a view (I suppose)

    Rob

    "Jordan Greenberg" <edu> wrote in message
    news:edu... 
    >
    > IIRC the alias can only be used in the GROUP BY, ORDER BY, and HAVING
    > clauses in MySQL. My suggestion: Name your columns, don't number them.
    >
    > Hope this helps.
    > -Jordan Greenberg
    >
    > --
    > Posted via a free Usenet account from http://www.teranews.com
    >[/ref]


    LittleRob Guest

  4. #4

    Default Re: column alias

    LittleRob wrote: 


    Ahh, I originally thought that your main problem was that your column
    names were numbers in your example, and you were using your aliases to
    get around writing SELECT 1+2, which would just return 3.
    This way, if you've got to do some calculation in the database, I
    suppose views (or duplicating calculations) is your best bet. At least I
    cant think of anything better. I dunno what you're doing with it next,
    of course. Most of my experience is in database applications, so I'd
    probably just do the calculation in the application, if thats an option.

    Jordan Greenberg

    --
    Posted via a free Usenet account from http://www.teranews.com

    Jordan Guest

  5. #5

    Default Re: column alias

    Jordan

    It *is* for a database application, and there are several options for where
    we do the calculation. My view was that if we did it server-side (i.e. in
    SQL) that it would be more maintainable (since it wouldn't be in a compiled
    EXE) and that if we could have created a single view that this would greatly
    assist reporting (for which we're using Crystal) since otherwise the GUI has
    lots of extra data fields that someone wishing to report doesn't get.

    I guess we'll have to accept that SQL will only get us partway there, still
    I guess that's something. The other thing we're looking at is UDFs but this
    looks pretty tricky and very DB specific (I presume)

    Cheers

    Rob


    "Jordan Greenberg" <edu> wrote in message
    news:edu... 
    >
    >
    > Ahh, I originally thought that your main problem was that your column
    > names were numbers in your example, and you were using your aliases to
    > get around writing SELECT 1+2, which would just return 3.
    > This way, if you've got to do some calculation in the database, I
    > suppose views (or duplicating calculations) is your best bet. At least I
    > cant think of anything better. I dunno what you're doing with it next,
    > of course. Most of my experience is in database applications, so I'd
    > probably just do the calculation in the application, if thats an option.
    >
    > Jordan Greenberg
    >
    > --
    > Posted via a free Usenet account from http://www.teranews.com
    >[/ref]


    LittleRob Guest

Similar Threads

  1. #33809 [Com]: pg_fetch_result: table alias produces invalid column result error
    By alan8 at maths dot topology dot org in forum PHP Bugs
    Replies: 0
    Last Post: September 22nd, 12:30 PM
  2. column alias help
    By Velaria in forum Macromedia ColdFusion
    Replies: 7
    Last Post: June 7th, 02:01 PM
  3. RS(fieldname) error when using column alias.
    By Lee Farrant in forum ASP Database
    Replies: 2
    Last Post: January 3rd, 03:59 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139