KSQL更新日期问题

leechin

需求：后台批量处理日期，按条件将一批数据的fEnd(datatime型)改为fStart(datetime型)的前一天
方案：Action中执行KSQL实现更新，由于KSQL函数中没有日期增减函数，因此采用表达式函数addDays
KSQL：

1. ksqldelete = "update AJCG_ContractDetail2 D2 set D2.fEnd = :addDays(D2.fStart, -1) where D2.fMasterID = '" + fContractID + "' and D2.fContractStatus <> '财务审核通过'"

复制代码

结果：执行KSQL出错，报错信息如下。如何调整这句KSQL，请帮忙提供指导。

1. Caused by: com.justep.exception.BusinessException: 编码: JUSTEP150210; 提示: 执行KSQL出错, KSQL: update AJCG_ContractDetail2 D2 set D2.fEnd = :addDays(D2.fStart, -1)  where D2.fMasterID = '7D5FA558831147D8B5D26F460F0FEF91' and D2.fContractStatus <> '财务审核通过'
   
2.         at com.justep.exception.BusinessException.create(Unknown Source)
   
3.         at com.justep.system.data.impl.TableUtilsImplement.ksqlUpdate(Unknown Source)
   
4.         at com.justep.system.data.impl.TableUtilsImplement.ksqlExecuteUpdate(Unknown Source)
   
5.         at com.justep.system.data.KSQL.executeUpdate(Unknown Source)
   
6.         at com.justep.system.data.KSQL.executeUpdate(Unknown Source)
   
7.         at com.justep.system.data.KSQL.executeUpdate(Unknown Source)
   
8.         at Utils.MDMUtil.GenerateContract(MDMUtil.java:197)
   
9.         at FWCG.GenerateContract(FWCG.java:587)
   
10.         ... 26 more
    
11. Caused by: com.justep.model.exception.ModelException: 编码: JUSTEP154075; 提示: 调用函数addDays出错
    
12.         at com.justep.model.exception.ModelException.create(Unknown Source)
    
13.         at com.justep.system.ksql.parser.ExpressionImpl.functionExpression(Unknown Source)
    
14.         at com.justep.system.ksql.parser.AbstractParser.parameterNode(Unknown Source)
    
15.         at com.justep.system.ksql.parser.AbstractParser.constExpression(Unknown Source)
    
16.         at com.justep.system.ksql.parser.AbstractParser.unaryExpression(Unknown Source)
    
17.         at com.justep.system.ksql.parser.AbstractParser.multiExpression(Unknown Source)
    
18.         at com.justep.system.ksql.parser.AbstractParser.addExpression(Unknown Source)
    
19.         at com.justep.system.ksql.parser.UpdateParser.assignExpression(Unknown Source)
    
20.         at com.justep.system.ksql.parser.UpdateParser.setNode(Unknown Source)
    
21.         at com.justep.system.ksql.parser.UpdateParser.start(Unknown Source)
    
22.         at com.justep.system.ksql.parser.AbstractParser.parse(Unknown Source)
    
23.         ... 33 more
    
24. Caused by: com.justep.exception.KSQLException: 编码: JUSTEP154115; 提示: KSQL语法错误, d = :addDays(D2^^.fStart, -1) ,D中未找到需要的元素, 位置56期望是(
    
25.         at com.justep.exception.KSQLException.create(Unknown Source)
    
26.         at com.justep.system.ksql.token.TokenManager.matchToken(Unknown Source)
    
27.         at com.justep.system.ksql.token.TokenManager.matched(Unknown Source)
    
28.         at com.justep.system.ksql.parser.ExpressionImpl.functionExpression(Unknown Source)
    
29.         at com.justep.system.ksql.parser.ExpressionImpl.constExpression(Unknown Source)
    
30.         at com.justep.system.ksql.parser.ExpressionImpl.unaryExpression(Unknown Source)
    
31.         at com.justep.system.ksql.parser.ExpressionImpl.multiExpression(Unknown Source)
    
32.         at com.justep.system.ksql.parser.ExpressionImpl.addExpression(Unknown Source)
    
33.         at com.justep.system.ksql.parser.ExpressionImpl.compExpression(Unknown Source)
    
34.         at com.justep.system.ksql.parser.ExpressionImpl.notExpression(Unknown Source)
    
35.         at com.justep.system.ksql.parser.ExpressionImpl.andExpression(Unknown Source)
    
36.         at com.justep.system.ksql.parser.ExpressionImpl.orExpression(Unknown Source)
    
37.         ... 43 more

复制代码

jishuang

内存函数计算时机比较早，不能这样用，可以用SQL.调用数据库的函数实现

leechin

jishuang 发表于 2017-10-31 13:27
内存函数计算时机比较早，不能这样用，可以用SQL.调用数据库的函数实现

还是不行，sqlserver的函数是DATEADD(DAY,-1,D2.fStart)，其中DAY是增减维度关键字，但KSQL不能识别

KSQL：

1. update AJCG_ContractDetail2 D2 set D2.fEnd = SQL.DATEADD(DAY,-1,D2.fStart) where D2.fMasterID = '03EC52961EBE465D92B7B50DE6010442' and D2.fContractStatus <> '财务审核通过'
   

复制代码

报错信息：

1. Caused by: com.justep.exception.BusinessException: 编码: JUSTEP150210; 提示: 执行KSQL出错, KSQL: update AJCG_ContractDetail2 D2 set D2.fEnd = SQL.DATEADD(DAY,-1,D2.fStart) where D2.fMasterID = '03EC52961EBE465D92B7B50DE6010442' and D2.fContractStatus <> '财务审核通过'
   
2.         at com.justep.exception.BusinessException.create(Unknown Source)
   
3.         at com.justep.system.data.impl.TableUtilsImplement.ksqlUpdate(Unknown Source)
   
4.         at com.justep.system.data.impl.TableUtilsImplement.ksqlExecuteUpdate(Unknown Source)
   
5.         at com.justep.system.data.KSQL.executeUpdate(Unknown Source)
   
6.         at com.justep.system.data.KSQL.executeUpdate(Unknown Source)
   
7.         at com.justep.system.data.KSQL.executeUpdate(Unknown Source)
   
8.         at Utils.MDMUtil.GenerateContract(MDMUtil.java:197)
   
9.         at FWCG.GenerateContract(FWCG.java:587)
   
10.         ... 26 more
    
11. Caused by: com.justep.exception.BusinessException: 编码: JUSTEP154054; 提示: KSQL语法错误, DAY找不到对应的符号描述
    
12.         at com.justep.exception.BusinessException.create(Unknown Source)
    
13.         at com.justep.util.Utils.check(Unknown Source)
    
14.         at com.justep.system.ksql.visitor.SQLVisitor.visit(Unknown Source)
    
15.         at com.justep.system.ksql.node.IdentiferNode.accept(Unknown Source)
    
16.         at com.justep.system.ksql.visitor.SQLVisitor.visitChildren(Unknown Source)
    
17.         at com.justep.system.ksql.visitor.SQLVisitor.visit(Unknown Source)
    
18.         at com.justep.system.ksql.node.LocalSqlFunctionNode.accept(Unknown Source)
    
19.         at com.justep.system.ksql.visitor.SQLVisitor.visitChildren(Unknown Source)
    
20.         at com.justep.system.ksql.visitor.SQLVisitor.visit(Unknown Source)
    
21.         at com.justep.system.ksql.node.AssignNode.accept(Unknown Source)
    
22.         at com.justep.system.ksql.visitor.SQLVisitor.visitChildren(Unknown Source)
    
23.         at com.justep.system.ksql.visitor.SQLVisitor.visit(Unknown Source)
    
24.         at com.justep.system.ksql.node.SetNode.accept(Unknown Source)
    
25.         at com.justep.system.ksql.visitor.SQLVisitor.visitChildren(Unknown Source)
    
26.         at com.justep.system.ksql.visitor.SQLVisitor.visit(Unknown Source)
    
27.         at com.justep.system.ksql.node.UpdateSentence.accept(Unknown Source)
    
28.         ... 33 more
    

复制代码

jishuang

DAY用
SQL.DAY

leechin

jishuang 发表于 2017-10-31 16:00
DAY用
SQL.DAY

好的，谢谢，结贴