SitePoint Sponsor

User Tag List

Results 1 to 2 of 2

Hybrid View

  1. #1
    SitePoint Wizard Mincer's Avatar
    Join Date
    Mar 2001
    Location
    London | UK
    Posts
    1,140
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    mysql select query help.

    If I run the query:

    Code:
    SELECT TO_DAYS( NOW() ) - TO_DAYS(date) AS ageing FROM invoice
    Everything works fine (woohoo! ).

    But if I add this WHERE clause:

    Code:
    SELECT TO_DAYS( NOW() ) - TO_DAYS(date) AS ageing FROM invoice WHERE ageing >= 60
    Then it craps out (boo hoo! ).

    Can someone point out my stupidity?

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    stupidity? that's being awfully hard on yourself

    actually, it's mysql's fault

    6.4.1 SELECT Syntax
    A SELECT expression may be given an alias using AS. The alias is used as the expression's column name and can be used with ORDER BY or HAVING clauses.... It is not allowed to use a column alias in a WHERE clause, because the column value may not yet be determined when the WHERE clause is executed. See section A.5.4 Problems with alias
    so you'll have to say
    Code:
    SELECT TO_DAYS( NOW() )
         - TO_DAYS( date ) AS ageing 
      FROM invoice 
     WHERE TO_DAYS( NOW() )
         - TO_DAYS( date ) >= 60
    except of course you don't really have a column named "date" right?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •