大家帮忙看看这条sql在10g可以运行，在11g报错，缺少关键字

显示全部楼层 · 2011-5-12 00:00:00

本帖最后由 zhangyu4849467 于 2012-5-14 20:39 编辑
create table inp_icu_inout
(
id
number(38)
not null,
name
varchar2(200 byte),
medicalservicevarchar2(200 byte),
deptid
varchar2(20 byte),
bedid varchar2(20 byte),
admissionid varchar2(20 byte),
inoutdate date,
inorout number(38),
inoutname varchar2(200 byte),
dose
varchar2(100 byte),
route varchar2(100 byte),
quantityfloat(126),
validflag number(38)
default 1,
type
varchar2(20 byte),
groupid varchar2(20 byte),
series
number(38),
inoutdesc varchar2(100 byte),
recordervarchar2(10 byte)
)
create table temperature
(
patient_id
varchar2(50 byte) not null,
mrn
varchar2(50 byte),
bedno
varchar2(4 byte),
deptid
varchar2(10 byte) not null,
plantime
date
not null,
excutetime
date,
vitalsignstypenumber
not null,
vitalsignsval number,
unit
varchar2(10 byte),
remark
varchar2(200 byte),
vitalsignshightvalnumber,
execute_nurse varchar2(10 byte),
measuretype number,
validflag number,
series
number
not null
)
create table inp_icu_disposal
(
id
number(38)
not null,
name
varchar2(50 byte),
medicalservice varchar2(20 byte),
deptid varchar2(20 byte),
bedid
varchar2(20 byte),
admissionidvarchar2(20 byte),
disposaltime date,
disposalcontentvarchar2(4000 byte),
validflagnumber(38)
default 1,
recordercode varchar2(10 byte),
series number(38),
icuflag
number(38),
head_nurse_namevarchar2(16 byte),
head_nurse_codevarchar2(16 byte),
flag
number
)
表结构如上，sql语句为
select

decode(grouping(row_num),0,max(order_time),null) as order_time
from(
select
rownum as row_num,order_time,decode(tem,lag(tem,1,null) over(partition by order_time order by order_time),null,tem) as tem,
decode(pulse,lag(pulse,1,null) over(partition by order_time order by order_time),null,pulse) as pulse,
decode(breathe,lag(breathe,1,null) over(partition by order_time order by order_time),null,breathe) as breathe,
decode(blood_pressure,lag(blood_pressure,1,null) over(partition by order_time order by order_time),null,blood_pressure) as blood_pressure,
decode(pulse_oxygen,lag(pulse_oxygen,1,null) over(partition by order_time order by order_time),null,pulse_oxygen) as pulse_oxygen,
in_name,in_quantity,in_type,in_no,route,out_name,out_quantity,out_type,out_no,groupid,
decode(disposalcontent,lag(disposalcontent,1,null) over(partition by order_time order by order_time),null,disposalcontent) as disposalcontent,
case
when (to_char(order_time,'hh24') >= 02 and to_char(order_time,'hh24') = 08 and to_char(order_time,'hh24') = to_date('2011-05-12 00:00:00','yyyy-mm-dd hh24:mi:ss')

and inoutdate = to_date('2011-5-12 00:00:00','yyyy-mm-dd hh24:mi:ss')

and plantime = to_date('2011-5-12 00:00:00','yyyy-mm-dd hh24:mi:ss')

and disposaltimeselect * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
PL/SQL Release 11.1.0.6.0 - Production
CORE11.1.0.6.0Production
TNS for 32-bit Windows: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production
这个版本不能执行

千问 · 2011-5-12 00:00:00

错误提示信息？

千问 · 2011-5-12 00:00:00

本帖最后由 zhangyu4849467 于 2012-5-14 14:57 编辑
〇〇发表于 2012-5-14 13:16
错误提示信息？
ORA-00905: missing keyword
主要是使用group by grouping sets ,就报缺失关键字，
这个是全部的sql , 帮我看下啊，谢谢
select
order_time,
case
when g1 = 0 then to_char(order_time,'yyyy-mm-dd hh24:mi:ss')
when g1 = 1 and g2 = 1 and g3 = 1 and g4 = 0 and g5 = 0 and g6 = 1 and g7 = 1 then

case

when shift = 1 then '08~17班次'

when shift = 2 then '17~02班次'

when shift = 3 then '02~08班次'

else null

end
when g1 = 1 and g2 = 3 and g3 = 3 and g4 = 1 and g5 = 0 and g6 = 1 and g7 = 1 then '24小时总结'
else null
end as show_time,tem,pulse,breathe,blood_pressure,pulse_oxygen,in_name,in_quantity,route,out_name,out_quantity,groupid,disposalcontent,in_type,out_type from (
select
case
when grouping(row_num) = 0

then max(order_time)
when grouping(row_num) = 1 and grouping_id(shift_date,shift,in_type) = 0 and grouping_id(shift_date,shift,out_type) = 1 and grouping_id(shift_date,shift) = 0 and grouping(shift_date) = 0 and grouping_id(shift_date,in_type) = 0 and grouping_id(shift_date,out_type) = 1 and in_type is not null then

case

when shift = 1

then shift_date + 17/24 + 1/(24*60*60)

when shift = 2

then (shift_date + 1) + 1/12 + 1/(24*60*60)

else (shift_date + 1) + 1/3 + 1/(24*60*60)

end
when grouping(row_num) = 1 and grouping_id(shift_date,shift,in_type) = 1 and grouping_id(shift_date,shift,out_type) = 0 and grouping_id(shift_date,shift) = 0 and grouping(shift_date) = 0 and grouping_id(shift_date,in_type) = 1 and grouping_id(shift_date,out_type) = 0 and out_type is not null then

case

when shift = 1

then shift_date + 17/24 + 1/(24*60*60)

when shift = 2

then (shift_date + 1) + 1/12 + 1/(24*60*60)

else (shift_date + 1) + 1/3 + 1/(24*60*60)

end
when grouping(row_num) = 1 and grouping_id(shift_date,shift,in_type) = 1 and grouping_id(shift_date,shift,out_type) = 1 and grouping_id(shift_date,shift) = 0 and grouping(shift_date) = 0 and grouping_id(shift_date,in_type) = 1 and grouping_id(shift_date,out_type) = 1 then

case

when shift = 1

then shift_date + 17/24 + 1/(24*60*60)

when shift = 2

then (shift_date + 1) + 1/12 + 1/(24*60*60)

else (shift_date + 1) + 1/3 + 1/(24*60*60)

end
when grouping(row_num) = 1 and grouping_id(shift_date,shift,in_type) = 3 and grouping_id(shift_date,shift,out_type) = 3 and grouping_id(shift_date,shift) = 1 and grouping(shift_date) = 0 and grouping_id(shift_date,in_type) = 1 and grouping_id(shift_date,out_type) = 1

then (shift_date + 1) + 1/3 + 1/(24*60*60)
when grouping(row_num) = 1 and grouping_id(shift_date,shift,in_type) = 2 and grouping_id(shift_date,shift,out_type) = 3 and grouping_id(shift_date,shift) = 1 and grouping(shift_date) = 0 and grouping_id(shift_date,in_type) = 0 and grouping_id(shift_date,out_type) = 1 and in_type is not null

then (shift_date + 1) + 1/3 + 1/(24*60*60)
when grouping(row_num) = 1 and grouping_id(shift_date,shift,in_type) = 3 and grouping_id(shift_date,shift,out_type) = 2 and grouping_id(shift_date,shift) = 1 and grouping(shift_date) = 0 and grouping_id(shift_date,in_type) = 1 and grouping_id(shift_date,out_type) = 0 and out_type is not null

then (shift_date + 1) + 1/3 + 1/(24*60*60)
else null
end as order_time,decode(grouping(row_num),0,max(tem),null) as tem,decode(grouping(row_num),0,max(pulse),null) as pulse,
decode(grouping(row_num),0,max(breathe),null) as breathe,decode(grouping(row_num),0,max(blood_pressure),null) as blood_pressure,decode(grouping(row_num),0,max(pulse_oxygen),null) as pulse_oxygen,
case

when grouping(row_num) = 0

then max(in_name)
when grouping(row_num) = 1 and grouping_id(shift_date,shift,in_type) = 0 and grouping_id(shift_date,shift,out_type) = 1 and grouping_id(shift_date,shift) = 0 and grouping(shift_date) = 0 and grouping_id(shift_date,in_type) = 0 and grouping_id(shift_date,out_type) = 1 and in_type is not null

then in_type
when grouping(row_num) = 1 and grouping_id(shift_date,shift,in_type) = 1 and grouping_id(shift_date,shift,out_type) = 1 and grouping_id(shift_date,shift) = 0 and grouping(shift_date) = 0 and grouping_id(shift_date,in_type) = 1 and grouping_id(shift_date,out_type) = 1

then '入量小结'
when grouping(row_num) = 1 and grouping_id(shift_date,shift,in_type) = 3 and grouping_id(shift_date,shift,out_type) = 3 and grouping_id(shift_date,shift) = 1 and grouping(shift_date) = 0 and grouping_id(shift_date,in_type) = 1 and grouping_id(shift_date,out_type) = 1

then '总入量'
when grouping(row_num) = 1 and grouping_id(shift_date,shift,in_type) = 2 and grouping_id(shift_date,shift,out_type) = 3 and grouping_id(shift_date,shift) = 1 and grouping(shift_date) = 0 and grouping_id(shift_date,in_type) = 0 and grouping_id(shift_date,out_type) = 1 and in_type is not null

then in_type
else null
end as in_name,sum(in_quantity) as in_quantity,decode(grouping(row_num),0,max(route),null) as route,
case
when grouping(row_num) = 0

then max(out_name)
when grouping(row_num) = 1 and grouping_id(shift_date,shift,in_type) = 1 and grouping_id(shift_date,shift,out_type) = 0 and grouping_id(shift_date,shift) = 0 and grouping(shift_date) = 0 and grouping_id(shift_date,in_type) = 1 and grouping_id(shift_date,out_type) = 0 and out_type is not null

then out_type

--
when grouping(row_num) = 1 and grouping_id(shift_date,shift,in_type) = 1 and grouping_id(shift_date,shift,out_type) = 1 and grouping_id(shift_date,shift) = 0 and grouping(shift_date) = 0 and grouping_id(shift_date,in_type) = 1 and grouping_id(shift_date,out_type) = 1

then '出量小结'
when grouping(row_num) = 1 and grouping_id(shift_date,shift,in_type) = 3 and grouping_id(shift_date,shift,out_type) = 3 and grouping_id(shift_date,shift) = 1 and grouping(shift_date) = 0 and grouping_id(shift_date,in_type) = 1 and grouping_id(shift_date,out_type) = 1

then '总出量'
when grouping(row_num) = 1 and grouping_id(shift_date,shift,in_type) = 3 and grouping_id(shift_date,shift,out_type) = 2 and grouping_id(shift_date,shift) = 1 and grouping(shift_date) = 0 and grouping_id(shift_date,in_type) = 1 and grouping_id(shift_date,out_type) = 0 and out_type is not null

then out_type
else null
end as out_name,sum(out_quantity) as out_quantity,decode(grouping(row_num),0,max(groupid),null) as groupid,decode(grouping(row_num),0,max(disposalcontent),null) as disposalcontent,
grouping(row_num) g1,grouping_id(shift_date,shift,in_type) g2,grouping_id(shift_date,shift,out_type) g3,grouping_id(shift_date,shift) g4,grouping(shift_date) g5,
grouping_id(shift_date,in_type) g6,grouping_id(shift_date,out_type) g7,shift,shift_date,max(in_type) as in_type,max(out_type) as out_type from(
select
rownum as row_num,order_time,decode(tem,lag(tem,1,null) over(partition by order_time order by order_time),null,tem) as tem,
decode(pulse,lag(pulse,1,null) over(partition by order_time order by order_time),null,pulse) as pulse,
decode(breathe,lag(breathe,1,null) over(partition by order_time order by order_time),null,breathe) as breathe,
decode(blood_pressure,lag(blood_pressure,1,null) over(partition by order_time order by order_time),null,blood_pressure) as blood_pressure,
decode(pulse_oxygen,lag(pulse_oxygen,1,null) over(partition by order_time order by order_time),null,pulse_oxygen) as pulse_oxygen,
in_name,in_quantity,in_type,in_no,route,out_name,out_quantity,out_type,out_no,groupid,
decode(disposalcontent,lag(disposalcontent,1,null) over(partition by order_time order by order_time),null,disposalcontent) as disposalcontent,
case
when (to_char(order_time,'hh24') >= 02 and to_char(order_time,'hh24') = 08 and to_char(order_time,'hh24') = to_date('2012-05-12 00:00:00','yyyy-mm-dd hh24:mi:ss')

and inoutdate = to_date('2012-5-12 00:00:00','yyyy-mm-dd hh24:mi:ss')

and plantime = to_date('2012-5-12 00:00:00','yyyy-mm-dd hh24:mi:ss')

and disposaltime <= to_date('2012-5-12 23:59:59','yyyy-mm-dd hh24:mi:ss')) d
on t.patient_id = d.admissionid and t.series = d.series and t.plantime = d.disposaltime) tp
on in_out.admissionid = tp.p_id and in_out.series = tp.series and in_out.inoutdate = tp.order_time))

group by grouping sets((row_num),(shift_date,shift,in_type),(shift_date,shift,out_type),(shift_date,shift),(shift_date),(shift_date,in_type),(shift_date,out_type)))
where order_time is not null and (in_quantity is not null or out_quantity is not null or tem is not null or pulse is not null or breathe is not null or blood_pressure is not null or pulse_oxygen is not null or disposalcontent is not null)
order by 1,tem,pulse,breathe,blood_pressure,pulse_oxygen,disposalcontent,g4,g5,g3 desc,g6 desc

千问 · 2011-5-12 00:00:00

第 59 行出现错误:
ORA-00905: 缺失关键字

千问 · 2011-5-12 00:00:00

如楼上所说的59行，在SQL PLUS 中，用ed 命令能准确的定位到报错出。
检查检查！

千问 · 2011-5-12 00:00:00

你简化的sql在哪里？

千问 · 2011-5-12 00:00:00

可能是BUG
group by ROLLUP((shift_date,shift)) ,ROLLUP(row_num)
HAVING grouping_id(shift_date,shift,row_num) IN (1,6) --这行不要不报错，一加上就报错

千问 · 2011-5-12 00:00:00

group by ROLLUP((shift_date,shift)) ,ROLLUP(row_num)
改为cube,不加having都报错，靠

千问 · 2011-5-12 00:00:00

dingjun123 发表于 2012-5-14 19:55
group by ROLLUP((shift_date,shift)) ,ROLLUP(row_num)
改为cube,不加having都报错，靠
11g 上 full outer join 和 group by grouping sets 一起使用就报错，这个问题可能是oracle11g的bug,10g上就没这个问题。
下午我把最下面的2个 full outer join 全部换成 union all 就好用