It seems SUM(CASE WHEN...) does not supported yet. Could you log a JIRA?
2017-08-11 16:33 GMT+08:00 578300...@qq.com <believe...@foxmail.com>:
> Thank you billy,I know how to solve the problem, but there is another
> error.
>
>
> error
>
>
> [Kylin][ODBC 1.0(w) Driver]The REST query request failed, the error
> message is: Error while executing SQL "SELECT SUM((CASE WHEN ((
> EXTRACT(YEAR FROM "BASEINFO_REPAY_HIS_FACT_PARTATION"."CAL_DATE") = 2017)
> AND ( EXTRACT(MONTH FROM "BASEINFO_REPAY_HIS_FACT_PARTATION"."CAL_DATE")
> = 6)) THEN "BASEINFO_REPAY_HIS_FACT_PARTATION"."REMAIN_CAPITAL_INTEREST"
> ELSE 0 END)) AS "sum_Calculation_FIIAIBABGCIFEFEJ_ok" FROM
> "TN_QUERY"."BASEINFO_REPAY_HIS_FACT_PARTATION"
> "BASEINFO_REPAY_HIS_FACT_PARTATION" LEFT JOIN "TN_QUERY"."DIM_BP_PRODUCT"
> "DIM_BP_PRODUCT" ON ("BASEINFO_REPAY_HIS_FACT_PARTATION"."PRODUCT_CODE" =
> "DIM_BP_PRODUCT"."PRODUCT_CODE") LEFT JOIN "TN_QUERY"."DIM_BP_SALES_CHANNEL"
> "DIM_BP_SALES_CHANNEL" ON ("BASEINFO_REPAY_HIS_FACT_PARTATION"."BUSINESS_TYPE"
> = "DIM_BP_SALES_CHANNEL"."CHANNEL_CODE") LEFT JOIN
> "TN_QUERY"."DIM_FUND_CHANNEL" "DIM_FUND_CHANNEL" ON
> ("BASEINFO_REPAY_HIS_FACT_PARTATION"."ROOT_CODE" =
> "DIM_FUND_CHANNEL"."FUND_CHANNEL_CODE") LEFT JOIN "TN_QUERY"."DIM_ORG"
> "DIM_ORG" ON ("BASEINFO_REPAY_HIS_FACT_PARTATION"."BRANCH_CODE" =
> "DIM_ORG"."BRANCH_CODE") ": Can't find any realization. Please confirm with
> providers. SQL digest: fact table
> TN_QUERY.BASEINFO_REPAY_HIS_FACT_PARTATION,group
> by [],filter on [],with aggregates[FunctionDesc [expression=SUM,
> parameter=null, returnType=null]].
>
>
>
>
> It seems about "case when" but how to solve it? Thank you.
>
>
>
>
> ------------------ 原始邮件 ------------------
> 发件人: "Billy Liu";<billy...@apache.org>;
> 发送时间: 2017年8月10日(星期四) 晚上7:32
> 收件人: "dev"<dev@kylin.apache.org>;
>
> 主题: Re: The REST query request failed
>
>
>
> This sql is not generated by kylin, Kylin does not generate any query.
> Kylin is read only OLAP system. Kylin accepts SQL and returns query result.
>
> Please try that SQL in Kylin insight tab directly, if not work, you could
> adjust the statement to figure out which one is the incompatible one.
>
> 2017-08-10 17:26 GMT+08:00 578300...@qq.com <believe...@foxmail.com>:
>
> > You mean that the tableau's calculation field query and kylin can not be
> > well fused?
> > Beacause the sql is automatically generated by kylin on the basis of
> > tableau's calculation field.
> > And the environment is:
> > jdk1.7.0_80
> > hadoop-2.5.0-cdh5.3.9
> > hbase-0.98.6-cdh5.3.9
> > apache-kylin-1.6.0
> > tableau 9
> >
> >
> > ------------------ 原始邮件 ------------------
> > 发件人: "Billy Liu";<billy...@apache.org>;
> > 发送时间: 2017年8月10日(星期四) 下午5:05
> > 收件人: "dev"<dev@kylin.apache.org>;
> >
> > 主题: Re: The REST query request failed
> >
> >
> >
> > The error is clear, some sql grammar did not work in your case, such as
> "{d
> > '2017-06-30'))}"
> >
> > 2017-08-10 15:58 GMT+08:00 578300...@qq.com <believe...@foxmail.com>:
> >
> > > Oh，I see.
> > > I will redescribe with word.
> > >
> > >
> > > I created a calculated field in tableau to judge the date(统计日期) is or
> not
> > > the last month of last year:
> > > YEAR([统计日期])=YEAR(DATEADD('year',-1,[截止日期])) and MONTH([统计日期])=12
> > >
> > >
> > >
> > > when I use the calculated field ,there is an error in kylin.log:
> > > 2017-08-10 15:44:27,279 ERROR [http-bio-7070-exec-2]
> > > controller.BasicController:44 :
> > > org.apache.kylin.rest.exception.InternalErrorException: Encountered
> ")"
> > > at line 1, column 154. Was expecting: "}" ...
> > > at org.apache.kylin.rest.service.
> QueryService.doQueryWithCache(
> > > QueryService.java:391)
> > > at org.apache.kylin.rest.controller.QueryController.
> > > query(QueryController.java:69)
> > > at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> > > at sun.reflect.NativeMethodAccessorImpl.invoke(
> > > NativeMethodAccessorImpl.java:57)
> > > at sun.reflect.DelegatingMethodAccessorImpl.invoke(
> > > DelegatingMethodAccessorImpl.java:43)
> > > at java.lang.reflect.Method.invoke(Method.java:606)
> > > at org.springframework.web.method.support.
> > InvocableHandlerMethod.
> > > doInvoke(InvocableHandlerMethod.java:221)
> > > at org.springframework.web.method.support.
> > InvocableHandlerMethod.
> > > invokeForRequest(InvocableHandlerMethod.java:136)
> > > at org.springframework.web.servlet.mvc.method.annotation.
> > > ServletInvocableHandlerMethod.invokeAndHandle(
> > > ServletInvocableHandlerMethod.java:104)
> > > at org.springframework.web.servlet.mvc.method.annotation.
> > > RequestMappingHandlerAdapter.invokeHandleMethod(
> > > RequestMappingHandlerAdapter.java:743)
> > > at org.springframework.web.servlet.mvc.method.annotation.
> > > RequestMappingHandlerAdapter.handleInternal(
> > RequestMappingHandlerAdapter.
> > > java:672)
> > > at org.springframework.web.servlet.mvc.method.
> > > AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.
> > java:82)
> > > at org.springframework.web.servlet.DispatcherServlet.
> > > doDispatch(DispatcherServlet.java:933)
> > > at org.springframework.web.servlet.DispatcherServlet.
> > > doService(DispatcherServlet.java:867)
> > > at org.springframework.web.servlet.FrameworkServlet.
> > > processRequest(FrameworkServlet.java:951)
> > > at org.springframework.web.servlet.FrameworkServlet.
> > > doPost(FrameworkServlet.java:853)
> > > at javax.servlet.http.HttpServlet
> .service(HttpServlet.java:650)
> > > at org.springframework.web.servlet.FrameworkServlet.
> > > service(FrameworkServlet.java:827)
> > > at javax.servlet.http.HttpServlet
> .service(HttpServlet.java:731)
> > > at org.apache.catalina.core.ApplicationFilterChain.
> > > internalDoFilter(ApplicationFilterChain.java:303)
> > > at org.apache.catalina.core.ApplicationFilterChain.doFilter(
> > > ApplicationFilterChain.java:208)
> > > at org.apache.tomcat.websocket.server.WsFilter.doFilter(
> > > WsFilter.java:52)
> > > at org.apache.catalina.core.ApplicationFilterChain.
> > > internalDoFilter(ApplicationFilterChain.java:241)
> > > at org.apache.catalina.core.ApplicationFilterChain.doFilter(
> > > ApplicationFilterChain.java:208)
> > > at org.springframework.security.web.FilterChainProxy$
> > > VirtualFilterChain.doFilter(FilterChainProxy.java:330)
> > > at org.springframework.security.web.access.intercept.
> > > FilterSecurityInterceptor.invoke(FilterSecurityInterceptor.java:118)
> > > at org.springframework.security.web.access.intercept.
> > > FilterSecurityInterceptor.doFilter(FilterSecurityInterceptor.java:84)
> > > at org.springframework.security.web.FilterChainProxy$
> > > VirtualFilterChain.doFilter(FilterChainProxy.java:342)
> > > at org.springframework.security.web.access.
> > > ExceptionTranslationFilter.doFilter(ExceptionTranslationFilt
> er.java:113)
> > > at org.springframework.security.web.FilterChainProxy$
> > > VirtualFilterChain.doFilter(FilterChainProxy.java:342)
> > > at org.springframework.security.web.session.
> > > SessionManagementFilter.doFilter(SessionManagementFilter.java:103)
> > > at org.springframework.security.web.FilterChainProxy$
> > > VirtualFilterChain.doFilter(FilterChainProxy.java:342)
> > > at org.springframework.security.web.authentication.
> > > AnonymousAuthenticationFilter.doFilter(AnonymousAuthenticationFilter.
> > > java:113)
> > > at org.springframework.security.web.FilterChainProxy$
> > > VirtualFilterChain.doFilter(FilterChainProxy.java:342)
> > > at org.springframework.security.web.servletapi.
> > > SecurityContextHolderAwareRequestFilter.doFilter(
> > > SecurityContextHolderAwareRequestFilter.java:54)
> > > at org.springframework.security.web.FilterChainProxy$
> > > VirtualFilterChain.doFilter(FilterChainProxy.java:342)
> > > at org.springframework.security.web.savedrequest.
> > > RequestCacheAwareFilter.doFilter(RequestCacheAwareFilter.java:45)
> > > at org.springframework.security.web.FilterChainProxy$
> > > VirtualFilterChain.doFilter(FilterChainProxy.java:342)
> > > at org.springframework.security.web.authentication.www.
> > > BasicAuthenticationFilter.doFilter(BasicAuthenticationFilter.java:201)
> > > at org.springframework.security.web.FilterChainProxy$
> > > VirtualFilterChain.doFilter(FilterChainProxy.java:342)
> > > at org.springframework.security.web.authentication.ui.
> > > DefaultLoginPageGeneratingFilter.doFilter(DefaultLoginPageGe
> neratingFilt
> > > er.java:91)
> > > at org.springframework.security.web.FilterChainProxy$
> > > VirtualFilterChain.doFilter(FilterChainProxy.java:342)
> > > at org.springframework.security.web.authentication.
> > > AbstractAuthenticationProcessingFilter.doFilter(
> > > AbstractAuthenticationProcessingFilter.java:183)
> > > at org.springframework.security.web.FilterChainProxy$
> > > VirtualFilterChain.doFilter(FilterChainProxy.java:342)
> > > at org.springframework.security.web.authentication.logout.
> > > LogoutFilter.doFilter(LogoutFilter.java:105)
> > > at org.springframework.security.web.FilterChainProxy$
> > > VirtualFilterChain.doFilter(FilterChainProxy.java:342)
> > > at org.springframework.security.web.context.
> > > SecurityContextPersistenceFilter.doFilter(SecurityContextPer
> sistenceFilt
> > > er.java:87)
> > > at org.springframework.security.web.FilterChainProxy$
> > > VirtualFilterChain.doFilter(FilterChainProxy.java:342)
> > > at org.springframework.security.web.FilterChainProxy.
> > > doFilterInternal(FilterChainProxy.java:192)
> > > at org.springframework.security.web.FilterChainProxy.doFilter(
> > > FilterChainProxy.java:160)
> > > at org.springframework.web.filter.DelegatingFilterProxy.
> > > invokeDelegate(DelegatingFilterProxy.java:343)
> > > at org.springframework.web.filter.DelegatingFilterProxy.
> > doFilter(
> > > DelegatingFilterProxy.java:260)
> > > at org.apache.catalina.core.ApplicationFilterChain.
> > > internalDoFilter(ApplicationFilterChain.java:241)
> > > at org.apache.catalina.core.ApplicationFilterChain.doFilter(
> > > ApplicationFilterChain.java:208)
> > > at com.thetransactioncompany.cors.CORSFilter.doFilter(
> > > CORSFilter.java:209)
> > > at com.thetransactioncompany.cors.CORSFilter.doFilter(
> > > CORSFilter.java:244)
> > > at org.apache.catalina.core.ApplicationFilterChain.
> > > internalDoFilter(ApplicationFilterChain.java:241)
> > > at org.apache.catalina.core.ApplicationFilterChain.doFilter(
> > > ApplicationFilterChain.java:208)
> > > at org.apache.catalina.core.StandardWrapperValve.invoke(
> > > StandardWrapperValve.java:220)
> > > at org.apache.catalina.core.StandardContextValve.invoke(
> > > StandardContextValve.java:122)
> > > at org.apache.catalina.authenticator.AuthenticatorBase.invoke(
> > > AuthenticatorBase.java:505)
> > > at org.apache.catalina.core.StandardHostValve.invoke(
> > > StandardHostValve.java:169)
> > > at org.apache.catalina.valves.ErrorReportValve.invoke(
> > > ErrorReportValve.java:103)
> > > at org.apache.catalina.valves.AccessLogValve.invoke(
> > > AccessLogValve.java:956)
> > > at org.apache.catalina.core.StandardEngineValve.invoke(
> > > StandardEngineValve.java:116)
> > > at org.apache.catalina.connector.CoyoteAdapter.service(
> > > CoyoteAdapter.java:436)
> > > at org.apache.coyote.http11.AbstractHttp11Processor.process(
> > > AbstractHttp11Processor.java:1078)
> > > at org.apache.coyote.AbstractProt
> ocol$AbstractConnectionHandler.
> > > process(AbstractProtocol.java:625)
> > > at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.
> > > run(JIoEndpoint.java:316)
> > > at java.util.concurrent.ThreadPoolExecutor.runWorker(
> > > ThreadPoolExecutor.java:1145)
> > > at java.util.concurrent.ThreadPoolExecutor$Worker.run(
> > > ThreadPoolExecutor.java:615)
> > > at org.apache.tomcat.util.threads.TaskThread$
> > WrappingRunnable.run(
> > > TaskThread.java:61)
> > > at java.lang.Thread.run(Thread.java:745)
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > > and in kylin.log,the sql is:
> > > SELECT (CASE WHEN (( EXTRACT(YEAR FROM "BASEINFO_REPAY_HIS_FACT_
> > PARTATION"."CAL_DATE")
> > > = EXTRACT(YEAR FROM TIMESTAMPADD(SQL_TSI_YEAR,-1,{d '2017-06-30'))})
> > > AND ( EXTRACT(MONTH FROM "BASEINFO_REPAY_HIS_FACT_PARTA
> TION"."CAL_DATE")
> > > = 12)) THEN 1 WHEN NOT (( EXTRACT(YEAR FROM "BASEINFO_REPAY_HIS_FACT_
> > PARTATION"."CAL_DATE")
> > > = EXTRACT(YEAR FROM TIMESTAMPADD(SQL_TSI_YEAR,-1,{d '2017-06-30'))})
> > > AND ( EXTRACT(MONTH FROM "BASEINFO_REPAY_HIS_FACT_PARTA
> TION"."CAL_DATE")
> > > = 12)) THEN 0 ELSE NULL END) AS "Calculation_FJFAIBABFECFFCFC" FROM
> > > "TN_QUERY"."BASEINFO_REPAY_HIS_FACT_PARTATION"
> "BASEINFO_REPAY_HIS_FACT_
> > PARTATION"
> > > GROUP BY (CASE WHEN (( EXTRACT(YEAR FROM "BASEINFO_REPAY_HIS_FACT_
> > PARTATION"."CAL_DATE")
> > > = EXTRACT(YEAR FROM TIMESTAMPADD(SQL_TSI_YEAR,-1,{d '2017-06-30'))})
> > > AND ( EXTRACT(MONTH FROM "BASEINFO_REPAY_HIS_FACT_PARTA
> TION"."CAL_DATE")
> > > = 12)) THEN 1 WHEN NOT (( EXTRACT(YEAR FROM "BASEINFO_REPAY_HIS_FACT_
> > PARTATION"."CAL_DATE")
> > > = EXTRACT(YEAR FROM TIMESTAMPADD(SQL_TSI_YEAR,-1,{d '2017-06-30'))})
> > > AND ( EXTRACT(MONTH FROM "BASEINFO_REPAY_HIS_FACT_PARTA
> TION"."CAL_DATE")
> > > = 12)) THEN 0 ELSE NULL END)
> > >
> > >
> > >
> > >
> > > The sql generated Automatically.
> > > Thank you!
> > >
> > >
> > >
> > >
> > >
> > >
> > > ------------------ 原始邮件 ------------------
> > > 发件人: "Billy Liu";<billy...@apache.org>;
> > > 发送时间: 2017年8月10日(星期四) 下午3:20
> > > 收件人: "dev"<dev@kylin.apache.org>;
> > >
> > > 主题: Re: The REST query request failed
> > >
> > >
> > >
> > > Attachment is not allowed in the mailer, you could upload the images to
> > > some public image services.
> > >
> > > 2017-08-09 19:21 GMT+08:00 578300...@qq.com <believe...@foxmail.com>:
> > >
> > > >
> > > > The picture may not show up, I put them in the attachment.Thank you!
> > > >
> > > > ------------------ 原始邮件 ------------------
> > > > *发件人:* "578300...@qq.com";<believe...@foxmail.com>;
> > > > *发送时间:* 2017年8月9日(星期三) 晚上6:08
> > > > *收件人:* "dev"<dev@kylin.apache.org>;
> > > > *主题:* The REST query request failed
> > > >
> > > > Hello!
> > > > when I use tableau to connect to kylin,some error arised.
> > > >
> > > > I created a calculated field in tableau
> > > >
> > > >
> > > > and in kylin.log,the sql is:
> > > >
> > > >
> > > > How is this caused and how can I solve it. Thank you very much!
> > > >
> > > >
> > >
> >
>
