bizQueryProcedure中KSQL语句错误

delphixf

在设计器中，测试没有问题，在.W页面中使用该Action报错

执行KSQL出错, KSQL: SELECT DISTINCT (getYear(ThirdContract.SignDate)) as ContractYear,SA_OPOrg.sName as DepartmentName,(sum(ThirdContract.ThirdContractAmount)) as ThirdContractAmount,(sum(ThirdContract.ThirdBillSumAmount)) as ThirdContractBill,(sum(ThirdContract.ThirdPaymentSumAmount)) as ThirdContractPayment,ThirdContract.SignDate as SignDate  FROM ThirdContract ThirdContract  optional  join SA_OPOrg SA_OPOrg on ThirdContract.ThirdContractDepartment = SA_OPOrg WHERE (getYear(ThirdContract.SignDate) = getYear(:currentDate()) group by ThirdContract.ThirdContractDepartment)

编码: JUSTEP154115; 提示: KSQL语法错误, currentDate()) ^^group by ThirdC中未找到需要的元素, 位置511期望是)

jishuang

测试没问题，运行应该是一样的，确认测试的ksql和运行的完全一致？

delphixf

jishuang 发表于 2018-1-24 09:37
测试没问题，运行应该是一样的，确认测试的ksql和运行的完全一致？

一样的。上面的语句就是在studio中考的。

delphixf

jishuang 发表于 2018-1-24 09:37
测试没问题，运行应该是一样的，确认测试的ksql和运行的完全一致？

大神，帮忙看看。测试的SQL
select distinct (getYear(ThirdContract.SignDate)) as ContractYear,SA_OPOrg.sName as DepartmentName,(sum(ThirdContract.ThirdContractAmount)) as ThirdContractAmount,(sum(ThirdContract.ThirdBillSumAmount)) as ThirdContractBill,(sum(ThirdContract.ThirdPaymentSumAmount)) as ThirdContractPayment,ThirdContract.SignDate as SignDate
    from ThirdContract ThirdContract
     optional  join SA_OPOrg SA_OPOrg on ThirdContract.ThirdContractDepartment = SA_OPOrg
    group by ThirdContract.ThirdContractDepartment

delphixf

delphixf 发表于 2018-1-24 12:20
大神，帮忙看看。测试的SQL
select distinct (getYear(ThirdContract.SignDate)) as ContractYear,SA_OPO ...

我对比测试的与运行中的一样啊

jishuang

测试的sql并没有日期的过滤

delphixf

jishuang 发表于 2018-1-24 13:39
测试的sql并没有日期的过滤

我刚才拷错了，给别人演示了，把日期过滤删了。这是加上日期的测试SQL，测试没问题，但在.w运行报错
select distinct (getYear(ThirdContract.SignDate)) as ContractYear,SA_OPOrg.sName as DepartmentName,(sum(ThirdContract.ThirdContractAmount)) as ThirdContractAmount,(sum(ThirdContract.ThirdBillSumAmount)) as ThirdContractBill,(sum(ThirdContract.ThirdPaymentSumAmount)) as ThirdContractPayment,ThirdContract.SignDate as SignDate
    from ThirdContract ThirdContract
     optional  join SA_OPOrg SA_OPOrg on ThirdContract.ThirdContractDepartment = SA_OPOrg
    where getYear( ThirdContract.SignDate )  =  getYear( :currentDate() )
    group by ThirdContract.ThirdContractDepartment LIMIT 0, 50

delphixf

delphixf 发表于 2018-1-24 14:03
我刚才拷错了，给别人演示了，把日期过滤删了。这是加上日期的测试SQL，测试没问题，但在.w运行报错
sele ...

运行错误日志
Caused by: com.justep.exception.BusinessException: 编码: JUSTEP150210; 提示: 执行KSQL出错, KSQL: SELECT DISTINCT (getYear(ThirdContract.SignDate)) as ContractYear,SA_OPOrg.sName as DepartmentName,(sum(ThirdContract.ThirdContractAmount)) as ThirdContractAmount,(sum(ThirdContract.ThirdBillSumAmount)) as ThirdContractBill,(sum(ThirdContract.ThirdPaymentSumAmount)) as ThirdContractPayment,ThirdContract.SignDate as SignDate  FROM ThirdContract ThirdContract  optional  join SA_OPOrg SA_OPOrg on ThirdContract.ThirdContractDepartment = SA_OPOrg  WHERE (getYear( ThirdContract.SignDate )  =  getYear( :currentDate() ) group by ThirdContract.ThirdContractDepartment) LIMIT 0, 20
--> sql:
--> binds: []
    at com.justep.exception.BusinessException.create(Unknown Source)
    at com.justep.system.data.impl.TableUtilsImplement.ksqlSelect(Unknown Source)
    at com.justep.system.data.impl.TableUtilsImplement.ksqlSelect(Unknown Source)
    at com.justep.system.data.impl.TableUtilsImplement.ksqlSelect(Unknown Source)
    at com.justep.system.data.BizData.query(Unknown Source)
    at BizProcedure.query(BizProcedure.java:12)
    ... 26 more
Caused by: com.justep.exception.KSQLException: 编码: JUSTEP154115; 提示: KSQL语法错误, urrentDate() ) ^^group by ThirdC中未找到需要的元素, 位置518期望是)
测试界面如图

delphixf

大神在哪儿？帮帮忙！

jishuang

比较复杂的ksql可以用sql实现,标准的queryAction中本来也不支持直接设置group by
http://bbs.wex5.com/forum.php?mod=viewthread&tid=54836

这个没起作用吧