KSQL中in条件设置后报错

cyjinli

Caused by: com.justep.exception.BusinessException: 编码: JUSTEP150210; 提示: 执行KSQL出错, KSQL: SELECT SA_Task.*,SA_Task_1.sStatusID as sInstanceStatusID,SA_Task_1.sStatusName as sInstanceStatusName,SA_Task_1.sData2 as sInstanceData2,SA_Task_1.sData3 as sInstanceData3,SA_Task_1.sProcessName as sInstanceProcessName  FROM SA_Task SA_Task  optional  join SA_Task SA_Task_1 on SA_Task.sFlowID = SA_Task_1 WHERE (( SA_Task.sFlowID,SA_Task.sCreateTime)  in  (  (select SA_Task.sFlowID as sFlowID,(max( SA_Task.sCreateTime )) as custom1 from SA_Task SA_Task where ((SA_Task.sStatusID='tesReady') or (SA_Task.sStatusID='tesExecuting')) and (('/ORG01.ogn/PSN01@ORG01.psm' like concat(SA_Task.sExecutorFID, '%') or '/ORG01.ogn/2EEB5DF465E44B23B9DBB66E3D906C27.dpt/E4DB86CF3FA949689F8741316AA15454.pos/PSN01@E4DB86CF3FA949689F8741316AA15454.psm' like concat(SA_Task.sExecutorFID, '%') or '/ORG01.ogn/2EEB5DF465E44B23B9DBB66E3D906C27.dpt/42E3E049CB884F16990D5BB5B8C9E681.pos/PSN01@42E3E049CB884F16990D5BB5B8C9E681.psm' like concat(SA_Task.sExecutorFID, '%')) and (SA_Task.sExecutorFID like '/%')) group by SA_Task.sFlowID)) ) ORDER BY sCreateTime DESC LIMIT 0, 20; 可能的原因: 无
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 TaskProcedure.taskEndQuery(TaskProcedure.java:156)
... 26 more
Caused by: com.justep.exception.KSQLException: 编码: JUSTEP154115; 提示: KSQL语法错误, SA_Task.sFlowID^^,SA_Task.sCreat中未找到需要的元素, 位置331期望是); 可能的原因: 无
at com.justep.exception.KSQLException.create(Unknown Source)
at com.justep.system.ksql.token.TokenManager.matchToken(Unknown Source)
at com.justep.system.ksql.token.TokenManager.match(Unknown Source)
at com.justep.system.ksql.parser.AbstractParser.unaryExpression(Unknown Source)
at com.justep.system.ksql.parser.AbstractParser.multiExpression(Unknown Source)
at com.justep.system.ksql.parser.AbstractParser.addExpression(Unknown Source)
at com.justep.system.ksql.parser.AbstractParser.compExpression(Unknown Source)
at com.justep.system.ksql.parser.AbstractParser.notExpression(Unknown Source)
at com.justep.system.ksql.parser.AbstractParser.andExpression(Unknown Source)
at com.justep.system.ksql.parser.AbstractParser.orExpression(Unknown Source)
at com.justep.system.ksql.parser.AbstractParser.unaryExpression(Unknown Source)
at com.justep.system.ksql.parser.AbstractParser.multiExpression(Unknown Source)
at com.justep.system.ksql.parser.AbstractParser.addExpression(Unknown Source)
at com.justep.system.ksql.parser.AbstractParser.compExpression(Unknown Source)
at com.justep.system.ksql.parser.AbstractParser.notExpression(Unknown Source)
at com.justep.system.ksql.parser.AbstractParser.andExpression(Unknown Source)
at com.justep.system.ksql.parser.AbstractParser.orExpression(Unknown Source)
at com.justep.system.ksql.parser.AbstractParser.conditionExpression(Unknown Source)
at com.justep.system.ksql.parser.AbstractParser.whereExpression(Unknown Source)
at com.justep.system.ksql.parser.AbstractParser.selectSentenceNode(Unknown Source)
at com.justep.system.ksql.parser.QueryParser.start(Unknown Source)
at com.justep.system.ksql.parser.QueryParser.start(Unknown Source)
at com.justep.system.ksql.parser.AbstractParser.parse(Unknown Source)
... 31 more

PS:我将ksql转化成sql是可以正常执行的。

zhaixin

ksql中的问题是
WHERE (( SA_Task.sFlowID,SA_Task.sCreateTime)  in
两个关系一起 in

cyjinli

zhaixin 发表于 2014-1-17 09:54
ksql中的问题是
WHERE (( SA_Task.sFlowID,SA_Task.sCreateTime)  in
两个关系一起 in

这个查询的含义是某个流程实例中，最新的一条记录。的确是要两个同时匹配的。

zhaixin

cyjinli

zhaixin 发表于 2014-1-17 10:20

其实我想要的效果是：
SELECT COLUMN_NAME(S)
FROM TABLE_NAME
WHERE (COLUMN_NAME1,COLUMN_NAME2) IN.....

也就是说可能不支持这种语法。

我换了一种写法。报错如下所示：
Caused by: com.justep.exception.BusinessException: 编码: JUSTEP150210; 提示: 执行KSQL出错, KSQL: SELECT SA_Task.*,SA_Task_1.sStatusID as sInstanceStatusID,SA_Task_1.sStatusName as sInstanceStatusName,SA_Task_1.sData2 as sInstanceData2,SA_Task_1.sData3 as sInstanceData3,SA_Task_1.sProcessName as sInstanceProcessName  FROM SA_Task SA_Task  optional  join SA_Task SA_Task_1 on SA_Task.sFlowID = SA_Task_1 join (select SA_Task2.sFlowID as sFlowID,(max( SA_Task2.sCreateTime )) as sCreateTime from SA_Task SA_Task2 where ((SA_Task2.sStatusID='tesReady') or (SA_Task2.sStatusID='tesExecuting')) and (('/ORG01.ogn/PSN01@ORG01.psm' like concat(SA_Task2.sExecutorFID, '%') or '/ORG01.ogn/2EEB5DF465E44B23B9DBB66E3D906C27.dpt/E4DB86CF3FA949689F8741316AA15454.pos/PSN01@E4DB86CF3FA949689F8741316AA15454.psm' like concat(SA_Task2.sExecutorFID, '%') or '/ORG01.ogn/2EEB5DF465E44B23B9DBB66E3D906C27.dpt/42E3E049CB884F16990D5BB5B8C9E681.pos/PSN01@42E3E049CB884F16990D5BB5B8C9E681.psm' like concat(SA_Task2.sExecutorFID, '%')) and (SA_Task2.sExecutorFID like '/%')) group by SA_Task2.sFlowID) newTask  on SA_Task.sFlowID = newTask.sFlowID and SA_Task.sCreateTime = newTask.sCreateTime ORDER BY sCreateTime DESC LIMIT 0, 20; 可能的原因: 无
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 TaskProcedure.taskEndQuery(TaskProcedure.java:161)
... 26 more
Caused by: com.justep.exception.BusinessException: 编码: JUSTEP154053; 提示: KSQL语法错误, columns中的列sFlowID必须包含在select查出列中; 可能的原因: 无
at com.justep.exception.BusinessException.create(Unknown Source)
at com.justep.util.Utils.check(Utils.java:76)
at com.justep.system.ksql.parser.AbstractParser.handleColumns(Unknown Source)
at com.justep.system.ksql.parser.AbstractParser.selectSentenceNode(Unknown Source)
at com.justep.system.ksql.parser.QueryParser.start(Unknown Source)
at com.justep.system.ksql.parser.QueryParser.start(Unknown Source)
at com.justep.system.ksql.parser.AbstractParser.parse(Unknown Source)
... 31 more

cyjinli

cyjinli 发表于 2014-1-17 10:33
其实我想要的效果是：
SELECT COLUMN_NAME(S)
FROM TABLE_NAME

在新的写法中， SA_Task.*  替换成具体的字段，就可以解决问题了。谢谢！

zhaixin

这个查询太复杂，不适合用ksql实现，用sql实现吧

cyjinli

zhaixin 发表于 2014-1-17 10:44
这个查询太复杂，不适合用ksql实现，用sql实现吧

这是在任务中心显示的，要求是已办和提交的记录仅显示最新的。SQL会简单一些。
谢谢！请结贴。