17,377
社区成员
发帖
与我相关
我的任务
分享相同的栏位，提示类型不同
select s.deriveno,
regexp_replace(listagg(cg.deptid, ',') within
group(order by cg.deptid),
'([^,]+)(,\1)+',
'\1') as deptid,
p.pname,
s.carno,
m.appemplname,
m.passenger_qty,
e.deptid applydeptid,
m.appdt,
m.ride_starttime,
g.groupname,
g.groupscope,
g.aiml1 || ',' || g.aiml2 || ',' || g.aiml3 aim,
regexp_replace(listagg(sc.cityfrom || sc.addfrom, '-->')
within group(order by sc.city_seq),
'([^,]+)(-->\1)+',
'\1') as addfrom,
regexp_replace(listagg(sc.cityfrom, ',') within
group(order by sc.cityfrom),
'([^,]+)(,\1)+',
'\1') as cityfrom
from app_send s,
app_nomapping n,
app_main m,
employee e,
bd_groupandaim g,
app_schedule sc,
app_chargedept cg,
app_passenger p
where s.deriveno = n.deriveno
and m.appno = n.appno
and m.appemplid = e.emplid
and m.aim_id = g.aim_id
and m.appno = sc.appno
and p.appno = m.appno
and m.appno = cg.appno
and n.optype in ('ORI', 'SEP')
group by m.ride_starttime,
s.deriveno,
p.pname,
s.carno,
m.passenger_qty,
m.appemplname,
e.deptid,
m.appdt,
g.groupname,
g.groupscope,
g.aiml1 || ',' || g.aiml2 || ',' || g.aiml3
union
select aa.deriveno,
pname,
carno,
appemplname,
passenger_qty,--就是这里
deptid,
applydeptid,
appdt,
ride_starttime,
groupname,
groupscope,
aim,
addfrom,
cityfrom
from (select s.deriveno,
regexp_replace(listagg(cg.deptid, ',') within
group(order by cg.deptid),
'([^,]+)(,\1)+',
'\1') as deptid,
p.pname,
s.carno,
m.appemplname,
m.passenger_qty,
e.deptid applydeptid,
m.appdt,
g.groupname,
g.groupscope,
regexp_replace(listagg(sc.cityfrom || sc.addfrom,
'-->') within
group(order by sc.city_seq),
'([^,]+)(-->\1)+',
'\1') as addfrom,
g.aiml1 || ',' || g.aiml2 || ',' || g.aiml3 aim,
regexp_replace(listagg(sc.cityfrom, ',') within
group(order by sc.cityfrom),
'([^,]+)(,\1)+',
'\1') as cityfrom
from app_send s,
app_nomapping n,
app_main m,
employee e,
bd_groupandaim g,
app_schedule sc,
app_chargedept cg,
app_passenger p
where s.deriveno = n.deriveno
and m.appno = n.appno
and m.appemplid = e.emplid
and m.aim_id = g.aim_id
and m.appno = sc.appno
and s.deriveno = cg.appno
and n.optype = 'SPE'
and p.appno = m.appno
group by s.deriveno,
p.pname,
s.carno,
m.appemplname,
m.passenger_qty,
e.deptid,
m.appdt,
g.groupname,
g.groupscope,
g.aiml1 || ',' || g.aiml2 || ',' || g.aiml3) aa,
(select s.deriveno, m.ride_starttime
from app_send s, app_main m
where s.deriveno = m.appno) bb
where aa.deriveno = bb.deriveno
union
select m.appno,
regexp_replace(listagg(cg.deptid, ',') within
group(order by cg.deptid),
'([^,]+)(,\1)+',
'\1') as deptid,
p.pname,
s.carno,
m.appemplname,
m.passenger_qty,
e.deptid applydeptid,
m.appdt,
m.ride_starttime,
g.groupname,
g.groupscope,
g.aiml1 || ',' || g.aiml2 || ',' || g.aiml3 aim,
regexp_replace(listagg(sc.cityfrom || sc.addfrom, '-->')
within group(order by sc.city_seq),
'([^,]+)(-->\1)+',
'\1') as addfrom,
regexp_replace(listagg(sc.cityfrom, ',') within
group(order by sc.cityfrom),
'([^,]+)(,\1)+',
'\1') as cityfrom
from app_send s,
app_nomapping n,
app_main m,
employee e,
bd_groupandaim g,
app_schedule sc,
app_chargedept cg,
app_passenger p
where s.deriveno = n.deriveno
and m.appno = n.appno
and m.appemplid = e.emplid
and m.aim_id = g.aim_id
and m.appno = sc.appno
and p.appno = m.appno
and m.appno = cg.appno
and n.optype = 'CMB'
group by m.ride_starttime,
m.appno,
p.pname,
s.carno,
m.appemplname,
m.passenger_qty,
e.deptid,
m.appdt,
g.groupname,
g.groupscope,
g.aiml1 || ',' || g.aiml2 || ',' || g.aiml3
ora - 01790:表达式必须具有相同的数据类型对应的表达式
都是人数这个栏位，三个分开写都没问题，用union并在一起就报错
...全文
请发表友善的回复…
发表回复
