Custom FinderImpl Not working with IN-Clause and Having

[7年前 に Debopam Mitra によって更新されました。]

I have a custom SQL like the following:

SELECT {pm.*} FROM productmaster pm 
INNER JOIN productcategories pc USING(productId) 
WHERE pc.categoryId IN (?) 
GROUP BY pc.courseId 
HAVING COUNT(DISTINCT pc.categoryId) >= ?

The FinderImpl code looks like this:

SQLQuery sqlQuery = session.createSQLQuery(customSQL);
sqlQuery.addEntity("pm", ProductMasterImpl.class);
QueryPos queryPos = QueryPos.getInstance(sqlQuery);
String filterCategoryIdsMerge = StringUtil.merge(filterCategoryIds, StringPool.COMMA_AND_SPACE);
queryPos.add(filterCategoryIdsMerge);
queryPos.add(filterCategoryIds.length);
productMasters = (List<productmaster>) sqlQuery.list();</productmaster>

The SQL is correct as it is showing the results when executed in MySQL prompt. But the same is giving 0 results. Further investigating I found that if I am providing only one categoryId it's giving me results.

Can any one tell what might be the problem?

[7年前 に David H Nebinger によって更新されました。]

where pc.category in (list) having count(distinct matches) >= list length?

Are you sure that's what you're going after?