在oracle 11.2.0.1.0 在sql语句中使用with table as 写法时触发了ora-03113错误
select pay_bill_no,
hospital_id,
hospital_name,
aaa027,
insur_name as center_name,
pers_name,
aka035,
indi_count as rc,
count(pers_name) as ds,
sum(bac004) as bac004,
0 as bili,
sum(total_pay) as total_money,
sum(fund_pay) as fund_money,
0 as fund_fee,
0 as qa_fee,
0 as offi_fee,
sum(total_pay_tekun) as total_money_tekun,
sum(fund_pay_tekun) as fund_money_tekun,
(select nvl(max(akb070), 0) from kf21 where aaz272 = pay_bill_no) +
(select nvl(sum(AKB069), 0) from kcb5 where aaz272 = pay_bill_no) akb070,
(select nvl(max(bkb070), 0) from kf21 where aaz272 = pay_bill_no) bkb070
from (with abcd as (select decode(a.bac004, '2', 1, 0) as bac004,
n.akb079,
k.aaa027,
n.aaz272 as pay_bill_no,
m.aaz107 as hospital_id,
b.aaz217 as serial_no,
z.aab069 as corp_name,
z.aab001 as corp_code,
k.aaa129 as insur_name,
a.aac003 as name,
a.aac002 as idcard,
d.aka121 as disease,
decode(b.aka042, 'E', '是', '否') as twice_inhosp_flag,
'否' as backbur_flag,
t.aaa103 as pers_name,
sum(c.aae019) as total_pay,
decode(a.bac004, '2', sum(c.aae019), 0) as total_pay_tekun,
sum(decode(c.aka002,
311901,
c.aae019,
311902,
c.aae019,
311903,
c.aae019,
510900,
c.aae019,
0)) as fund_pay,
decode(a.bac004,
'2',
sum(decode(c.aka002,
311901,
c.aae019,
311902,
c.aae019,
311903,
c.aae019,
510900,
c.aae019,
0)),
0) as fund_pay_tekun,
a.aac001 as indi_id,
e.aab069 as hospital_name,
b.aae030 as begin_date,
b.aae031 as end_date,
to_char(n.aae015, 'yyyy-mm-dd') as check_date,
decode(b.aka042,
'1',
'普通住院',
'2',
'急诊或抢救住院',
'3',
'转院住院',
'Q',
'更正报销业务',
'R',
'连续住院',
'Z',
'转科住院',
'B',
'欠费补缴',
'E',
'二次返院',
'') as aka042,
decode(y.aka035a,
'A',
'普通病种',
'C',
'特殊病种',
'C1',
'欠费补缴',
'D',
'综合病种',
'E',
'生育病种',
d.aka121) as aka035,
y.bkb007,
y.bkb008,
y.bkb009,
y.bkb010,
y.bkb030,
y.bkb035,
y.aka035a,
decode(ceil(trunc(to_date(h.aae031, 'yyyymmdd')) -
trunc(to_date(h.aae030, 'yyyymmdd'))),
0,
1,
ceil(trunc(to_date(h.aae031, 'yyyymmdd')) -
trunc(to_date(h.aae030, 'yyyymmdd')))) as days
from Ac01 a,
Kc21 b,
Kc27 c,
Ka06 d,
Ae10 e,
Kb01 f,
Kc19 h,
Kc25 m,
Kf20 n,
aa13 k,
Kcb4 z,
(select aaa102, aaa103
from aa23
where aae140 = '310'
and aaa027 = 440999) t,
(select decode(y.aka120, '', y.aka035, y.aka120) as aka035a,
sum(bkb007) as bkb007,
sum(bkb008) as bkb008,
sum(bkb009) as bkb009,
sum(y.bkb010) as bkb010,
sum(y.bkb030) as bkb030,
sum(y.bkb035) as bkb035
from kcb5 y
where aaz272 = 8180
group by decode(y.aka120,
'',
y.aka035,
y.aka120)) y
where b.aac001 = a.aac001
and b.aaz217 = c.aaz217
and d.aka120 = b.akc196
and f.aaz269 = e.Aaz001
and b.aaz217 = h.aaz217
and b.aaz217 = m.aaz217
and f.akb020 = n.akb020
and n.aaz272 = m.aaz272
and z.aaz217 = b.aaz217
and z.baa027 = k.aaa027
and b.aaa027 = f.aaa027
and n.aka028 = 2
and b.aae100 = 1
and c.aae100 = 1
and h.aae100 = 1
and m.aae100 = 1
and decode(z.aac066, '1', '311', z.aac066) =
t.aaa102(+)
and nvl(m.aae167, 0) >= 0
and (m.aka035 = y.aka035a or y.aka035a = d.aka120)
and m.aaz272 = 8180
and n.aaa027 in
('440999', '440901', '440902', '440903')
group by n.aaz272,
b.aaz217,
a.aac003,
a.aac002,
d.aka121,
b.aka042,
t.aaa103,
e.aab069,
b.aae030,
b.aae031,
m.aaz107,
n.aae015,
z.aab001,
z.aab069,
k.aaa129,
n.akb079,
a.aac001,
y.aka035a,
y.bkb007,
y.bkb008,
y.bkb009,
y.bkb010,
y.bkb030,
y.bkb035,
k.aaa027,
m.aka035,
h.aae031,
a.bac004,
h.aae030
union all
select decode(a.bac004, '2', 1, 0) as bac004,
f.akb079,
k.aaa027,
d.aaz272 as pay_bill_no,
d.aaz107 as hospital_id,
0 as serial_no,
t1.aab069 as corp_name,
e.aaz001 as corp_code,
k.aaa129 as insur_name,
a.aac003 as name,
a.aac002 as idcard,
b.aka121 as disease,
decode(d.aka042, 'E', '是', '否') as twice_inhosp_flag,
'是' as backbur_flag,
t.aaa103 as pers_name,
d.aae198 as total_pay,
decode(a.bac004, '2', d.aae198, 0) as total_pay_tekun,
0 as fund_pay,
0 as fund_pay_tekun,
a.aac001 as indi_id,
e.aab069 as hospital_name,
d.aae041 as begin_date,
d.aae042 as end_date,
to_char(f.aae015, 'yyyy-mm-dd') as check_date,
decode(d.aka042,
'1',
'普通住院',
'2',
'急诊或抢救住院',
'3',
'转院住院',
'Q',
'更正报销业务',
'R',
'连续住院',
'Z',
'转科住院',
'B',
'欠费补缴',
'E',
'二次返院',
'') as aka042,
decode(y.aka035a,
'A',
'普通病种',
'C',
'特殊病种',
'C1',
'欠费补缴',
'D',
'综合病种',
'E',
'生育病种',
b.aka121) as aka035,
y.bkb007,
y.bkb008,
y.bkb009,
y.bkb010,
y.bkb030,
y.bkb035,
y.aka035a,
decode(ceil(trunc(to_date(d.aae042, 'yyyymmdd')) -
trunc(to_date(d.aae041, 'yyyymmdd'))),
0,
1,
ceil(trunc(to_date(d.aae042, 'yyyymmdd')) -
trunc(to_date(d.aae041, 'yyyymmdd')))) as days
from Ac01 a,
Ka06 b,
Ae10 e,
Kb01 c,
Kcb1 d,
Kf20 f,
aa13 k,
(select aaa102, aaa103
from aa23
where aae140 = '310'
and aaa027 = 440999) t,
(select decode(y.aka120, '', y.aka035, y.aka120) as aka035a,
sum(bkb007) as bkb007,
sum(bkb008) as bkb008,
sum(bkb009) as bkb009,
sum(y.bkb010) as bkb010,
sum(y.bkb030) as bkb030,
sum(y.bkb035) as bkb035
from kcb5 y
where aaz272 = 8180
group by decode(y.aka120,
'',
y.aka035,
y.aka120)) y,
(select ac20.aac001, ae10.aab069
from ae10, ac20
where ae10.aaz001 = ac20.aab001
and ac20.aae140 = '310') t1
where c.aaz269 = e.Aaz001
and f.aaz272 = d.aaz272
and d.aac001 = a.aac001
and b.aka120 = d.akc196
and d.aae100 = 1
and d.aaz107 = c.aaz107
and k.aaa027 = d.aaa027
and c.aaa027 = d.aaa027
and d.aac001 = t1.aac001
and f.aka028 = 2
and nvl(d.aae167, 0) >= 0
and d.aac066 = t.aaa102(+)
and (d.aka035 = y.aka035a or b.aka120 = y.aka035a)
and d.aaz272 = 8180), abc as (select count(distinct
indi_id) indi_count,
insur_name,
pers_name,
aka035,
aaa027
from abcd
group by insur_name,
pers_name,
aka035,
aaa027)
select abc.indi_count,
abc.insur_name,
abc.pers_name,
abc.aka035,
abc.aaa027,
abcd.hospital_id,
abcd.hospital_name,
abcd.bac004,
abcd.pay_bill_no,
abcd.total_pay,
abcd.fund_pay,
abcd.total_pay_tekun,
abcd.fund_pay_tekun
from abc, abcd
where abc.insur_name = abcd.insur_name
and abc.pers_name = abcd.pers_name
and abc.aka035 = abcd.aka035
and abc.aaa027 = abcd.aaa027)
group by indi_count,
insur_name,
pers_name,
aka035,
aaa027,
hospital_id,
hospital_name,
pay_bill_no
order by aaa027, pers_name desc;
上面是报ora-03113故障的语句使用了with table as 写法
经过修改语句去掉with table as 写法后正常执行
select pay_bill_no,
hospital_id,
hospital_name,
aaa027,
insur_name as center_name,
pers_name,
aka035,
indi_count as rc,
count(pers_name) as ds,
sum(bac004) as bac004,
0 as bili,
sum(total_pay) as total_money,
sum(fund_pay) as fund_money,
0 as fund_fee,
0 as qa_fee,
0 as offi_fee,
sum(total_pay_tekun) as total_money_tekun,
sum(fund_pay_tekun) as fund_money_tekun,
(select nvl(max(akb070), 0) from kf21 where aaz272 = pay_bill_no) +
(select nvl(sum(AKB069), 0) from kcb5 where aaz272 = pay_bill_no) akb070,
(select nvl(max(bkb070), 0) from kf21 where aaz272 = pay_bill_no) bkb070
from (
select abc.indi_count,
abc.insur_name,
abc.pers_name,
abc.aka035,
abc.aaa027,
abcd.hospital_id,
abcd.hospital_name,
abcd.bac004,
abcd.pay_bill_no,
abcd.total_pay,
abcd.fund_pay,
abcd.total_pay_tekun,
abcd.fund_pay_tekun
from
(
select count(distinct
indi_id) indi_count,
insur_name,
pers_name,
aka035,
aaa027
from
(
select decode(a.bac004, '2', 1, 0) as bac004,
n.akb079,
k.aaa027,
n.aaz272 as pay_bill_no,
m.aaz107 as hospital_id,
b.aaz217 as serial_no,
z.aab069 as corp_name,
z.aab001 as corp_code,
k.aaa129 as insur_name,
a.aac003 as name,
a.aac002 as idcard,
d.aka121 as disease,
decode(b.aka042, 'E', '是', '否') as twice_inhosp_flag,
'否' as backbur_flag,
t.aaa103 as pers_name,
sum(c.aae019) as total_pay,
decode(a.bac004, '2', sum(c.aae019), 0) as total_pay_tekun,
sum(decode(c.aka002,
311901,
c.aae019,
311902,
c.aae019,
311903,
c.aae019,
510900,
c.aae019,
0)) as fund_pay,
decode(a.bac004,
'2',
sum(decode(c.aka002,
311901,
c.aae019,
311902,
c.aae019,
311903,
c.aae019,
510900,
c.aae019,
0)),
0) as fund_pay_tekun,
a.aac001 as indi_id,
e.aab069 as hospital_name,
b.aae030 as begin_date,
b.aae031 as end_date,
to_char(n.aae015, 'yyyy-mm-dd') as check_date,
decode(b.aka042,
'1',
'普通住院',
'2',
'急诊或抢救住院',
'3',
'转院住院',
'Q',
'更正报销业务',
'R',
'连续住院',
'Z',
'转科住院',
'B',
'欠费补缴',
'E',
'二次返院',
'') as aka042,
decode(y.aka035a,
'A',
'普通病种',
'C',
'特殊病种',
'C1',
'欠费补缴',
'D',
'综合病种',
'E',
'生育病种',
d.aka121) as aka035,
y.bkb007,
y.bkb008,
y.bkb009,
y.bkb010,
y.bkb030,
y.bkb035,
y.aka035a,
decode(ceil(trunc(to_date(h.aae031, 'yyyymmdd')) -
trunc(to_date(h.aae030, 'yyyymmdd'))),
0,
1,
ceil(trunc(to_date(h.aae031, 'yyyymmdd')) -
trunc(to_date(h.aae030, 'yyyymmdd')))) as days
from Ac01 a,
Kc21 b,
Kc27 c,
Ka06 d,
Ae10 e,
Kb01 f,
Kc19 h,
Kc25 m,
Kf20 n,
aa13 k,
Kcb4 z,
(select aaa102, aaa103
from aa23
where aae140 = '310'
and aaa027 = 440999) t,
(select decode(y.aka120, '', y.aka035, y.aka120) as aka035a,
sum(bkb007) as bkb007,
sum(bkb008) as bkb008,
sum(bkb009) as bkb009,
sum(y.bkb010) as bkb010,
sum(y.bkb030) as bkb030,
sum(y.bkb035) as bkb035
from kcb5 y
where aaz272 = 8180
group by decode(y.aka120,
'',
y.aka035,
y.aka120)) y
where b.aac001 = a.aac001
and b.aaz217 = c.aaz217
and d.aka120 = b.akc196
and f.aaz269 = e.Aaz001
and b.aaz217 = h.aaz217
and b.aaz217 = m.aaz217
and f.akb020 = n.akb020
and n.aaz272 = m.aaz272
and z.aaz217 = b.aaz217
and z.baa027 = k.aaa027
and b.aaa027 = f.aaa027
and n.aka028 = 2
and b.aae100 = 1
and c.aae100 = 1
and h.aae100 = 1
and m.aae100 = 1
and decode(z.aac066, '1', '311', z.aac066) =
t.aaa102(+)
and nvl(m.aae167, 0) >= 0
and (m.aka035 = y.aka035a or y.aka035a = d.aka120)
and m.aaz272 = 8180
and n.aaa027 in
('440999', '440901', '440902', '440903')
group by n.aaz272,
b.aaz217,
a.aac003,
a.aac002,
d.aka121,
b.aka042,
t.aaa103,
e.aab069,
b.aae030,
b.aae031,
m.aaz107,
n.aae015,
z.aab001,
z.aab069,
k.aaa129,
n.akb079,
a.aac001,
y.aka035a,
y.bkb007,
y.bkb008,
y.bkb009,
y.bkb010,
y.bkb030,
y.bkb035,
k.aaa027,
m.aka035,
h.aae031,
a.bac004,
h.aae030
union all
select decode(a.bac004, '2', 1, 0) as bac004,
f.akb079,
k.aaa027,
d.aaz272 as pay_bill_no,
d.aaz107 as hospital_id,
0 as serial_no,
t1.aab069 as corp_name,
e.aaz001 as corp_code,
k.aaa129 as insur_name,
a.aac003 as name,
a.aac002 as idcard,
b.aka121 as disease,
decode(d.aka042, 'E', '是', '否') as twice_inhosp_flag,
'是' as backbur_flag,
t.aaa103 as pers_name,
d.aae198 as total_pay,
decode(a.bac004, '2', d.aae198, 0) as total_pay_tekun,
0 as fund_pay,
0 as fund_pay_tekun,
a.aac001 as indi_id,
e.aab069 as hospital_name,
d.aae041 as begin_date,
d.aae042 as end_date,
to_char(f.aae015, 'yyyy-mm-dd') as check_date,
decode(d.aka042,
'1',
'普通住院',
'2',
'急诊或抢救住院',
'3',
'转院住院',
'Q',
'更正报销业务',
'R',
'连续住院',
'Z',
'转科住院',
'B',
'欠费补缴',
'E',
'二次返院',
'') as aka042,
decode(y.aka035a,
'A',
'普通病种',
'C',
'特殊病种',
'C1',
'欠费补缴',
'D',
'综合病种',
'E',
'生育病种',
b.aka121) as aka035,
y.bkb007,
y.bkb008,
y.bkb009,
y.bkb010,
y.bkb030,
y.bkb035,
y.aka035a,
decode(ceil(trunc(to_date(d.aae042, 'yyyymmdd')) -
trunc(to_date(d.aae041, 'yyyymmdd'))),
0,
1,
ceil(trunc(to_date(d.aae042, 'yyyymmdd')) -
trunc(to_date(d.aae041, 'yyyymmdd')))) as days
from Ac01 a,
Ka06 b,
Ae10 e,
Kb01 c,
Kcb1 d,
Kf20 f,
aa13 k,
(select aaa102, aaa103
from aa23
where aae140 = '310'
and aaa027 = 440999) t,
(select decode(y.aka120, '', y.aka035, y.aka120) as aka035a,
sum(bkb007) as bkb007,
sum(bkb008) as bkb008,
sum(bkb009) as bkb009,
sum(y.bkb010) as bkb010,
sum(y.bkb030) as bkb030,
sum(y.bkb035) as bkb035
from kcb5 y
where aaz272 = 8180
group by decode(y.aka120,
'',
y.aka035,
y.aka120)) y,
(select ac20.aac001, ae10.aab069
from ae10, ac20
where ae10.aaz001 = ac20.aab001
and ac20.aae140 = '310') t1
where c.aaz269 = e.Aaz001
and f.aaz272 = d.aaz272
and d.aac001 = a.aac001
and b.aka120 = d.akc196
and d.aae100 = 1
and d.aaz107 = c.aaz107
and k.aaa027 = d.aaa027
and c.aaa027 = d.aaa027
and d.aac001 = t1.aac001
and f.aka028 = 2
and nvl(d.aae167, 0) >= 0
and d.aac066 = t.aaa102(+)
and (d.aka035 = y.aka035a or b.aka120 = y.aka035a)
and d.aaz272 = 8180)
group by insur_name,
pers_name,
aka035,
aaa027)
abc,
(select decode(a.bac004, '2', 1, 0) as bac004,
n.akb079,
k.aaa027,
n.aaz272 as pay_bill_no,
m.aaz107 as hospital_id,
b.aaz217 as serial_no,
z.aab069 as corp_name,
z.aab001 as corp_code,
k.aaa129 as insur_name,
a.aac003 as name,
a.aac002 as idcard,
d.aka121 as disease,
decode(b.aka042, 'E', '是', '否') as twice_inhosp_flag,
'否' as backbur_flag,
t.aaa103 as pers_name,
sum(c.aae019) as total_pay,
decode(a.bac004, '2', sum(c.aae019), 0) as total_pay_tekun,
sum(decode(c.aka002,
311901,
c.aae019,
311902,
c.aae019,
311903,
c.aae019,
510900,
c.aae019,
0)) as fund_pay,
decode(a.bac004,
'2',
sum(decode(c.aka002,
311901,
c.aae019,
311902,
c.aae019,
311903,
c.aae019,
510900,
c.aae019,
0)),
0) as fund_pay_tekun,
a.aac001 as indi_id,
e.aab069 as hospital_name,
b.aae030 as begin_date,
b.aae031 as end_date,
to_char(n.aae015, 'yyyy-mm-dd') as check_date,
decode(b.aka042,
'1',
'普通住院',
'2',
'急诊或抢救住院',
'3',
'转院住院',
'Q',
'更正报销业务',
'R',
'连续住院',
'Z',
'转科住院',
'B',
'欠费补缴',
'E',
'二次返院',
'') as aka042,
decode(y.aka035a,
'A',
'普通病种',
'C',
'特殊病种',
'C1',
'欠费补缴',
'D',
'综合病种',
'E',
'生育病种',
d.aka121) as aka035,
y.bkb007,
y.bkb008,
y.bkb009,
y.bkb010,
y.bkb030,
y.bkb035,
y.aka035a,
decode(ceil(trunc(to_date(h.aae031, 'yyyymmdd')) -
trunc(to_date(h.aae030, 'yyyymmdd'))),
0,
1,
ceil(trunc(to_date(h.aae031, 'yyyymmdd')) -
trunc(to_date(h.aae030, 'yyyymmdd')))) as days
from Ac01 a,
Kc21 b,
Kc27 c,
Ka06 d,
Ae10 e,
Kb01 f,
Kc19 h,
Kc25 m,
Kf20 n,
aa13 k,
Kcb4 z,
(select aaa102, aaa103
from aa23
where aae140 = '310'
and aaa027 = 440999) t,
(select decode(y.aka120, '', y.aka035, y.aka120) as aka035a,
sum(bkb007) as bkb007,
sum(bkb008) as bkb008,
sum(bkb009) as bkb009,
sum(y.bkb010) as bkb010,
sum(y.bkb030) as bkb030,
sum(y.bkb035) as bkb035
from kcb5 y
where aaz272 = 8180
group by decode(y.aka120,
'',
y.aka035,
y.aka120)) y
where b.aac001 = a.aac001
and b.aaz217 = c.aaz217
and d.aka120 = b.akc196
and f.aaz269 = e.Aaz001
and b.aaz217 = h.aaz217
and b.aaz217 = m.aaz217
and f.akb020 = n.akb020
and n.aaz272 = m.aaz272
and z.aaz217 = b.aaz217
and z.baa027 = k.aaa027
and b.aaa027 = f.aaa027
and n.aka028 = 2
and b.aae100 = 1
and c.aae100 = 1
and h.aae100 = 1
and m.aae100 = 1
and decode(z.aac066, '1', '311', z.aac066) =
t.aaa102(+)
and nvl(m.aae167, 0) >= 0
and (m.aka035 = y.aka035a or y.aka035a = d.aka120)
and m.aaz272 = 8180
and n.aaa027 in
('440999', '440901', '440902', '440903')
group by n.aaz272,
b.aaz217,
a.aac003,
a.aac002,
d.aka121,
b.aka042,
t.aaa103,
e.aab069,
b.aae030,
b.aae031,
m.aaz107,
n.aae015,
z.aab001,
z.aab069,
k.aaa129,
n.akb079,
a.aac001,
y.aka035a,
y.bkb007,
y.bkb008,
y.bkb009,
y.bkb010,
y.bkb030,
y.bkb035,
k.aaa027,
m.aka035,
h.aae031,
a.bac004,
h.aae030
union all
select decode(a.bac004, '2', 1, 0) as bac004,
f.akb079,
k.aaa027,
d.aaz272 as pay_bill_no,
d.aaz107 as hospital_id,
0 as serial_no,
t1.aab069 as corp_name,
e.aaz001 as corp_code,
k.aaa129 as insur_name,
a.aac003 as name,
a.aac002 as idcard,
b.aka121 as disease,
decode(d.aka042, 'E', '是', '否') as twice_inhosp_flag,
'是' as backbur_flag,
t.aaa103 as pers_name,
d.aae198 as total_pay,
decode(a.bac004, '2', d.aae198, 0) as total_pay_tekun,
0 as fund_pay,
0 as fund_pay_tekun,
a.aac001 as indi_id,
e.aab069 as hospital_name,
d.aae041 as begin_date,
d.aae042 as end_date,
to_char(f.aae015, 'yyyy-mm-dd') as check_date,
decode(d.aka042,
'1',
'普通住院',
'2',
'急诊或抢救住院',
'3',
'转院住院',
'Q',
'更正报销业务',
'R',
'连续住院',
'Z',
'转科住院',
'B',
'欠费补缴',
'E',
'二次返院',
'') as aka042,
decode(y.aka035a,
'A',
'普通病种',
'C',
'特殊病种',
'C1',
'欠费补缴',
'D',
'综合病种',
'E',
'生育病种',
b.aka121) as aka035,
y.bkb007,
y.bkb008,
y.bkb009,
y.bkb010,
y.bkb030,
y.bkb035,
y.aka035a,
decode(ceil(trunc(to_date(d.aae042, 'yyyymmdd')) -
trunc(to_date(d.aae041, 'yyyymmdd'))),
0,
1,
ceil(trunc(to_date(d.aae042, 'yyyymmdd')) -
trunc(to_date(d.aae041, 'yyyymmdd')))) as days
from Ac01 a,
Ka06 b,
Ae10 e,
Kb01 c,
Kcb1 d,
Kf20 f,
aa13 k,
(select aaa102, aaa103
from aa23
where aae140 = '310'
and aaa027 = 440999) t,
(select decode(y.aka120, '', y.aka035, y.aka120) as aka035a,
sum(bkb007) as bkb007,
sum(bkb008) as bkb008,
sum(bkb009) as bkb009,
sum(y.bkb010) as bkb010,
sum(y.bkb030) as bkb030,
sum(y.bkb035) as bkb035
from kcb5 y
where aaz272 = 8180
group by decode(y.aka120,
'',
y.aka035,
y.aka120)) y,
(select ac20.aac001, ae10.aab069
from ae10, ac20
where ae10.aaz001 = ac20.aab001
and ac20.aae140 = '310') t1
where c.aaz269 = e.Aaz001
and f.aaz272 = d.aaz272
and d.aac001 = a.aac001
and b.aka120 = d.akc196
and d.aae100 = 1
and d.aaz107 = c.aaz107
and k.aaa027 = d.aaa027
and c.aaa027 = d.aaa027
and d.aac001 = t1.aac001
and f.aka028 = 2
and nvl(d.aae167, 0) >= 0
and d.aac066 = t.aaa102(+)
and (d.aka035 = y.aka035a or b.aka120 = y.aka035a)
and d.aaz272 = 8180)
abcd
where abc.insur_name = abcd.insur_name
and abc.pers_name = abcd.pers_name
and abc.aka035 = abcd.aka035
and abc.aaa027 = abcd.aaa027)
group by indi_count,
insur_name,
pers_name,
aka035,
aaa027,
hospital_id,
hospital_name,
pay_bill_no
order by aaa027, pers_name desc;