Professional Web Applications Themes

re-using aliases in SELECT - MySQL

Hi all, I want to receive some calculated fields in a SELECT statement, of which some are made with results of previous calculations, like: SELECT if(invoice1<>"", invoiceamount1, 0)+ if(invoice2<>"", invoiceamount2, 0) AS paidamount, totalamount-paidamount AS neededamount; Where invoice1 and invoice2 are text fields and invoiceamount1 and invoiceamount2 are numerical fields. I know this doesn't work, but is there a way to do this? It has to work in one query, to simplify the rest of the implementation. The only option I can think of is repeating the if() statements. TIA Bart...

  1. #1

    Default re-using aliases in SELECT

    Hi all,

    I want to receive some calculated fields in a SELECT statement, of which
    some are made with results of previous calculations, like:

    SELECT
    if(invoice1<>"", invoiceamount1, 0)+
    if(invoice2<>"", invoiceamount2, 0)
    AS paidamount,
    totalamount-paidamount
    AS neededamount;

    Where invoice1 and invoice2 are text fields and invoiceamount1 and
    invoiceamount2 are numerical fields.

    I know this doesn't work, but is there a way to do this? It has to work
    in one query, to simplify the rest of the implementation.

    The only option I can think of is repeating the if() statements.

    TIA
    Bart
    Bart Friederichs Guest

  2. #2

    Default Re: re-using aliases in SELECT

    Bart Friederichs wrote:
    > I know this doesn't work, but is there a way to do this? It has to work
    > in one query, to simplify the rest of the implementation.
    See [url]http://dev.mysql.com/doc/refman/5.0/en/problems-with-alias.html:[/url]

    Standard SQL doesn't allow you to refer to a column alias in a WHERE
    clause. This is because when the WHERE code is executed, the column
    value may not yet be determined. For example, the following query is
    illegal:

    SELECT id, COUNT(*) AS cnt FROM tbl_name WHERE cnt > 0 GROUP BY id;
    > The only option I can think of is repeating the if() statements.
    That's what I do. It's ugly and unsatisfying, but the SQL standard and
    the MySQL implementation do not give any alternative.

    Regards,
    Bill K.
    Bill Karwin Guest

  3. #3

    Default Re: re-using aliases in SELECT

    Bart Friederichs wrote:
    > Hi all,
    >
    > I want to receive some calculated fields in a SELECT statement, of which
    > some are made with results of previous calculations, like:
    >
    > SELECT
    > if(invoice1<>"", invoiceamount1, 0)+
    > if(invoice2<>"", invoiceamount2, 0)
    > AS paidamount,
    > totalamount-paidamount
    > AS neededamount;
    >
    > Where invoice1 and invoice2 are text fields and invoiceamount1 and
    > invoiceamount2 are numerical fields.
    >
    > I know this doesn't work, but is there a way to do this? It has to work
    > in one query, to simplify the rest of the implementation.
    >
    > The only option I can think of is repeating the if() statements.
    >
    > TIA
    > Bart
    User variables may be what you want:

    SELECT
    PAIDAMOUNT := if(invoice1<>"", invoiceamount1, 0)+
    if(invoice2<>"", invoiceamount2, 0)
    AS paidamount,
    totalamount-PAIDAMOUNT
    AS neededamount;

    Remember that each user variable is evaluated only once per record.

    ciao
    gmax

    --
    _ _ _ _
    (_|| | |(_|><
    _|
    [url]http://gmax.oltrelinux.com[/url]
    Giuseppe Maxia Guest

  4. #4

    Default Re: re-using aliases in SELECT

    Bill Karwin wrote:
    >
    > See [url]http://dev.mysql.com/doc/refman/5.0/en/problems-with-alias.html:[/url]
    <snip>

    That is about WHERE clauses, I knew that. My problem lies in the SELECT
    part.
    Bart Friederichs Guest

  5. #5

    Default Re: re-using aliases in SELECT

    Giuseppe Maxia wrote:
    > User variables may be what you want:
    >
    <snip>

    They are. I have been looking into them, but apparently missed something.

    Thanks
    Bart
    Bart Friederichs Guest

Similar Threads

  1. CFInclude and aliases
    By Tim_Mar in forum Macromedia ColdFusion
    Replies: 0
    Last Post: April 26th, 03:55 PM
  2. RFC: field::aliases
    By Kevin Michael Vail in forum PERL Modules
    Replies: 2
    Last Post: September 28th, 01:24 AM
  3. Aliases in Perl?
    By Jeff Westman in forum PERL Beginners
    Replies: 4
    Last Post: October 20th, 04:41 PM
  4. IP-Addresses and aliases
    By Friedhelm Neyer in forum AIX
    Replies: 3
    Last Post: August 6th, 03:49 PM
  5. DB2/390 aliases to remote databases
    By Phil Castle in forum IBM DB2
    Replies: 0
    Last Post: July 25th, 12: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