0

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.

1 Answer 1

1

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

Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.