HQL QuerySyntaxException

Question

我是一个经验丰富的SQL开发人员新的HQL，我的任务是在HQL中编写跟随和接收跟随错误，它不喜欢关于CASE语句的一些东西，但我不知道为什么。SQL查询工作良好，但已指示必须用户HQL。如果你能的话，请帮忙

查询：

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

Answer 1

@伯纳德；蜂巢在解释方面有困难；或者。或者，在translate函数中。相反，尝试在特殊字符前添加转义字符\。希望能有所帮助。谢了。

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

我正在使用Apache配置单元（版本1.2）

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