HQL QuerySyntaxException

0
votes

I'm an experienced SQL developer new to HQL, i've been tasked with writing following in HQL and receiving following error, it doesn't like something about the CASE statement but I don't know why. SQL query works fine but have been instructed must user HQL. Please help if you're can

Caused by: org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected AST node: case near line 1, column 143 [Select fs from FuelStation as fs join fs.address as a where fs.marketRegion = :marketRegion and ...

Query:

String hql = "Select fs from FuelStation as fs join fs.address as a where fs.marketRegion = :marketRegion "
+ "and (  (case when  :phone1 is not null "
+ "         then (    a.phone1 is not null "
+ "               and upper(translate(:phone1,  ' ()+-/@&''.,;$','')) = "
+ "                   upper(translate(a.phone1, ' ()+-/@&''.,;$','')) "
+ "               and (   (    :fsloc is not null "
+ "                        and :fsname is not null "
+ "                        and a.locality is not null "
+ "                        and fs.stationName is not null "
+ "               add upper(translate(:fsloc,         ' ()+-/@&''.,;$','')) = "
+ "                   upper(translate(a.locality,     ' ()+-/@&''.,;$','')) "
+ "               and upper(translate(:fsname,        ' ()+-/@&''.,;$','')) = "
+ "                   upper(translate(fs.stationName, ' ()+-/@&''.,;$',''))) "
+ "                    or (    a.zip is not null "
+ "                        and :fszip is not null "
+ "                        and :fsaddr is not null "
+ "                        and a.address is not null "
+ "                        and upper(translate(:zip,  ' ()+-/@&''.,;$','')) = "
+ "             and upper(translate(a.zip,            ' ()+-/@&''.,;$','')) "
+ "             and upper(translate(:fsaddr,' 0123456789()+-/@&''.,;$','')) = "
+ "             upper(translate(a.address,  ' 0123456789()+-/@&''.,;$',''))))) "
+ "         else (    a.zip is not null "
+ "              and :fszip is not null "
+ "              and :fsloc is not null "
+ "              and :fsname is not null "
+ "              and :fsaddr is not null "
+ "              and a.address is not null "
+ "              and a.locality is not null "
+ "              and fs.stationName is not null "
+ "              and upper(translate(a.zip,          ' ()+-/@&''.,;$','')) = "
+ "                  upper(translate(:fszip,         ' ()+-/@&''.,;$','')) "
+ "              and upper(translate(:fsloc,         ' ()+-/@&''.,;$','')) = "
+ "                  upper(translate(a.locality,     ' ()+-/@&''.,;$','')) "
+ "              and upper(translate(:fsname,        ' ()+-/@&''.,;$','')) = "
+ "                  upper(translate(fs.stationName, ' ()+-/@&''.,;$','')) "
+ "            and upper(translate(:fsaddr,  ' 0123456789()+-/@&''.,;$','')) = "
+ "                upper(translate(a.address,' 0123456789()+-/@&''.,;$',''))) "
+ "         end) "
+ "      or "
+ "        (     abs(abs(a.latitude)  - abs(:lat)) <= 0.001 "
+ "          and abs(abs(a.longitude) - abs(:lng)) <= 0.001)) ";
1
hibernatehql
This is a massive query. Reduce the case logic until it starts working, then add things back until it breaks. - Tim Biegeleisen
Yeah good advise issue ended up being simple the CASE statement was fine the issue was i selecting the table column "a.address" instead of the class field "a.addressLine". Newbie hql issue i'm sure - Bernard Craddock
I'm not sure HQL has much to do with it per se. With a query this complex, the odds of something going wrong are fairly high. - Tim Biegeleisen

1 Answers

0
votes

@Bernard; Hive is having trouble on interpreting ; or . or , in the translate function. Instead, try adding an escape char \ before the special characters. Hope it helps. Thanks.

Old: upper(translate(:phone1,  ' ()+-/@&''.,;$',''))
New: upper(translate(:phone1,  ' ()+-/@&''\.\,\;$',''))

i'm using Apache Hive (version 1.2)

sample: select upper(translate('1+2\;)3(456',  ' ()+-/@&''\.\,\;$',''));
result: 123456