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
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.
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
, count(SRCHFILNBR) as flooble
where SRCHWORD between '$keywd1' and 'keywd12'
or SRCHWORD between '$keywd2' and '$keywd22'
having count(SRCHFILNBR) = $keywords
SELECT SRCHWORD, SRCHFILNBR, COUNT(SRCHFILNBR) AS HITS
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