Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2004
    Posts
    3

    Unanswered: SELECT AS clause errors

    Hello. I am trying to use a search table in this AS400. It basically contains keywords, and corresponding filenumbers that the rest of the information can be pulled from other tables with. There are multiple keywords for single files, hence the GROUPing.

    But my problem is using the AS clause, i get this error:

    Warning: odbc_exec(): SQL error: [IBM][Client Access Express ODBC Driver (32-bit)][DB2/400 SQL]SQL0206 - Column COUNT not in specified tables., SQL state S0022

    Here is the SQL:

    SELECT SRCHWORD, SRCHFILNBR, COUNT(SRCHFILNBR) AS COUNT
    FROM CISDTA.RSRCH
    WHERE (SRCHWORD >= '$keywd1' AND SRCHWORD <= 'keywd12')
    OR (SRCHWORD >= '$keywd2' AND SRCHWORD <= '$keywd22')
    AND COUNT = $keywords
    GROUP BY SRCHFILNBR, SRCHWORD, COUNT ORDER BY SRCHFILNBR

    In this case, $keywords would be 2, because the AS400 will return all filenumbers where only one keyword matches, and this will filter them out.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i would try using something other than a reserved word like COUNT as the alias

    also, any tests on an aggregate function must be done in the HAVING clause, not the WHERE clause
    Code:
    select SRCHWORD
         , SRCHFILNBR
         , count(SRCHFILNBR) as flooble
      from CISDTA.RSRCH
     where SRCHWORD between '$keywd1' and 'keywd12'
        or SRCHWORD between '$keywd2' and '$keywd22'
    group 
        by SRCHWORD
         , SRCHFILNBR
    having count(SRCHFILNBR) = $keywords      
    order 
        by SRCHFILNBR
    Last edited by r937; 06-01-04 at 09:49.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jun 2004
    Posts
    3
    Quote Originally Posted by r937
    i would try using something other than a reserved word like COUNT as the alias
    Ive tried many words

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    try query i put into previous post

    i guess i was editing it while you answered
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jun 2004
    Posts
    3
    Quote Originally Posted by r937
    try query i put into previous post

    i guess i was editing it while you answered
    This:

    SELECT SRCHWORD, SRCHFILNBR, COUNT(SRCHFILNBR) AS HITS
    FROM CISDTA.RSRCH
    WHERE (SRCHWORD >= 'keyword1' AND SRCHWORD <= 'keyword12')
    OR (SRCHWORD >= 'keyword2' AND SRCHWORD <= 'keyword22')
    GROUP BY SRCHFILNBR, SRCHWORD, HAVING COUNT(SRCHFILNBR) = 2
    ORDER BY SRCHFILNBR

    gives me this:

    Warning: odbc_exec(): SQL error: [IBM][Client Access Express ODBC Driver (32-bit)][DB2/400 SQL]SQL0199 - Keyword COUNT not expected. Valid tokens: FOR WITH FETCH ORDER UNION OPTIMIZE., SQL state 37000 in SQLExecDirect

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    comma in front of HAVING
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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