KSQL语句 QUERY_ALIAS 问题

lkaih

我现在有一条KSQL语句，
select z ,z.CodeSM,z.NameSM,z.SpecSM,z.GradSM,z.BranSM,z.UnitSM,z.aPriSM, z.NumSM,z.MonSM,z.PID from (select a ,a.CodeSM,a.NameSM,a.SpecSM,a.GradSM,a.BranSM,a.UnitSM,round(SUM(b.aMonWL) / SUM(b.aNumWL),2) as aPriSM,SUM(b.aNumWL) as NumSM,SUM(b.aMonWL) as MonSM,a.PID_SM as PID from SetMat a optional join ( select l.mFidWL,l.CodeWL,l.aPriWL,l.aNumWL,l.aMonWL,i.ebNameWI from WarInfor i optional join WarList l on i =l.MID_WI_WL and (i.Mark_WI = 'iow' or i.Mark_WI = 'irw' or i.Mark_WI = 'inw') and l.aNumWL <> 0  and i.pNameWI ='西南大学经济管理实验楼工程' and i.wNameWI ='西南大学经管楼仓库' and i.ebNameWI = '赵吉勇' and (i.aDateWI >= '2013-12-01 00:00:00.000' and i.aDateWI <= '2013-12-30 00:00:00.000') ) b on b.CodeWL like a.CodeSM +'%' group by a ,a.CodeSM,a.NameSM,a.SpecSM,a.GradSM,a.BranSM,a.UnitSM,a.PID_SM ) z where z.NumSM is not null order by z.CodeSM

在没有加外面的select这一层就可以运行，加了就报错：
Caused by: com.justep.model.exception.ModelException: 执行ksql:select z ,z.CodeSM,z.NameSM,z.SpecSM,z.GradSM,z.BranSM,z.UnitSM,z.aPriSM, z.NumSM,z.MonSM,z.PID from (select a ,a.CodeSM,a.NameSM,a.SpecSM,a.GradSM,a.BranSM,a.UnitSM,round(SUM(b.aMonWL) / SUM(b.aNumWL),2) as aPriSM,SUM(b.aNumWL) as NumSM,SUM(b.aMonWL) as MonSM,a.PID_SM as PID from SetMat a optional join ( select l.mFidWL,l.CodeWL,l.aPriWL,l.aNumWL,l.aMonWL,i.ebNameWI from WarInfor i optional join WarList l on i =l.MID_WI_WL and (i.Mark_WI = 'iow' or i.Mark_WI = 'irw' or i.Mark_WI = 'inw') and l.aNumWL <> 0  and i.pNameWI ='西南大学经济管理实验楼工程' and i.wNameWI ='西南大学经管楼仓库' and i.ebNameWI = '赵吉勇' and (i.aDateWI >= '2013-12-01 00:00:00.000' and i.aDateWI <= '2013-12-30 00:00:00.000') ) b on b.CodeWL like a.CodeSM +'%' group by a ,a.CodeSM,a.NameSM,a.SpecSM,a.GradSM,a.BranSM,a.UnitSM,a.PID_SM ) z where z.NumSM is not null order by z.CodeSM LIMIT 0, 50时出错！
        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.KSQL.select(Unknown Source)
        at KSQLProcedure.query(KSQLProcedure.java:15)
        ... 25 more
Caused by: com.justep.model.exception.ModelException: 错误的类型：z -> QUERY_ALIAS
        at com.justep.system.ksql.visitor.SQLVisitor.visit(Unknown Source)
        at com.justep.system.ksql.node.IdentiferNode.accept(Unknown Source)
        at com.justep.system.ksql.visitor.SQLVisitor.visitChildren(Unknown Source)
        at com.justep.system.ksql.visitor.SQLVisitor.visit(Unknown Source)
        at com.justep.system.ksql.node.SelectColumnNode.accept(Unknown Source)
        at com.justep.system.ksql.visitor.SQLVisitor.visitChildren(Unknown Source)
        at com.justep.system.ksql.visitor.SQLVisitor.visit(Unknown Source)
        at com.justep.system.ksql.visitor.QueryVisitor.visit(Unknown Source)
        at com.justep.system.ksql.node.SelectListNode.accept(Unknown Source)
        at com.justep.system.ksql.visitor.SQLVisitor.visitChildren(Unknown Source)
        at com.justep.system.ksql.visitor.SQLVisitor.visit(Unknown Source)
        at com.justep.system.ksql.node.SelectSentenceNode.accept(Unknown Source)
        at com.justep.system.ksql.visitor.SQLVisitor.visitChildren(Unknown Source)
        at com.justep.system.ksql.visitor.QueryVisitor.visit(Unknown Source)
        at com.justep.system.ksql.node.QuerySentence.accept(Unknown Source)
        ... 30 more

lkaih

请问我应该怎么改写这条KSQL语句呢

jishuang

KSQL嵌套子查询支持的有限，复杂的KSQL推荐用sql实现