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.

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

Ive tried many words

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

comma in front of HAVING