我有一个HQL查询,它正在搜索某些值(在下面示例中的字段chr和ar中(。我想扩展一下,它不仅返回给定的值,还返回那些对该字段具有null值的条目。(类似于SELECT * FROM MY_TABLE WHERE MY_FIELD = 2 OR MY_FIELD IS NULL
,但嵌套在更大的查询中。(
这是一个工作查询,它返回参数中给定值的字段:
SELECT DISTINCT pr
FROM Product pr
INNER JOIN FETCH pr.pCategory pcat
INNER JOIN FETCH pr.status status
JOIN FETCH pr.productPP productPP
LEFT OUTER JOIN FETCH pr.area area
LEFT OUTER JOIN FETCH pr.chR chr
LEFT OUTER JOIN FETCH pr.area ar
WHERE (UPPER(chr.title) = UPPER(:parameter_10))
AND (UPPER(ar.title) <> UPPER(:parameter_20))
AND (productPP.product.objId in (
SELECT ppp.product.objId
FROM ProductPP ppp
WHERE productPP.product.objId = pr.objId
))
这是我的版本:
SELECT DISTINCT pr
FROM Product pr
INNER JOIN FETCH pr.pCategory pcat
INNER JOIN FETCH pr.status status
JOIN FETCH pr.productPP productPP
LEFT OUTER JOIN FETCH pr.area area
LEFT OUTER JOIN FETCH pr.chR chr
LEFT OUTER JOIN FETCH pr.area ar
WHERE (UPPER(chr.title) = UPPER(:parameter_10) OR UPPER(:chr.title) IS NULL)
AND (UPPER(ar.title) <> UPPER(:parameter_20) OR UPPER(:ar.title) IS NULL)
AND (productPP.product.objId in (
SELECT ppp.product.objId
FROM ProductPP ppp
WHERE productPP.product.objId = pr.objId
))
我得到了错误unexpected AST node
,目前我无法解释,但我怀疑我的语法一定有问题。
冒号是参数的语法标记。该标记不能位于路径表达式之前。只需将UPPER(:chr.title) IS NULL
和UPPER(:ar.title) IS NULL
更改为chr.title IS NULL
和ar.title IS NULL