sql语句 执行
SELECT 汇总,名称,值 FROM (
SELECT 科室 AS 名称,round(SUM(金额)/10000,2) AS 总收入,round(SUM(DECODE(TT.名称,'药品费',金额,0))/10000,2) AS 药品收入,
round(SUM(DECODE(TT.名称,'药品费',0,金额)) /10000,2) AS 非药收入
FROM
(SELECT DA.系统序号,NVL(KS.名称,'未知') AS 科室 FROM INQ_D住院档案 DA LEFT JOIN DOC_T科室档案 KS ON KS.系统序号=DA.当前科室I
UNION ALL
SELECT DA.系统序号,NVL(KS.名称,'未知') AS 科室 FROM INQ_D住院档案Z DA LEFT JOIN DOC_T科室档案 KS ON KS.系统序号=DA.当前科室I
) T INNER JOIN (
SELECT JSLB.住院序号I ,FB.名称,SUM(FYMX.执行金额*SIGN(JSLB.结算总额)) AS 金额
FROM inq_d住院费用明细 FYMX
INNER JOIN inq_d住院结算明细 JSMX ON FYMX.系统序号=JSMX.明细序号I
INNER JOIN inq_d住院结算列表 JSLB ON JSMX.结算序号I=decode(JSLB.冲销状态N,2,JSLB.冲销序号I,JSLB.系统序号)
INNER JOIN doc_t收费项目价格表 JG ON JG.系统序号=FYMX.收费项目序号I
INNER JOIN doc_t收费项目 XM ON JG.项目序号I=XM.系统序号
INNER JOIN DOC_VT费用类别_院长查询 FB ON XM.费用类别I=FB.系统序号
--WHERE JSLB.隶属机构I='@当前机构ID@' AND JSLB.结算时间 BETWEEN to_date('{统计时间_开始}','yyyy-mm-dd hh24:mi:ss') AND to_date('{统计时间_截止}','yyyy-mm-dd hh24:mi:ss')
GROUP BY FB.名称,JSLB.住院序号I
UNION ALL
SELECT JSLB.住院序号I ,FB.名称,SUM(FYMX.执行金额*SIGN(JSLB.结算总额)) AS 金额
FROM inq_d住院费用明细Z FYMX
INNER JOIN inq_d住院结算明细 JSMX ON FYMX.系统序号=JSMX.明细序号I
INNER JOIN inq_d住院结算列表 JSLB ON JSMX.结算序号I=decode(JSLB.冲销状态N,2,JSLB.冲销序号I,JSLB.系统序号)
INNER JOIN doc_t收费项目价格表 JG ON JG.系统序号=FYMX.收费项目序号I
INNER JOIN doc_t收费项目 XM ON JG.项目序号I=XM.系统序号
INNER JOIN DOC_VT费用类别_院长查询 FB ON XM.费用类别I=FB.系统序号
--WHERE JSLB.隶属机构I='@当前机构ID@' AND JSLB.结算时间 BETWEEN to_date('{统计时间_开始}','yyyy-mm-dd hh24:mi:ss') AND to_date('{统计时间_截止}','yyyy-mm-dd hh24:mi:ss')
GROUP BY FB.名称,JSLB.住院序号I
) TT ON T.系统序号=TT.住院序号I
GROUP BY ROLLUP(科室)
ORDER BY 总收入 DESC
) UNPIVOT ( 值 FOR 汇总 IN (总收入, 药品收入,非药收入));
排查中业务查询语句 可以执行出结果
SELECT 科室 AS 名称,
round(SUM(金额) / 10000, 2) AS 总收入,
round(SUM(DECODE(TT.名称, '药品费', 金额, 0)) / 10000, 2) AS 药品收入,
round(SUM(DECODE(TT.名称, '药品费', 0, 金额)) / 10000, 2) AS 非药收入
FROM (SELECT DA.系统序号, NVL(KS.名称, '未知') AS 科室
FROM INQ_D住院档案 DA
LEFT JOIN DOC_T科室档案 KS
ON KS.系统序号 = DA.当前科室I
UNION ALL
SELECT DA.系统序号, NVL(KS.名称, '未知') AS 科室
FROM INQ_D住院档案Z DA
LEFT JOIN DOC_T科室档案 KS
ON KS.系统序号 = DA.当前科室I) T
INNER JOIN (SELECT JSLB.住院序号I,
FB.名称,
SUM(FYMX.执行金额 * SIGN(JSLB.结算总额)) AS 金额
FROM inq_d住院费用明细 FYMX
INNER JOIN inq_d住院结算明细 JSMX
ON FYMX.系统序号 = JSMX.明细序号I
INNER JOIN inq_d住院结算列表 JSLB
ON JSMX.结算序号I = decode(JSLB.冲销状态N,
2,
JSLB.冲销序号I,
JSLB.系统序号)
INNER JOIN doc_t收费项目价格表 JG
ON JG.系统序号 = FYMX.收费项目序号I
INNER JOIN doc_t收费项目 XM
ON JG.项目序号I = XM.系统序号
INNER JOIN DOC_VT费用类别_院长查询 FB
ON XM.费用类别I = FB.系统序号
GROUP BY FB.名称, JSLB.住院序号I
UNION ALL
SELECT JSLB.住院序号I,
FB.名称,
SUM(FYMX.执行金额 * SIGN(JSLB.结算总额)) AS 金额
FROM inq_d住院费用明细Z FYMX
INNER JOIN inq_d住院结算明细 JSMX
ON FYMX.系统序号 = JSMX.明细序号I
INNER JOIN inq_d住院结算列表 JSLB
ON JSMX.结算序号I = decode(JSLB.冲销状态N,
2,
JSLB.冲销序号I,
JSLB.系统序号)
INNER JOIN doc_t收费项目价格表 JG
ON JG.系统序号 = FYMX.收费项目序号I
INNER JOIN doc_t收费项目 XM
ON JG.项目序号I = XM.系统序号
INNER JOIN DOC_VT费用类别_院长查询 FB
ON XM.费用类别I = FB.系统序号
GROUP BY FB.名称, JSLB.住院序号I) TT
ON T.系统序号 = TT.住院序号I
GROUP BY ROLLUP(科室)
ORDER BY 总收入 DESC
那面问题定位到 外部嵌套语句
SELECT 汇总, 名称, 值
FROM () UNPIVOT(值 FOR 汇总 IN(总收入, 药品收入, 非药收入) )
其中
Dump continued from file: D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\trace\orcl_ora_6028.trc
ORA-07445: 出现异常错误: 核心转储 [kokmrwo_agg()+133] [ACCESS_VIOLATION] [ADDR:0x0] [PC:0x14348E42F] [UNABLE_TO_READ] []
========= Dump for incident 80669 (ORA 7445 [kokmrwo_agg()+133]) ========
----- Beginning of Customized Incident Dump(s) -----
Exception [type: ACCESS_VIOLATION, UNABLE_TO_READ] [ADDR:0x0] [PC:0x14348E42F, kokmrwo_agg()+133]
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
Process Id: 0x00000734 Thread Id : 0x0000178c Time : Mon Jun 24 11:13:54
Excp. Code: 0xc0000005 Excp. Type: ACCESS_VIO Flags: 0x00000000
------------------- Registers ----------------------------
ip=000000014348E42F sp=0000000041CE59E0 rp=000000004DA96C40
r1=0000000000000010 r2=0000000000000000 r3=0000000000000001
r4=0000000000000004 r5=0000000041CE59E0 r6=000000004DA96C40 r7=000000004DA96C68
r8=0000000000000003 r9=00000000398C1628 r10=0000000000000002 r11=000000000A22C020
r12=00000000398C16B0 r13=0000000000000001 r14=0000000000000001 r15=0000000000000001
------------------- End of Registers ---------------------
*** 2019-06-24 11:13:54.519
dbkedDefDump(): Starting a non-incident diagnostic dump (flags=0x3, level=3, mask=0x0)
----- Current SQL Statement for this session (sql_id=6nar8cr1xznmp) -----
table_reference UNPIVOT [ {INCLUDE | EXCLUDE} NULLS ]
( { column | ( column [, column]... ) }
pivot_for_clause
unpivot_in_clause
)
UNPIVOT 使用语法 需要使用列名 错误写法中使用别名
SQL修改为
SELECT 汇总, 名称, 值
FROM (select 名称, 总收入, 药品收入, 非药收入 from (SELECT 科室 AS 名称,
round(SUM(金额) / 10000, 2) AS 总收入,
round(SUM(DECODE(TT.名称, '药品费', 金额, 0)) / 10000, 2) AS 药品收入,
round(SUM(DECODE(TT.名称, '药品费', 0, 金额)) / 10000, 2) AS 非药收入
FROM (SELECT DA.系统序号, NVL(KS.名称, '未知') AS 科室
FROM INQ_D住院档案 DA
LEFT JOIN DOC_T科室档案 KS
ON KS.系统序号 = DA.当前科室I
UNION ALL
SELECT DA.系统序号, NVL(KS.名称, '未知') AS 科室
FROM INQ_D住院档案Z DA
LEFT JOIN DOC_T科室档案 KS
ON KS.系统序号 = DA.当前科室I) T
INNER JOIN (SELECT JSLB.住院序号I,
FB.名称,
SUM(FYMX.执行金额 * SIGN(JSLB.结算总额)) AS 金额
FROM inq_d住院费用明细 FYMX
INNER JOIN inq_d住院结算明细 JSMX
ON FYMX.系统序号 = JSMX.明细序号I
INNER JOIN inq_d住院结算列表 JSLB
ON JSMX.结算序号I = decode(JSLB.冲销状态N,
2,
JSLB.冲销序号I,
JSLB.系统序号)
INNER JOIN doc_t收费项目价格表 JG
ON JG.系统序号 = FYMX.收费项目序号I
INNER JOIN doc_t收费项目 XM
ON JG.项目序号I = XM.系统序号
INNER JOIN DOC_VT费用类别_院长查询 FB
ON XM.费用类别I = FB.系统序号
GROUP BY FB.名称, JSLB.住院序号I
UNION ALL
SELECT JSLB.住院序号I,
FB.名称,
SUM(FYMX.执行金额 * SIGN(JSLB.结算总额)) AS 金额
FROM inq_d住院费用明细Z FYMX
INNER JOIN inq_d住院结算明细 JSMX
ON FYMX.系统序号 = JSMX.明细序号I
INNER JOIN inq_d住院结算列表 JSLB
ON JSMX.结算序号I = decode(JSLB.冲销状态N,
2,
JSLB.冲销序号I,
JSLB.系统序号)
INNER JOIN doc_t收费项目价格表 JG
ON JG.系统序号 = FYMX.收费项目序号I
INNER JOIN doc_t收费项目 XM
ON JG.项目序号I = XM.系统序号
INNER JOIN DOC_VT费用类别_院长查询 FB
ON XM.费用类别I = FB.系统序号
GROUP BY FB.名称, JSLB.住院序号I) TT
ON T.系统序号 = TT.住院序号I
GROUP BY ROLLUP(科室)
ORDER BY 总收入 DESC)) UNPIVOT(值 FOR 汇总 IN(总收入, 药品收入, 非药收入))
后执行成功