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?
