异常代码:
20:17:49.331 [http-nio-8081-exec-6] ERROR c.a.d.f.s.StatFilter - [mergeSql,169] - merge sql error, dbType oracle, druid-1.2.8, sql : WITH
orgn AS(SELECt w.dwid,w.qk,w.qkdm,w.jhid FROM cd_orgn_source_attr w WHERe w.dwid='xAJLaafdjei321jlaj13nanfasX000113'),
pclog AS(SELECt p.orgn_id,p.sand_layer_group_name AS cw ,p.soak_volume,p.thickness,p.soak_thickness,p.perforate_orgn_top_depth top FROM pc_log_desc_infuse p WHERe p.soak_volume>0 AND p.sand_layer_group_name IS NOT NULL ORDER BY top ASC),
res AS(SELECt *FROM orgn w INNER JOIN pclog p ON w.jhid=p.orgn_id),
cwres AS (SELECt cw,orgn_id FROM res GROUP BY orgn_id,cw ORDER BY cw),
layerdata AS(SELECt distinct(cw) AS "cw",SUM(thickness) AS "sy",SUM(soak_thickness) AS "yx",(SELECT COUNT(c.orgn_id) FROM cwres c WHERe c.cw=r.cw ) AS "cs" FROM res r WHERe r.QKDM=? GROUP BY cw),
sumdata AS(SELECt SUM("cs") AS "cs_sum",SUM("sy") AS "sy_sum",SUM("yx") AS "yx_sum" FROM layerdata),
res1 AS(SELECt l.*,s.*,(l."sy"/NULLIF(s."sy_sum",0)*100) AS "p_sy",(l."yx"/NULLIF(s."yx_sum",0)*100) AS "p_yx",(l."cs"/NULLIF(s."cs_sum",0)*100)AS "p_cs" FROM layerdata l,sumdata s),
res2 AS(SELECt "cw",k AS "key",CASE
when v >= 0 and v < 35 then 'c1'
when v >= 35 and v < 40 then 'c2'
when v >= 40 and v < 50 then 'c3'
when v >= 50 and v < 75 then 'c4'
when v >= 75 and v <= 100 then 'c5'
END AS "value"
FROM res1 t UNPIVOT (v FOR k IN("p_cs","p_sy","p_yx")) t),
res3 AS (SELECt *FROM (select t."key",t."value",count(0) AS cc from res2 t group BY t."key",t."value") PIVOT (SUM(cc) for "value" in ('c1' as "c1",'c2' as "c2",'c3' as "c3",'c4' as "c4",'c5' as "c5")))
select * from res3
com.alibaba.druid.sql.parser.ParserException: syntax error, error in :' FROM res1 t UNPIVOT (v FOR k IN("p_cs","', expect ), actual null, pos 1371, line 18, column 26, token IDENTIFIER UNPIVOT
问题描述:
1.前端可以正常返回结果，但是后端控制台报错 错误代码:
2.输出的sql可以在sqlplus中正常执行
20:17:49.331 [http-nio-8081-exec-6] ERROR c.a.d.f.s.StatFilter - [mergeSql,169] - merge sql error, dbType oracle, druid-1.2.8, sql
问题分析及解决:
开始怀疑是不是使用了Oracle函数 PIVOT 和 UNPIVOT 导致的问题，转念一想阿里天天都在用 pivot 和 unpivot 这是不可能的，然后开始度娘......
方法1.（升级druid版本）(失败)
查询相关资料有人说是 druid版本过低(1.2.5),然后果断 升级成 druid-1.2.8....（然并卵）.
方法2.(修改application.yaml)中druid配置 （成功）
修改
spring:datasource:druid:filter:merge-sql：false
问题解决了，确实不报错了，可是这和掩耳盗铃有什么区别，简直是侮辱码畜的职业素养。
方法3.（勇敢面对问题）
回头仔细看下异常代码，
com.alibaba.druid.sql.parser.ParserException: syntax error, error in :
' FROM res1 t UNPIVOT (v FOR k IN("p_cs","', expect ), actual null, pos 1371, line 18, column 26, token IDENTIFIER UNPIVOT
at com.alibaba.druid.sql.parser.SQLParser.printError(SQLParser.java:841)
at com.alibaba.druid.sql.parser.SQLParser.accept(SQLParser.java:849
.......
还是得从SQL下手，
问题出现在红框的SQL语法问题，虽然在sqldevlop中可以正常运行但是还是不够严谨，修改成绿框中内容后，系统就正常了。
总结：
merge sql error 不是真正的ERROR，而是告诉你,骚年! 你的SQL水平有待提高......
