Stored Procedure Column Error - No Column Specified

[BSSME]

I was recently "elected" to add a field to a stored procedure but when I have everything in place I get the following:

"Error 8155: No column was specified for column 14 of 'a'."

I have colored the changes I made. Any ideas on what I am doing wrong or missing?

Thanks!

***Begin Code***

CREATE PROCEDURE orptshcEmSummaryReportNew

( --@BegEMCo bCompany=0,@EndEMCo bCompany= 255,
@Co bCompany=1, @IncludeCo varchar(200)='',
@BeginCat varchar(10) ='', @EndCat varchar(10) = 'zzzzzzzzz',
@BeginExcludeCat varchar(10), @EndExcludeCat varchar(10),
@BeginEquip bEquip = '', @EndEquip bEquip = 'zzzzzzzzz',
@BeginDept bDept = '', @EndDept bDept = 'zzzzzzzzzz',
@BeginMonth bMonth= '01/01/1951', @ThruMonth bMonth='01/01/2050',
@Status char(1),
@OwnStatus varchar(20)='', @BegShop varchar(20)='',@EndShop Varchar(20)='zzzzzzzzzzzzzzzzzzzz'
,
@BeginEquipSort1 varchar(10)='', @EndEquipSort1 varchar(10)='zzzzzzzzzz',
@BeginEquipSort2 varchar(10)='', @EndEquipSort2 varchar(10)='zzzzzzzzzz',
@BeginEquipSort3 varchar(10)='', @EndEquipSort3 varchar(10)='zzzzzzzzzz',
@BegReportGroup varchar(10)='', @EndReportGroup varchar(10)='zzzzzzzzzz',
@BegSubReportGroup varchar(10)='', @EndSubReportGroup varchar(10)='zzzzzzzzzz'

)
as
set nocount on

if @IncludeCo in (null,'')
begin
select @IncludeCo = null
end
else
begin
select @IncludeCo = ',' + @IncludeCo + ','
end

/* insert Revenue info */
select a.*, Status=EMEM.Status, Category=EMEM.Category,CatDesc=EMCM.Description,
EquipDesc=EMEM.Description, RevCodeDesc=EMRC.Description, HPTU=EMRC.HrsPerTimeUM,
CostCodeDesc=EMCC.Description, CTAbbreviation=EMCT.Abbreviation, EMEM.Department,
EMEM.OwnershipStatus, EMEM.Shop, CoName=bHQCO.Name, EMCM.udBPKeyCategory,
udoHQRG.ReportingGroupCode, udoHQRG.ReportingGroupDesc, udoHQRS.ReportingSubGrpCode,
ReportingSubGrpDesc = isnull(udoHQRS.ReportingSubGrpDesc, 'West Virginia'), EMDM.Description,EMCD.GLTransAcct
from (SELECT 'R' as Type,EMRD.EMCo, EMRD.EMGroup,EMRD.Equipment, Month=EMRD.Mth,
EMRD.RevCode, EMRD.TimeUnits, RDollars=EMRD.Dollars,
cast (Null as varchar(10)) CostCode,
cast (Null as tinyint) CostType,
cast (Null as decimal(16,2)) CDollars,
cast (Null as decimal(16,2)) TargetHours,
Null GLTransAcct,
Null
FROM EMRD EMRD With (NoLock)
WHERE (@Co =EMRD.EMCo OR (CHARINDEX(','+convert(varchar(3),EMRD.EMCo)+',',@IncludeCo)>0))
and EMRD.Equipment>= @BeginEquip
and EMRD.Equipment<=@EndEquip
and EMRD.Mth>=@BeginMonth
and EMRD.Mth<=@ThruMonth
and (EMRD.Dollars<>0 or EMRD.TimeUnits<>0)
Union All
Select 'C',EMCD.EMCo,EMCD.EMGroup,EMCD.Equipment,EMCD.Mth,
NULL,NULL,NULL, EMCD.CostCode, EMCD.EMCostType, EMCD.Dollars,Null,Null,EMCD.GLTransAcct
FROM EMCD EMCD With (NoLock)
where (@Co =EMCD.EMCo OR (CHARINDEX(','+convert(varchar(3),EMCD.EMCo)+',',@IncludeCo)>0))
and EMCD.Equipment>=@BeginEquip
and EMCD.Equipment<=@EndEquip
and EMCD.Mth>=@BeginMonth
and EMCD.Mth<=@ThruMonth
and EMCD.Dollars<>0
Union All
SELECT distinct 'T',udoEMTH.Co,EMEM.EMGroup,EMEM.Equipment,udoEMTH.Month,
NULL,NULL,NULL,NULL,NULL,NULL, TargetHours =udoEMTH.TargetHours,Null, null
FROM udoEMTH With (NoLock) LEFT JOIN EMEM EMEM With (NoLock) ON udoEMTH.Co=EMEM.EMCo and udoEMTH.EMCategory=EMEM.Category
WHERE (@Co =EMEM.EMCo OR (CHARINDEX(','+convert(varchar(3),EMEM.EMCo)+',',@IncludeCo)>0))
and EMEM.Equipment>=@BeginEquip
and EMEM.Equipment<=@EndEquip
and udoEMTH.Month>=@BeginMonth
and udoEMTH.Month<=@ThruMonth
and udoEMTH.EMCategory>=@BeginCat
and udoEMTH.EMCategory<=@EndCat
and udoEMTH.EMCategory not between @BeginExcludeCat and @EndExcludeCat
Union All
SELECT 'E',EMEM.EMCo,EMEM.EMGroup,EMEM.Equipment,
NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,Null, Null
From EMEM EMEM With (NoLock)
WHERE (@Co =EMEM.EMCo OR (CHARINDEX(','+convert(varchar(3),EMEM.EMCo)+',',@IncludeCo)>0))
and EMEM.Equipment>=@BeginEquip
and EMEM.Equipment<=@EndEquip
) a
JOIN EMEM With (NoLock) on EMEM.EMCo=a.EMCo and EMEM.Equipment=a.Equipment
Left Join EMCD With (NoLock) on EMCD.EMCo=a.EMCo and EMCD.Equipment=a.Equipment
Left JOIN EMCM With (NoLock) on EMCM.EMCo=a.EMCo and EMCM.Category=EMEM.Category
Left JOIN EMRC With (NoLock) on EMRC.EMGroup=a.EMGroup and EMRC.RevCode=a.RevCode
Left JOIN EMCC With (NoLock) on EMCC.EMGroup=a.EMGroup and EMCC.CostCode=a.CostCode
Left JOIN EMCT With (NoLock) on EMCT.EMGroup=a.EMGroup and EMCT.CostType=a.CostType
Left JOIN EMCH With (NoLock) on EMCH.EMCo=a.EMCo and EMCH.Equipment=a.Equipment and EMCH.EMGroup=a.EMGroup
and EMCH.CostCode=a.CostCode and EMCH.CostType=a.CostType
JOIN bHQCO With (NoLock) on bHQCO.HQCo=a.EMCo
LEFT JOIN bEMDM EMDM With (NoLock) ON EMEM.EMCo=EMDM.EMCo AND EMEM.Department=EMDM.Department
LEFT OUTER JOIN GLPIpart3o GLPIpart3o With (NoLock) ON EMDM.GLCo=GLPIpart3o.GLCo AND EMDM.udBusinessUnit=GLPIpart3o.Instance
LEFT OUTER JOIN udoHQRS udoHQRS With (NoLock) ON GLPIpart3o.udReportingSubGroup=udoHQRS.ReportingSubGrpCode
LEFT OUTER JOIN udoHQRG udoHQRG With (NoLock) ON udoHQRS.ReportingGroup=udoHQRG.ReportingGroupCode
Where (@Co =EMEM.EMCo OR ( CHARINDEX(','+convert(varchar(3),EMEM.EMCo)+',',@IncludeCo)>0))
and EMEM.Equipment>=@BeginEquip and EMEM.Equipment<=@EndEquip
and EMEM.Category>=@BeginCat and EMEM.Category<=@EndCat
and EMEM.Category not between @BeginExcludeCat and @EndExcludeCat
and isnull(EMEM.Department,'') >=@BeginDept
and isnull(EMEM.Department,'')<=@EndDept
and (case when @Status in ('','B') then @Status else EMEM.Status end)=@Status
and (case when @OwnStatus='' then @OwnStatus else EMEM.OwnershipStatus end)=@OwnStatus
and isnull(EMEM.Shop,'')>=@BegShop
and isnull(EMEM.Shop,'')<=@EndShop
and isnull(EMEM.udEquipSort1,'')>=@BeginEquipSort1
and isnull(EMEM.udEquipSort1,'')<=@EndEquipSort1
and isnull(EMEM.udEquipSort2,'')>=@BeginEquipSort2
and isnull(EMEM.udEquipSort2,'')<=@EndEquipSort2
and isnull(EMEM.udEquipSort3,'')>=@BeginEquipSort3
and isnull(EMEM.udEquipSort3,'')<=@EndEquipSort3
and isnull(udoHQRG.ReportingGroupCode,'')>=@BegReportGroup
and isnull(udoHQRG.ReportingGroupCode,'')<=@EndReportGroup
and isnull(udoHQRS.ReportingSubGrpCode,'')>=@BegSubReportGroup
and isnull(udoHQRS.ReportingSubGrpCode,'')<=@EndSubReportGroup
GO

***********End Code***********

 

[SQLDenis]

you have to alias the firs red null

instead of
cast (Null as decimal(16,2)) TargetHours,
Null GLTransAcct,
Null
do

cast (Null as decimal(16,2)) TargetHours,
Null GLTransAcct,
Null SomeNameHere


Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/