CODEWITHSUNDEEP
hibernateselecthql
RekaK
RekaK

Reputation: 113

Hibernate error when modifying working query to also return null values for field

I have a HQL query that is searching for certain values (in the fields chr and ar in the example below). I want to extend it that it doesn't only return the given value but also those entries that have null value for that field. (Similar to SELECT * FROM MY_TABLE WHERE MY_FIELD = 2 OR MY_FIELD IS NULL, but nested in a bigger query.)

Here is the working query which returns fields with that certain value given in parameters:

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 
       ))

Here is my version:

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
      ))

I'm getting the error unexpected AST node which is at the moment inexplicable to me, but I suspect something must be off in my syntax.

Upvotes: 0

Views: 35

Answers (1)

Christian Beikov
Christian Beikov

Reputation: 16430

The colon is a syntactic marker for parameters. That marker can't precede a path expression. Just change UPPER(:chr.title) IS NULL and UPPER(:ar.title) IS NULL to chr.title IS NULL and ar.title IS NULL

Upvotes: 1

Related Questions