SitePoint Sponsor

User Tag List

Results 1 to 12 of 12
  1. #1
    Non-Member
    Join Date
    Oct 2005
    Posts
    205
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    need help with complex select statement

    Hello all. I am generating the following statement dynamically but I get errors when I add the between clause in.... mySQL says:

    #1054 - Unknown column 'occwds' in 'where clause'

    any help is appreciated.

    select *, text, length(text) as len, length(replace(text,'PHP','')) as lrep, ( length(text) - length(replace(text,'PHP','')) ) / length('PHP') as occ , length(text) + 1 -length(replace(text,' ','')) as wds , (( length(text) - length(replace(text,'PHP','')) ) / length('PHP') ) /(length(text) + 1 - length(replace(text,' ','')) ) as occwds , ( length(text) - length(replace(text,'PHP','')) ) / length(text) as occlen from article_database where text like '%PHP%' AND occwds BETWEEN '0' AND '50' ORDER by occwds DESC LIMIT 0, 100

  2. #2
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    you can not always use a column alias in a where clause. in fact, the SQL standard doesn't allow it.

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    webhead2, i kiss you!!!
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    by the way, why don't you want occwds greater than 50? especially if you are sorting them descending?

    longneck, the reason i got so enthused is because webhead2 is using my query (thanks, webhead2)

    see Simple Keyword Relevance
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    i thought i recognized that, but i couldn't quite put my finger on it.

  6. #6
    Non-Member
    Join Date
    Oct 2005
    Posts
    205
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Is it a problem if I use it?

    How do you get around the alias..... i understand that is the problem but is there a solution?

    The between statement is not always the same, the vaules will change depending on user input.

    But I am trying to provide where density values fall between a range..

  7. #7
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    copy the entire definiton of that column to the where clause instead.

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by webhead2
    Is it a problem if I use it?
    not at all, i am pleased

    but why 50? what does that number mean? if some row has a "score" of 51, why wouldn't you want it?

    that's like asking "gimme the best basketball players, and show them to me in decreasing sequence by height, but i want only those guys under 5 foot 6 tall"
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    Non-Member
    Join Date
    Oct 2005
    Posts
    205
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by longneck
    copy the entire definiton of that column to the where clause instead.
    okay, thanks for the advice.... i tried this, but I get an error.....


    Couldn't execute density query! select *, text, length(text) as len, length(replace(text,'cat','')) as lrep, ( length(text) - length(replace(text,'cat','')) ) / length('cat') as occ , length(text) + 1 -length(replace(text,' ','')) as wds , (( length(text) - length(replace(text,'cat','')) ) / length('cat') ) /(length(text) + 1 - length(replace(text,' ','')) ) as occwds , ( length(text) - length(replace(text,'cat','')) ) / length(text) as occlen from article_database where text like '%cat%' AND (( length(text) - length(replace(text,'cat','')) ) / length('cat') ) /(length(text) + 1 - length(replace(text,' ','')) ) as occwds BETWEEN 3 AND 15 ORDER by occwds DESC LIMIT 0, 100

  10. #10
    Non-Member
    Join Date
    Oct 2005
    Posts
    205
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    okay i tried this.... seems to work

    Couldn't execute density query! select *, text, length(text) as len, length(replace(text,'cat','')) as lrep, ( length(text) - length(replace(text,'cat','')) ) / length('cat') as occ , length(text) + 1 -length(replace(text,' ','')) as wds , (( length(text) - length(replace(text,'cat','')) ) / length('cat') ) /(length(text) + 1 - length(replace(text,' ','')) ) as occwds , ( length(text) - length(replace(text,'cat','')) ) / length(text) as occlen from article_database where text like '%cat%' AND (( length(text) - length(replace(text,'cat','')) ) / length('cat') ) /(length(text) + 1 - length(replace(text,' ','')) ) BETWEEN 3 AND 15 ORDER by occwds DESC LIMIT 0, 100

  11. #11
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    yes, you have to leave off the as part in the where clause.

    and in the future, don't just say you get an error, POST THE ERROR!

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    why cut it off at 15?

    if you have an article that mentions cat 15 time, you want to see it, but if you have one that mentions cat 16 times, you want to ignore it?

    that doesn't make sense
    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
  •