Professional Web Applications Themes

mysql question : CASE - MySQL

You might want to try comp.databases.mysql for comprehensive answers. (X-Post and Followup-To set) Geradeaus wrote: > I have searched but have not yet found how I can resolve the following > problem : > > SELECT adId, > CASE > WHEN adId =1 THEN cityId > WHEN adId =2 THEN regionId > END AS extraField > FROM ad > WHERE extraField <> 0 > > The problem is "extraField" cannot be used in the WHERE clause... I am using > mySQL 4.1 .... WHERE adId in (1, 2) not tested!...

  1. #1

    Default Re: mysql question : CASE

    You might want to try comp.databases.mysql for comprehensive answers.
    (X-Post and Followup-To set)

    Geradeaus wrote:
    > I have searched but have not yet found how I can resolve the following
    > problem :
    >
    > SELECT adId,
    > CASE
    > WHEN adId =1 THEN cityId
    > WHEN adId =2 THEN regionId
    > END AS extraField
    > FROM ad
    > WHERE extraField <> 0
    >
    > The problem is "extraField" cannot be used in the WHERE clause... I am using
    > mySQL 4.1
    ....
    WHERE adId in (1, 2)

    not tested!

    maraguida@gmail.com Guest

  2. #2

    Default Re: mysql question : CASE

    > Geradeaus wrote:
    >> I have searched but have not yet found how I can resolve the following
    >> problem :
    >>
    >> SELECT adId,
    >> CASE
    >> WHEN adId =1 THEN cityId
    >> WHEN adId =2 THEN regionId
    >> END AS extraField
    >> FROM ad
    >> WHERE extraField <> 0
    >>
    >> The problem is "extraField" cannot be used in the WHERE clause... I am
    >> using
    >> mySQL 4.1
    This is a doented limitation in MySQL, and in the SQL language.

    Excerpts from [url]http://dev.mysql.com/doc/refman/5.0/en/select.html[/url] :

    A select_expr can be given an alias using AS alias_name.
    The alias is used as the expression's column name and can be used
    in GROUP BY, ORDER BY, or HAVING clauses.
    . . .
    It is not allowable to use a column alias in a WHERE clause, because
    the column value might not yet be determined when the WHERE clause
    is executed.

    Example of the latter case:

    SELECT SUM(lineitem_value) AS order_subtotal
    FROM order_lineitems
    WHERE order_subtotal > 999
    GROUP BY order_id;

    See also [url]http://dev.mysql.com/doc/refman/5.0/en/problems-with-alias.html[/url]

    Regards,
    Bill K.


    Bill Karwin Guest

Similar Threads

  1. PHP / MySql question
    By lukas in forum PHP Development
    Replies: 0
    Last Post: August 18th, 05:20 PM
  2. #23026 [Com]: Make Zend case-sensitive (classes, functions, remove case-insensitive)
    By nvivo at mandic dot com dot br in forum PHP Development
    Replies: 0
    Last Post: October 19th, 12:17 PM
  3. Question regarding PHP and mySQL
    By Me@lost_pls_help in forum PHP Development
    Replies: 1
    Last Post: July 27th, 09:57 PM
  4. 7013 case question
    By Robert DiRosario in forum AIX
    Replies: 1
    Last Post: July 20th, 08:21 PM
  5. OT: MySQL question
    By Chris in forum PHP Development
    Replies: 2
    Last Post: July 10th, 11:54 AM

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