本帖最后由 sbq 于 2022-6-17 11:07 编辑
oracle数据库，在数据库中可以执行成功，但是放在SmartBI源库SQL脚本中报“无效字符”
执行sql日志：
ERROR node.GenericNode.handleExecuteError:150 - Node execution failed.(id:ba21381a9e2536a2e55b3e2e1fb3bff2,name:SOURCE_SQL_NODE)
smartbix.datamining.engine.execute.exception.ExecuteException: Fail to excute sql [1] :
create table BI_C_DJH as
SELECT s.dw_mc 清洗前委托单位,CLR2.DW_MC_clear 清洗后委托单位,z.sjdw 清洗前证书单位,CLR1.DW_MC_clear 清洗后证书单位
,pr.sample_name 委托样品,z.qj 证书样品
,ct.hymc 委托单位行业,A1.NAME 委托单位所在省, A2.NAME 委托单位所在市
,ct_zs.hymc 证书单位行业,BIDW_ZS.Province 证书单位所在省,BIDW_ZS.City 证书单位所在市
,decode(substr(s.zdbh,0,1),'8','送检','5','上门') 委托类型
,svc.servicename 服务名称,z.zslx 证书类型
,decode(q.IS_JZ,'0','否','1','是') 是否结账, BM.BM_MC 检测部门, GW.GWMC 专业组
,decode(DJH.SFFB,'0','标准','1','非标') 是否非标,DJH.FJFREASON 附加费原因
,DJH.JCF 检测费,DJH.FJF 附加费,DJH.SMF 上门费,DJH.JJF 加急费,DJH.XLF 修理费,DJH.JTF 交通费,DJH.YJF 邮寄费,DJH.ZHF 综合业务费,DJH.QTF 其它费
,DJH.BGXGF 报告修改费,DJH.TOTAL 合计,DJH.ZK 折扣值,DJH.ZSF 实收费
,1 数量
,s.entrust_date 委托日期,DJH.EXPIRE_DATE 超期日期
,'取件地点变更' 变更类型,' ' 变更原因
,s.sjd_id,s.zdbh,s.dw_id,q.sample_id,q.lzdh,q.base_id,djh.djh
,0 自行委托数量,0 非自行委托数量,0 自行委托实收费,0 非自行委托实收费,z.zs_bh 证书编号, decode(djh.zsflag,'1','出具证书','0','不出具证书') 是否出具证书
,0 证书数量, to_char(s.entrust_date,'yyyy') 委托日期年份, to_char(s.entrust_date,'yyyy-mm') 委托日期年月, to_char(s.entrust_date,'mm') 委托日期月份
, to_char(s.entrust_date,'yyyy-Q') 委托日期年季, to_char(s.entrust_date,'Q') 委托日期季度
,DW.Htzk 委托单位合同折扣, decode(DW.VIP,0,'非VIP',1,'VIP') 委托单位VIP
,BIDW_ZS.Htzk 证书单位合同折扣, decode(BIDW_ZS.VIP,0,'非VIP',1,'VIP') 证书单位VIP
,PR_FY.sample_name 入费样品,PR_FY.base_id as base_id_fy
,to_char(dw.create_time,'yyyy-mm-dd') 委托单位注册日期
,z.zzcs 清洗前制造厂商,CLR3.DW_MC_clear 清洗后制造厂商,z.jdjl 检定结论
,q.FINISH_DATE 完成日期, djh.return_flag 退件标识,djh.delay_flag 延期标识
, to_char(q.FINISH_DATE,'yyyy-mm') 完成日期年月, to_char(q.FINISH_DATE,'mm') 完成日期月份
, to_char(q.FINISH_DATE,'yyyy-Q') 完成日期年季, to_char(q.FINISH_DATE,'Q') 完成日期季度
, q.QJ_SJMC 流转单样品实际名称
,q.JZRQ 结账日期 , to_char(q.JZRQ,'yyyy') 结账日期年份, to_char(q.JZRQ,'yyyy-mm') 结账日期年月, to_char(q.JZRQ,'mm') 结账日期月份
, to_char(q.JZRQ,'yyyy-Q') 结账日期年季, to_char(q.JZRQ,'Q') 结账日期季度
,DJH.total_before 业务应收
,decode(PR_FY.sample_identification,'T','标准样品','F','非标样品') 入费样品的样品标识
, to_char(q.FINISH_DATE,'yyyy') 完成日期年份, BIDW_ZS.CY 证书客户产业
,s.zsdw 清洗前委托书上的证书单位,CLR4.DW_MC_clear 清洗后委托书上的证书单位,qjfy.sjmc 入费样品实际名称
from BI_T_SJD s
left join BI_T_SJD_QJ q on s.SJD_ID=q.SJD_ID
left join BI_T_SJD_QJ_DJH djh on djh.SAMPLE_ID = q.SAMPLE_ID
left join BI_T_ZS z on z.SAMPLE_ID=DJH.SAMPLE_ID and z.DJH=DJH.DJH
left join BI_t_sjd_fy_qj qjfy on qjfy.SAMPLE_ID=DJH.SAMPLE_ID and qjfy.DJH=DJH.DJH
left join bi_t_service svc on svc.ID=q.service_type
LEFT JOIN BI_T_PRICE_ABILITY_BASE PR ON PR.BASE_ID = Q.BASE_ID
LEFT JOIN BI_T_PRICE_ABILITY_BASE PR_FY ON PR_FY.BASE_ID = QJFY.BASE_ID
LEFT JOIN BI_T_SJDW DW ON DW.DW_ID = S.DW_ID
LEFT JOIN BI_DWMC_CLEAR CLR1 ON CLR1.DW_MC = z.sjdw
LEFT JOIN BI_DW BIDW_ZS ON CLR1.DW_MC_CLEAR = BIDW_ZS.DW_MC_CLEAR
LEFT JOIN BI_DWMC_CLEAR CLR2 ON CLR2.DW_MC = dw.dw_mc
LEFT JOIN BI_DW BIDW_SJ ON CLR2.DW_MC_CLEAR = BIDW_SJ.DW_MC_CLEAR
LEFT JOIN BI_DWMC_CLEAR CLR3 ON CLR3.DW_MC = z.zzcs
LEFT JOIN BI_DWMC_CLEAR CLR4 ON CLR4.DW_MC = s.zsdw
LEFT JOIN BI_T_BM BM ON q.jcbm=BM.BM_BH
LEFT JOIN BI_T_GW GW ON q.GW=GW.GWID
left join BI_T_CUSTOMERTYPE ct on ct.hydm=dw.hy
left join BI_T_CUSTOMERTYPE ct_zs on ct_zs.hydm=BIDW_ZS.hy
LEFT JOIN BI_T_ADMINISTRATION_DIVISION A1 ON DW.XZQ_PID = A1.ID
LEFT JOIN BI_T_ADMINISTRATION_DIVISION A2 ON DW.XZQ_ID = A2.ID
where q.lzdh is not null;
at smartbix.datamining.engine.datasource.DefaultDsExecutor.executeSqlList(DefaultDsExecutor.java:79) ~[EngineCore-1.0.jar:?]
at smartbix.datamining.engine.execute.node.preprocess.SourceSQLNode.execute(SourceSQLNode.java:70) ~[EngineCommonNode-1.0.jar:?]
at smartbix.datamining.engine.execute.node.GenericNode.start(GenericNode.java:119) [EngineCore-1.0.jar:?]
at smartbix.datamining.engine.experiment.execute.node.LocalNodeExecutor.run(LocalNodeExecutor.java:47) [EngineExperiment-1.0.jar:?]
at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511) [?:1.8.0_202-ea]
at java.util.concurrent.FutureTask.run(FutureTask.java:266) [?:1.8.0_202-ea]
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) [?:1.8.0_202-ea]
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) [?:1.8.0_202-ea]
at java.lang.Thread.run(Thread.java:748) [?:1.8.0_202-ea]
Caused by: java.sql.SQLSyntaxErrorException: ORA-00911: 无效字符
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:445) ~[ojdbc6-11.2.0.3.jar:11.2.0.3.0]
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396) ~[ojdbc6-11.2.0.3.jar:11.2.0.3.0]
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:879) ~[ojdbc6-11.2.0.3.jar:11.2.0.3.0]
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:450) ~[ojdbc6-11.2.0.3.jar:11.2.0.3.0]
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:192) ~[ojdbc6-11.2.0.3.jar:11.2.0.3.0]
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:531) ~[ojdbc6-11.2.0.3.jar:11.2.0.3.0]
at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:193) ~[ojdbc6-11.2.0.3.jar:11.2.0.3.0]
at oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:1033) ~[ojdbc6-11.2.0.3.jar:11.2.0.3.0]
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1329) ~[ojdbc6-11.2.0.3.jar:11.2.0.3.0]
at oracle.jdbc.driver.OracleStatement.executeInternal(OracleStatement.java:1909) ~[ojdbc6-11.2.0.3.jar:11.2.0.3.0]
at oracle.jdbc.driver.OracleStatement.execute(OracleStatement.java:1871) ~[ojdbc6-11.2.0.3.jar:11.2.0.3.0]
at oracle.jdbc.driver.OracleStatementWrapper.execute(OracleStatementWrapper.java:318) ~[ojdbc6-11.2.0.3.jar:11.2.0.3.0]
at smartbix.datamining.engine.datasource.DefaultDsExecutor.executeSqlList(DefaultDsExecutor.java:69) ~[EngineCore-1.0.jar:?]
... 8 more
2022-06-17 10:54:07.461 [205] INFO flow.DistributionFlow.fail:292 - Flow failed,(id:Iefefefef01816f656f65bf3b01816f718e8b002a,name:数据刷新)
| 
