我正在修改其他人的代码以调整所需的输出,但是,当我这样做时,我收到一条错误消息:使用UNION,INTERSECT或EXCEPT运算符组合的所有查询在其目标列表中必须具有相同数量的表达式。
更改前的代码(可以正常运行):
DECLARE @Period CHAR(6)
SELECT @Period = CONVERT(CHAR(6), GETDATE(),112)
select sum(CORPG) as CORPG, sum(FUNDS) as FUNDS, sum(EUCOM) as EUCOM, sum(INSUR) as INSUR, sum(IPIT) as IPIT, sum(LITGE) as LITGE, sum(FINR) as FINR,
sum(CNSTR) as CNSTR, sum(PLENV) as PLENV, sum(PLENV) as INSOL, sum(EMPLO) as EMPLO, sum(HELSC) as HELSC, sum(BANKG) as BANKG, sum(CONST) as CONST FROM (
Select TARGETFEESBILLED as CORPG, 0 as FUNDS, 0 as EUCOM, 0 as INSUR, 0 as IPIT, 0 as LITGE, 0 as FINR,
0 as CNSTR, 0 as PLENV, 0 as INSOL, 0 as EMPLO, 0 as HELSC, 0 as BANKG, 0 as CONST
from _ALG_TARGETFEESBILLED_PG where GROUP_CODE = 'FUNDS' AND PERIOD=@PERIOD
UNION
Select 0,TARGETFEESBILLED,0,0,0,0,0,0,0,0,0,0,0,0 from _ALG_TARGETFEESBILLED_PG where GROUP_CODE = 'EUCOM' and PERIOD=@PERIOD UNION
Select 0,0,TARGETFEESBILLED,0,0,0,0,0,0,0,0,0,0,0 from _ALG_TARGETFEESBILLED_PG where GROUP_CODE = 'EUCOM' and PERIOD=@PERIOD UNION
Select 0,0,0,TARGETFEESBILLED,0,0,0,0,0,0,0,0,0,0 from _ALG_TARGETFEESBILLED_PG where GROUP_CODE = 'INSUR' and PERIOD=@PERIOD UNION
Select 0,0,0,0,TARGETFEESBILLED,0,0,0,0,0,0,0,0,0 from _ALG_TARGETFEESBILLED_PG where GROUP_CODE = 'IPIT' and PERIOD=@PERIOD UNION
Select 0,0,0,0,0,TARGETFEESBILLED,0,0,0,0,0,0,0,0 from _ALG_TARGETFEESBILLED_PG where GROUP_CODE = 'LITGE' and PERIOD=@PERIOD UNION
Select 0,0,0,0,0,0,TARGETFEESBILLED,0,0,0,0,0,0,0 from _ALG_TARGETFEESBILLED_PG where GROUP_CODE = 'FINR' and PERIOD=@PERIOD UNION
Select 0,0,0,0,0,0,0,TARGETFEESBILLED,0,0,0,0,0,0 from _ALG_TARGETFEESBILLED_PG where GROUP_CODE = 'CNSTR' and PERIOD=@PERIOD UNION
Select 0,0,0,0,0,0,0,0,TARGETFEESBILLED,0,0,0,0,0 from _ALG_TARGETFEESBILLED_PG where GROUP_CODE = 'PLENV' and PERIOD=@PERIOD UNION
Select 0,0,0,0,0,0,0,0,0,TARGETFEESBILLED,0,0,0,0 from _ALG_TARGETFEESBILLED_PG where GROUP_CODE = 'INSOL' and PERIOD=@PERIOD UNION
Select 0,0,0,0,0,0,0,0,0,0,TARGETFEESBILLED,0,0,0 from _ALG_TARGETFEESBILLED_PG where GROUP_CODE = 'EMPLO' and PERIOD=@PERIOD UNION
Select 0,0,0,0,0,0,0,0,0,0,0,TARGETFEESBILLED,0,0 from _ALG_TARGETFEESBILLED_PG where GROUP_CODE = 'HELSC' and PERIOD=@PERIOD UNION
Select 0,0,0,0,0,0,0,0,0,0,0,0,TARGETFEESBILLED,0 from _ALG_TARGETFEESBILLED_PG where GROUP_CODE = 'BANKG' and PERIOD=@PERIOD UNION
Select 0,0,0,0,0,0,0,0,0,0,0,0,0,TARGETFEESBILLED from _ALG_TARGETFEESBILLED_PG where GROUP_CODE = 'CONST' and PERIOD=@PERIOD ) Rollup
这是更改后的代码(将不起作用):
DECLARE @Period CHAR(6)
SELECT @Period = CONVERT(CHAR(6), GETDATE(),112)
select sum(CORPG) as CORPG, sum(FUNDS) as FUNDS, sum(EUCOM) as EUCOM, sum(INSUR) as INSUR, sum(IPIT) as IPIT, sum(LITGE) as LITGE, sum(FINR) as FINR,
sum(CNSTR) as CNSTR, sum(PLENV) as PLENV, sum(PLENV) as INSOL, sum(EMPLO) as EMPLO, sum(HELSC) as HELSC, sum(BANKG) as BANKG, sum(CONST) as CONST, sum(COEN) as COEN, sum(CLIM) as CLIM FROM (
Select TARGETFEESBILLED as CORPG, 0 as FUNDS, 0 as EUCOM, 0 as INSUR, 0 as IPIT, 0 as LITGE, 0 as FINR,
0 as CNSTR, 0 as PLENV, 0 as INSOL, 0 as EMPLO, 0 as HELSC, 0 as BANKG, 0 as CONST, 0 as COEN, 0 as CLIM
from _ALG_TARGETFEESBILLED_PG where GROUP_CODE = 'FUNDS' AND PERIOD=@PERIOD
UNION
Select 0,TARGETFEESBILLED,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0 from _ALG_TARGETFEESBILLED_PG where GROUP_CODE = 'EUCOM' and PERIOD=@PERIOD UNION
Select 0,0,TARGETFEESBILLED,0,0,0,0,0,0,0,0,0,0,0,0,0,0 from _ALG_TARGETFEESBILLED_PG where GROUP_CODE = 'EUCOM' and PERIOD=@PERIOD UNION
Select 0,0,0,TARGETFEESBILLED,0,0,0,0,0,0,0,0,0,0,0,0,0 from _ALG_TARGETFEESBILLED_PG where GROUP_CODE = 'INSUR' and PERIOD=@PERIOD UNION
Select 0,0,0,0,TARGETFEESBILLED,0,0,0,0,0,0,0,0,0,0,0,0 from _ALG_TARGETFEESBILLED_PG where GROUP_CODE = 'IPIT' and PERIOD=@PERIOD UNION
Select 0,0,0,0,0,TARGETFEESBILLED,0,0,0,0,0,0,0,0,0,0,0 from _ALG_TARGETFEESBILLED_PG where GROUP_CODE = 'LITGE' and PERIOD=@PERIOD UNION
Select 0,0,0,0,0,0,TARGETFEESBILLED,0,0,0,0,0,0,0,0,0,0 from _ALG_TARGETFEESBILLED_PG where GROUP_CODE = 'FINR' and PERIOD=@PERIOD UNION
Select 0,0,0,0,0,0,0,TARGETFEESBILLED,0,0,0,0,0,0,0,0,0 from _ALG_TARGETFEESBILLED_PG where GROUP_CODE = 'CNSTR' and PERIOD=@PERIOD UNION
Select 0,0,0,0,0,0,0,0,TARGETFEESBILLED,0,0,0,0,0,0,0,0 from _ALG_TARGETFEESBILLED_PG where GROUP_CODE = 'PLENV' and PERIOD=@PERIOD UNION
Select 0,0,0,0,0,0,0,0,0,TARGETFEESBILLED,0,0,0,0,0,0,0 from _ALG_TARGETFEESBILLED_PG where GROUP_CODE = 'INSOL' and PERIOD=@PERIOD UNION
Select 0,0,0,0,0,0,0,0,0,0,TARGETFEESBILLED,0,0,0,0,0,0 from _ALG_TARGETFEESBILLED_PG where GROUP_CODE = 'EMPLO' and PERIOD=@PERIOD UNION
Select 0,0,0,0,0,0,0,0,0,0,0,TARGETFEESBILLED,0,0,0,0,0 from _ALG_TARGETFEESBILLED_PG where GROUP_CODE = 'HELSC' and PERIOD=@PERIOD UNION
Select 0,0,0,0,0,0,0,0,0,0,0,0,TARGETFEESBILLED,0,0,0,0 from _ALG_TARGETFEESBILLED_PG where GROUP_CODE = 'BANKG' and PERIOD=@PERIOD UNION
Select 0,0,0,0,0,0,0,0,0,0,0,0,0,TARGETFEESBILLED,0,0,0 from _ALG_TARGETFEESBILLED_PG where GROUP_CODE = 'CONST' and PERIOD=@PERIOD UNION
Select 0,0,0,0,0,0,0,0,0,0,0,0,0,0,TARGETFEESBILLED,0,0 from _ALG_TARGETFEESBILLED_PG where GROUP_CODE = 'CONST' and PERIOD=@PERIOD UNION
Select 0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,TARGETFEESBILLED,0 from _ALG_TARGETFEESBILLED_PG where GROUP_CODE = 'CONST' and PERIOD=@PERIOD ) Rollup
一些指导表示赞赏。联合建议目标列表中没有足够的表达,但据我所知。
谢谢
在下面的查询中,列数为16。选择TARGETFEESBILLED作为CORPG,0作为EUCOM,0作为EUUR,0作为IPIT,0作为LITGE,0作为FINR,0作为CNSTR,0作为PLENV,0作为INSOL,0表示为EMPLO,0表示HELSC,0表示BANKG,0表示CONST,0表示COEN,0表示CLIM ...
但是其他带有联合的查询有17列。
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句