Need Help.. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Need Help..

Author

Topic

ganny
Yak Posting Veteran

51 Posts

Posted - 2009-02-19 : 08:07:41

I have written the below SP and successfully compiled. BUT, while executing the SP with parameters.. am getting errors as below:

ERROR: Msg 207, Level 16, State 1, Line 91
Invalid column name 'reportid'.
Msg 207, Level 16, State 1, Line 86
Invalid column name 'reportid'.
Msg 207, Level 16, State 1, Line 96
Invalid column name 'reportid'........... (same msg 14 times).

Kindly help me whats my mistake in this SP since before making this SP, i have executed the queries without getting any errors. But, SP is not luck. (Below is my SP):

-----Starts------
@rptYear varchar(100),
@rptMonth varchar(100),
@svcCode varchar(500),
@vslCode varchar(max),
@terminalCode varchar(max)

--EXEC ESLsp_terminalVol_costControl '2008', '1','^EIDX^~^HGLX^', '^SKI^~^LGA^~^ELT^', '^INNSANSI00^~^CNNGBNBC00^'
--EXEC ESLsp_terminalVol_costControl '2009', '1','^EICS^', '^HYC^~^CIM^~^ACD^', '^INNSAGTI00^'
--EXEC ESLsp_terminalVol_costControl '2009', '1','^EICS^', '^CAP^', '^INNSAGTI00^'

AS
BEGIN

DECLARE @modsvcCode varchar(max);
DECLARE @modVslCode varchar(max);
DECLARE @modTerminalCode varchar(max);
DECLARE @sql varchar(max);

SET @modsvcCode = REPLACE(REPLACE(@svcCode,'~',','),'^','''');
SET @modsvcCode = LTRIM(RTRIM(@modsvcCode));
SET @modVslCode = REPLACE(REPLACE(@vslCode,'~',','),'^','''');
SET @modVslCode = LTRIM(RTRIM(@modVslCode));
SET @modTerminalCode = REPLACE(REPLACE(@terminalCode,'~',','),'^','''');
SET @modTerminalCode = LTRIM(RTRIM(@modTerminalCode));

SET @sql = 'select distinct a.reportid,a.contracttype,a.arrdate,a.grpcallid,a.terminalCode,a.servicecode,a.vesselcode,
a.voyagecode,a.portcode into #tmpcr from reportmaster a (nolock)
where contracttype=''CTL''
and a.terminalcode in (' + @modTerminalCode + ')
and a.servicecode in (' + @modsvcCode +')
and a.vesselcode in ( '+ @modVslCode + ')
and year(arrDate) = ' + @rptYear + '
and month(arrDate) = ' + @rptMonth + '

select a.reportid,EquipmentType,opspod,opspodTerminal,OprType,CargoType,b.VesselCode,b.ServiceCode,DGClass,OOG,dischcallid,bookno,
containerStatus,mode,nor,opspol,opspolTerminal, reftype into #tmpopdD from operationdetail b (nolock) left join #tmpcr a on dischcallid=a.grpcallid
where opspod = a.portcode AND (opspodTerminal = a.terminalcode) AND (OprType = ''import'') AND (CargoType =''Local'') AND
(b.VesselCode in (a.vesselcode)) AND (b.ServiceCode = a.servicecode) and dischcallid=a.grpcallid and status=''D''

SELECT reportid,EquipmentType, ''Normal'' as Status, count(EquipmentType) as EqpCount ,''Discharge'' as type, bkg.transporttermId,
''0'' as soc into #tmpOps2 FROM #tmpOpdD opd (nolock) , booking bkg (nolock)
WHERE opd.DGClass=''N'' and opd.OOG=''N'' and substring(RTRIM(opd.EquipmentType), 1, 1) <> ''R'' and
opd.containerStatus = ''F'' and bkg.book_no = opd.bookno
and (opd.mode = ''F'' or opd.mode = ''M'') group by opd.reportid,opd.EquipmentType , bkg.transporttermId

insert into #tmpOps2 SELECT reportid,EquipmentType, ''Empty'' as Status, count(EquipmentType) as EqpCount ,''Discharge'' as type,
bkg.transporttermId, ''0'' as soc FROM #tmpOpdD opd (nolock) , booking bkg (nolock)
WHERE opd.containerStatus = ''M'' and (opd.mode = ''F'' or opd.mode = ''M'') and bkg.book_no = opd.bookno
group by opd.reportid,opd.EquipmentType , bkg.transporttermId

insert into #tmpOps2 SELECT reportid,EquipmentType, ''Empty'' as Status, count(EquipmentType) as EqpCount ,''Discharge'' as type,
''00'', ''0'' as soc FROM #tmpOpdD opd (nolock)
WHERE opd.containerStatus = ''M'' and (opd.mode = ''F'' or opd.mode = ''M'') and opd.reftype = ''W''
group by opd.reportid,opd.EquipmentType

insert into #tmpOps2 SELECT reportid,EquipmentType, ''DG'' as Status, count(EquipmentType) as EqpCount,''Discharge'' as type ,
bkg.transporttermId, ''0'' as soc FROM #tmpOpdD opd (nolock) , booking bkg (nolock)
WHERE opd.DGClass!=''N'' and substring(RTRIM(opd.EquipmentType), 1, 1) <> ''R'' and
opd.containerStatus = ''F'' and bkg.book_no = opd.bookno
and (opd.mode = ''F'' or opd.mode = ''M'') group by opd.reportid,opd.EquipmentType , bkg.transporttermId

insert into #tmpOps2 SELECT reportid,EquipmentType, ''OOG'' as Status, count(EquipmentType) as EqpCount ,''Discharge'' as type,
bkg.transporttermId , ''0'' as soc FROM #tmpOpdD opd (nolock) , booking bkg (nolock)
WHERE opd.OOG=''Y'' and substring(RTRIM(opd.EquipmentType), 1, 1) <> ''R'' and
opd.containerStatus = ''F'' and bkg.book_no = opd.bookno
and (opd.mode = ''F'' or opd.mode = ''M'') group by opd.reportid,opd.EquipmentType, bkg.transporttermId

insert into #tmpOps2 SELECT reportid,EquipmentType, ''Normal'' as Status, count(EquipmentType) as EqpCount ,''Discharge'' as type,
bkg.transporttermId , ''0'' as soc FROM #tmpOpdD opd (nolock), booking bkg (nolock)
WHERE substring(RTRIM(opd.EquipmentType), 1, 1) = ''R'' and opd.nor = ''Y'' and
opd.containerStatus = ''F'' and bkg.book_no = opd.bookno
and (opd.mode = ''F'' or opd.mode = ''M'') group by opd.reportid,opd.EquipmentType , bkg.transporttermId

insert into #tmpOps2 SELECT reportid,EquipmentType, ''REF'' as Status, count(EquipmentType) as EqpCount ,''Discharge'' as type,
bkg.transporttermId , ''0'' as soc FROM #tmpOpdD opd (nolock) , booking bkg (nolock)
WHERE substring(RTRIM(opd.EquipmentType), 1, 1) = ''R'' and opd.nor = ''N'' and
opd.containerStatus = ''F'' and bkg.book_no = opd.bookno
and (opd.mode = ''F'' or opd.mode = ''M'') group by opd.reportid,opd.EquipmentType , bkg.transporttermId

update #tmpOps2 set #tmpOps2.soc = ''1'' from soctermmapping s (nolock) where s.transportId = #tmpOps2.transporttermId
and s.type=''LD''

select reportid,equipmenttype, status, sum(eqpcount) as soccount,''VLC'' as type,transporttermid into #tmpms from #tmpOps2 (nolock)
where soc = ''1'' group by reportid,equipmenttype, status, transporttermid

select distinct a.reportid,a.contracttype,a.arrdate,a.grpcallid,a.terminalCode,a.servicecode,a.vesselcode,a.voyagecode,a.portcode
into #tmpc from reportmaster a (nolock)
where contracttype=''CTL''
and a.terminalcode in (' + @modTerminalCode + ')
and a.servicecode in (' + @modsvcCode +')
and a.vesselcode in ( '+ @modVslCode + ')
and year(arrDate) = ' + @rptYear + '
and month(arrDate) = ' + @rptMonth + ';

select a.reportid,EquipmentType,opspol,opspolTerminal,OprType,CargoType,b.VesselCode,b.ServiceCode,DGClass,OOG,loadcallid,bookno,
containerStatus,mode,nor,opspod,opspodTerminal, reftype into #tmpOpdL from operationdetail b (nolock) left join #tmpc a on loadcallid=a.grpcallid
where opspol= a.portcode AND (opspolTerminal = a.terminalcode) AND (OprType = ''export'') AND (CargoType =''local'') AND
(b.VesselCode in (a.vesselcode)) AND (b.ServiceCode = a.servicecode) and loadcallid=a.grpcallid and status=''L''

SELECT reportid,EquipmentType, ''Normal'' as Status, count(EquipmentType) as EqpCount ,''Load'' as type, bkg.transporttermId,
''0'' as soc into #tmpOps1 FROM #tmpOpdL opd (nolock) , booking bkg (nolock)
WHERE substring(RTRIM(opd.EquipmentType), 1, 1) <> ''R'' and
opd.DGClass=''N'' and opd.OOG=''N'' and substring(RTRIM(opd.EquipmentType), 1, 1) <> ''R'' and bkg.book_no = opd.bookno
and (opd.mode = ''F'' or opd.mode = ''M'') and opd.containerStatus = ''F''
group by opd.reportid,opd.EquipmentType , bkg.transporttermId

insert into #tmpOps1 SELECT reportid,EquipmentType, ''Empty'' as Status, count(EquipmentType) as EqpCount ,''Load'' as type ,
bkg.transporttermId, ''0'' as soc FROM #tmpOpdL opd (nolock) , booking bkg (nolock)
WHERE bkg.book_no = opd.bookno and opd.containerStatus = ''M'' and (opd.mode = ''F'' or opd.mode = ''M'')
group by opd.reportid,opd.EquipmentType , bkg.transporttermId

insert into #tmpOps1 SELECT reportid,EquipmentType, ''Empty'' as Status, count(EquipmentType) as EqpCount ,''Load'' as type ,
''00'', ''0'' as soc FROM #tmpOpdL opd (nolock)
WHERE opd.containerStatus = ''M'' and opd.reftype = ''W'' and (opd.mode = ''F'' or opd.mode = ''M'')
group by opd.reportid,opd.EquipmentType

insert into #tmpOps1 SELECT reportid,EquipmentType, ''DG'' as Status, count(EquipmentType) as EqpCount,''Load'' as type ,
bkg.transporttermId, ''0'' as soc FROM #tmpOpdL opd (nolock) , booking bkg (nolock)
WHERE opd.DGClass <> ''N'' and substring(RTRIM(opd.EquipmentType), 1, 1) <> ''R'' and
opd.containerStatus = ''F'' and bkg.book_no = opd.bookno
and (opd.mode = ''F'' or opd.mode = ''M'') group by opd.reportid,opd.EquipmentType , bkg.transporttermId

insert into #tmpOps1 SELECT reportid,EquipmentType, ''OOG'' as Status, count(EquipmentType) as EqpCount ,''Load'' as type,
bkg.transporttermId, ''0'' as soc FROM #tmpOpdL opd (nolock) , booking bkg (nolock)
WHERE opd.OOG=''Y'' and substring(RTRIM(opd.EquipmentType), 1, 1) <> ''R'' and
opd.containerStatus = ''F'' and bkg.book_no = opd.bookno
and (opd.mode = ''F'' or opd.mode = ''M'') group by opd.reportid,opd.EquipmentType , bkg.transporttermId

insert into #tmpOps1 SELECT reportid,EquipmentType, ''Normal'' as Status, count(EquipmentType) as EqpCount ,''Load'' as type ,
bkg.transporttermId, ''0'' as soc FROM #tmpOpdL opd (nolock) , booking bkg (nolock)
WHERE substring(RTRIM(opd.EquipmentType), 1, 1) = ''R'' and opd.nor = ''Y'' and
opd.containerStatus = ''F'' and bkg.book_no = opd.bookno
and (opd.mode = ''F'' or opd.mode = ''M'') group by opd.reportid,opd.EquipmentType , bkg.transporttermId

insert into #tmpOps1 SELECT reportid,EquipmentType, ''REF'' as Status, count(EquipmentType) as EqpCount ,''Load'' as type,
bkg.transporttermId, ''0'' as soc FROM #tmpOpdL opd (nolock) , booking bkg (nolock)
WHERE substring(RTRIM(opd.EquipmentType), 1, 1) = ''R'' and opd.nor = ''N'' and
opd.containerStatus = ''F'' and bkg.book_no = opd.bookno
and (opd.mode = ''F'' or opd.mode = ''M'') group by opd.reportid,opd.EquipmentType , bkg.transporttermId

update #tmpOps1 set #tmpOps1.soc = ''1'' from soctermmapping s (nolock) where s.transportId = #tmpOps1.transporttermId
and s.type=''LL''

select reportid,equipmenttype, status, sum(eqpcount) as soccount,''CVL'' as type,transporttermId into #tmpm from #tmpOps1 (nolock)
where soc = ''1'' group by reportid,equipmenttype, status , transporttermId

select * into #tmpmfin from #tmpm
union
select * from #tmpms

select distinct a.reportid,a.contracttype,b.activitycode,b.eqpstatus,a.arrdate,a.grpcallid,
a.terminalCode,a.servicecode,a.vesselcode,a.voyagecode,a.portcode,b.eqptype,b.agencyvolume,b.socvolume,
case when (a.reportid=c.reportid and b.eqpstatus=c.status and b.eqptype=c.equipmenttype and b.socvolume=c.soccount and b.activitycode=c.type)
then c.transporttermid else '''' end as carrigeterm into #tmpmfina
from reportmaster a left outer join reportsummary b on a.reportid=b.reportid
left outer join #tmpmfin c on a.reportid=c.reportid and b.eqpstatus=c.status and b.eqptype=c.equipmenttype and
b.socvolume=c.soccount and b.activitycode=c.type
where terminalcode in (' + @modTerminalCode + ')
and a.servicecode in (' + @modsvcCode +')
and a.vesselcode in ( '+ @modVslCode + ')
and year(arrDate) = ' + @rptYear + '
and month(arrDate) = ' + @rptMonth + ';

select distinct a.reportid,a.contracttype,a.activitycode,a.eqpstatus,a.arrdate,a.grpcallid,a.terminalcode,a.servicecode,
a.vesselcode,a.voyagecode,a.portcode,a.eqptype,a.agencyvolume,a.socvolume,a.carrigeterm as transporttermid,
case when (b.trptterm is null) then '''' else b.trptterm end trptterm into #tempts11
from #tmpmfina a left outer join transportterms b on a.carrigeterm=b.trptcode

select activitycode,eqpstatus,DATEPART(mm, arrDate) as rpMonth,transporttermid,trptterm,terminalcode,portcode,servicecode,
case when eqptype like ''%20'' and activitycode=''cvl'' and eqpstatus<>''Empty'' then agencyvolume else '''' end as LOAD_20_Agncy,
case when eqptype like ''%20'' and activitycode=''vlc'' and eqpstatus<>''Empty'' then agencyvolume else '''' end as DISCH_20_Agncy,
case when eqptype like ''%20'' and activitycode=''cvl'' and eqpstatus=''Empty'' then agencyvolume else '''' end as LOAD_EMPTY_20_Agncy,
case when eqptype like ''%20'' and activitycode=''vlc'' and eqpstatus=''Empty'' then agencyvolume else '''' end as DISCH_EMPTY_20_Agncy,
case when eqptype like ''%20'' and activitycode=''TLD'' and eqpstatus<>''Empty'' then agencyvolume else '''' end as TS_LOAD_20_Agncy,
case when eqptype like ''%20'' and activitycode=''TDI'' and eqpstatus<>''Empty'' then agencyvolume else '''' end as TS_DISCH_20_Agncy,
case when eqptype like ''%20'' and activitycode=''TLD'' and eqpstatus=''Empty'' then agencyvolume else '''' end as TS_LOAD_EMPTY_20_Agncy,
case when eqptype like ''%20'' and activitycode=''TDI'' and eqpstatus=''Empty'' then agencyvolume else '''' end as TS_DISCH_EMPTY_20_Agncy,
case when eqptype like ''%20'' and activitycode=''cvl'' and eqpstatus<>''Empty'' then socvolume else '''' end as LOAD_20_SOC,
case when eqptype like ''%20'' and activitycode=''vlc'' and eqpstatus<>''Empty'' then socvolume else '''' end as DISCH_20_SOC,

case when eqptype like ''%40'' and activitycode=''cvl'' and eqpstatus<>''Empty'' then agencyvolume else '''' end as LOAD_40_Agncy,
case when eqptype like ''%40'' and activitycode=''vlc'' and eqpstatus<>''Empty'' then agencyvolume else '''' end as DISCH_40_Agncy,
case when eqptype like ''%40'' and activitycode=''cvl'' and eqpstatus=''Empty'' then agencyvolume else '''' end as LOAD_EMPTY_40_Agncy,
case when eqptype like ''%40'' and activitycode=''vlc'' and eqpstatus=''Empty'' then agencyvolume else '''' end as DISCH_EMPTY_40_Agncy,
case when eqptype like ''%40'' and activitycode=''TLD'' and eqpstatus<>''Empty'' then agencyvolume else '''' end as TS_LOAD_40_Agncy,
case when eqptype like ''%40'' and activitycode=''TDI'' and eqpstatus<>''Empty'' then agencyvolume else '''' end as TS_DISCH_40_Agncy,
case when eqptype like ''%40'' and activitycode=''TLD'' and eqpstatus=''Empty'' then agencyvolume else '''' end as TS_LOAD_EMPTY_40_Agncy,
case when eqptype like ''%40'' and activitycode=''TDI'' and eqpstatus=''Empty'' then agencyvolume else '''' end as TS_DISCH_EMPTY_40_Agncy,
case when eqptype like ''%40'' and activitycode=''cvl'' and eqpstatus<>''Empty'' then socvolume else '''' end as LOAD_40_SOC,
case when eqptype like ''%40'' and activitycode=''vlc'' and eqpstatus<>''Empty'' then socvolume else '''' end as DISCH_40_SOC

into #tempts111
from #tempts11

select rpMonth, case when rpmonth=''1'' then ''January''
when rpmonth=''2'' then ''February''
when rpmonth=''3'' then ''March''
when rpmonth=''4'' then ''April''
when rpmonth=''5'' then ''May''
when rpmonth=''6'' then ''June''
when rpmonth=''7'' then ''July''
when rpmonth=''8'' then ''August''
when rpmonth=''9'' then ''September''
when rpmonth=''10'' then ''October''
when rpmonth=''11'' then ''November''
when rpmonth=''12'' then ''December'' else '''' end as rpMonthdes,
servicecode,portcode,terminalcode,transporttermid,trptterm,
sum(LOAD_20_Agncy) as load20teus,
sum(LOAD_20_SOC) as load20SOC,
sum(LOAD_40_Agncy) as load40teus,
sum(LOAD_40_SOC) as load40SOC,

sum(DISCH_20_Agncy) as disch20teus,
sum(DISCH_20_SOC) as disch20SOC,
sum(DISCH_40_Agncy) as disch40teus,
sum(DISCH_40_SOC) as disch40SOC,

sum(TS_LOAD_20_Agncy) as TSload20teus,
sum(TS_LOAD_40_Agncy) as TSload40teus,
sum(TS_DISCH_20_Agncy) as TSdisch20teus,
sum(TS_DISCH_40_Agncy) as TSdisch40teus,

sum(LOAD_EMPTY_20_Agncy) as loadEmpty20teus,
sum(LOAD_EMPTY_40_Agncy) as loadEmpty40teus,
sum(DISCH_EMPTY_20_Agncy) as dischEmpty20teus,
sum(DISCH_EMPTY_40_Agncy) as dischEmpty40teus,

sum(TS_LOAD_EMPTY_20_Agncy) as TSloadEmpty20teus,
sum(TS_LOAD_EMPTY_40_Agncy) as TSloadEmpty40teus,
sum(TS_DISCH_EMPTY_20_Agncy) as TSdischEmpty20teus,
sum(TS_DISCH_EMPTY_40_Agncy) as TSdischEmpty40teus

from #tempts111
group by rpmonth,servicecode,portcode,terminalcode,transporttermid,trptterm order by portcode

drop table #tmpcr
drop table #tmpopdD
drop table #tmpOps2
drop table #tmpc
drop table #tmpopdl
drop table #tmpops1
drop table #tmpm
drop table #tmpms
drop table #tmpmfin
drop table #tmpmfina
drop table #tempts11
drop table #tempts111 '

exec (@sql);

END

----Ends------

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-02-19 : 08:16:31

Do you have reportid column in reportmaster table ?

ganny
Yak Posting Veteran

51 Posts

Posted - 2009-02-19 : 08:17:56

quote:
Originally posted by sakets_2000

Do you have reportid column in reportmaster table ?

Yes, i have reportid column in the repormaster table.

asgast
Posting Yak Master

149 Posts

Posted - 2009-02-19 : 08:41:13

its possible to run this query without using dynamic sql at all, think about it.

then try scaling your code into blocks, add print '1' - print'...' after each block and see where you loose our reportid column. Now way is it possible to debug and read a code with so many unnecessary tmp tables, for me. So nothing more I can add.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources