KSQL中子查询包含limit

engle_wang

在js中对data，添加了filter
lsfbFileData.setFilter("fCPFBIDFilter", "fCPFBID in (select t.fID from (select cp.* from YFCK_CPFBB cp where cp.fXMDM = '"+ this._params.fXMDM +"' and cp.fCPBM = '"+ this._params.fCPBM +"' and cp.fCPMC ='"+ this._params.fCPMC +"' and cp.fZT ='审批完成' order by cp.fFBSJ desc LIMIT 0, 1)t)")
平台报错，把KSQL打印的放到数据库执行没有问题。

SELECT YFCK_CP_FILELIST.*
FROM YFCK_CP_FILELIST YFCK_CP_FILELIST
WHERE ((fCPFBID IN (
SELECT t.fID
FROM (
SELECT *
FROM YFCK_CPFBB cp
WHERE cp.fXMDM = 'x-js-20170817001-4' AND cp.fCPBM = '测试产品002' AND cp.fCPMC ='testprod002' AND cp.fZT ='审批完成'
ORDER BY cp.fFBSJ DESC
LIMIT 0, 1)t)))
LIMIT 0, 20

错误信息如下：
Caused by: com.justep.exception.BusinessException: 编码: JUSTEP150210; 提示: 执行KSQL出错, KSQL: SELECT YFCK_CP_FILELIST.*  FROM YFCK_CP_FILELIST YFCK_CP_FILELIST WHERE ((fCPFBID in (select t.fID from (select cp.* from YFCK_CPFBB cp where cp.fXMDM = 'x-js-20170817001-4' and cp.fCPBM = '测试产品002' and cp.fCPMC ='testprod002' and cp.fZT ='审批完成' order by cp.fFBSJ desc LIMIT 0, 1)t))) 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:19)
        ... 26 more
Caused by: com.justep.exception.KSQLException: 编码: JUSTEP154115; 提示: KSQL语法错误,  cp.fFBSJ desc ^^LIMIT 0, 1)t)))中未找到需要的元素, 位置269期望是)
        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.joinedExpression(Unknown Source)
        at com.justep.system.ksql.parser.AbstractParser.fromNode(Unknown Source)
        at com.justep.system.ksql.parser.AbstractParser.selectSentenceNode(Unknown Source)
        at com.justep.system.ksql.parser.AbstractParser.selectSentenceNode(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.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

jishuang

单独执行
select cp.* from YFCK_CPFBB cp where cp.fXMDM = 'x-js-20170817001-4' and cp.fCPBM = '测试产品002' and cp.fCPMC ='testprod002' and cp.fZT ='审批完成' order by cp.fFBSJ desc LIMIT 0, 1
看看报错吗？

把 order by cp.fFBSJ desc去掉看看报错吗？

engle_wang

jishuang 发表于 2017-8-18 16:58
单独执行
select cp.* from YFCK_CPFBB cp where cp.fXMDM = 'x-js-20170817001-4' and cp.fCPBM = '测试产 ...

打印的ksql在数据库中执行是没问题的。
limit和order by这两条件我都需要删掉order by 也不行。。。

jishuang

ksql的语法和sql是由区别的，到数据库中执行正确ksql不一定 正确
而且ksql中用子查询有些情况下会有错误

limit和order by都去掉后提示的错误信息是什么？
关系名前加上概念别名.看看

engle_wang

jishuang 发表于 2017-8-18 17:18
ksql的语法和sql是由区别的，到数据库中执行正确ksql不一定 正确
而且ksql中用子查询有些情况下会有错误

1、加上别名也是一样的。
2、删除order by的错误信息：
Caused by: com.justep.exception.BusinessException: 编码: JUSTEP150210; 提示: 执行KSQL出错, KSQL: SELECT YFCK_CP_FILELIST.*  FROM YFCK_CP_FILELIST YFCK_CP_FILELIST WHERE ((YFCK_CP_FILELIST.fCPFBID in (select t.fID from (select cp.* from YFCK_CPFBB cp where cp.fXMDM = 'x-js-20170817001-4' and cp.fCPBM = '测试产品002' and cp.fCPMC ='testprod002' and cp.fZT ='审批完成' LIMIT 0, 1)t))) 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:19)
        ... 26 more
Caused by: com.justep.exception.KSQLException: 编码: JUSTEP154115; 提示: KSQL语法错误, cp.fZT ='审批完成' ^^LIMIT 0, 1)t)))中未找到需要的元素, 位置263期望是)
        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.joinedExpression(Unknown Source)
        at com.justep.system.ksql.parser.AbstractParser.fromNode(Unknown Source)
        at com.justep.system.ksql.parser.AbstractParser.selectSentenceNode(Unknown Source)
        at com.justep.system.ksql.parser.AbstractParser.selectSentenceNode(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.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

错误信息提示的位置，就是order by 和limit 语句的地方。。。

jishuang

看写法应该没问题，那应该就是ksql中子查询的缺陷，可以改用sql实现