以下sql,
select qymc,getggbm('企业类型',qylx) qylx,getggbm('行业代码',hydm) hydm,getggbm('国籍',t1.tzssgb) gb,getdwmc(t1.djjg) djjgmc,clrq from aqydjsx t1
where tslx='1' and (qylx like '5%' or qylx like '6%' or qylx like '7%')
and datasource<>'420100' and djjg not like '4201%'
--order by t1.djjg
union all
select qymc,getggbm('企业类型',f_excode_whsbgb('企业类型',t1.QYLX,'省标')) qylx,getggbm('行业代码',hydm) hydm,getggbm('国籍',t1.tzssgb) gb,getdwmc(t1.djjg) djjgmc,clrq from aqydjsx t1
where f_excode_whsbgb('经营状态',t1.TSLX,'省标') ='1' and substr(f_excode_whsbgb('企业类型',t1.QYLX,'省标'),1,1) in ('5','6','7')
and datasource='420100' and djjg like '4201%'
order by t1.djjg
总是无法正确执行,提示:
ORA-00904: "T1"."DJJG": invalid identifier
查明原因是union all的优先级高于order by,导致union后无法按t1.djjg排序。
可将sql改写为:
select qymc,
getggbm('企业类型', qylx) qylx,
getggbm('行业代码', hydm) hydm,
getggbm('国籍', t1.tzssgb) gb,
djjg,
getdwmc(t1.djjg) djjgmc,
clrq
from aqydjsx t1
where tslx = '1'
and (qylx like '5%' or qylx like '6%' or qylx like '7%')
and datasource <> '420100'
and djjg not like '4201%'
union all
select qymc,
getggbm('企业类型', f_excode_whsbgb('企业类型', t2.QYLX, '省标')) qylx,
getggbm('行业代码', hydm) hydm,
getggbm('国籍', t2.tzssgb) gb,
djjg,
getdwmc(t2.djjg) djjgmc,
clrq
from aqydjsx t2
where f_excode_whsbgb('经营状态', t2.TSLX, '省标') = '1'
and substr(f_excode_whsbgb('企业类型', t2.QYLX, '省标'), 1, 1) in
('5', '6', '7')
and datasource = '420100'
and djjg like '4201%'
order by 5
其中,order by 5指union后select 列的第五列(即djjg)。
转载于:https://blog.51cto.com/99570386/969876