#temp Table 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)

#temp Table help

Author

Topic

eljapo4
Posting Yak Master

100 Posts

Posted - 2010-11-10 : 04:48:43

I'm doing the following insert into #mp_Dept44:
DECLARE
@DOT NVARCHAR(18)
SET @DOT = '20225733'

CREATE TABLE DBO.#mp_Dept44
(
FromDept INT NULL,
ToDept INT NULL,
TransCode INT NULL,
TransCodeDesc VARCHAR(50) NULL,
DOT NVARCHAR(18) NULL,
OutputDOT NVARCHAR(18) NULL,
PostingDate DATETIME,
Archived DATETIME,
ArchivedBy NVARCHAR(30) NULL,
TotalNetWeight NUMERIC(10,4) NULL,
ProductNo NVARCHAR(18) NULL,
ScheduleNo NVARCHAR(10) NULL,
Lot INT NULL,
SubLot VARCHAR(4) NULL,
BatchNo VARCHAR(20) NULL,
RackNo INT NULL,
MixNo INT NULL,
MaterialID INT NULL,
usr_OvenNo INT NULL
)

INSERT INTO #mp_Dept44 --TC2 Insert
SELECT TransferDeptID AS 'FromDept',
DepartmentID AS 'ToDept',
mf_invobject.TransCode AS 'TransCode',
usr_TransCode.Description AS 'TransCodeDesc',
ObjectNo AS 'DOT',
usr_ParentObjectNo AS 'OutputDOT',
PostingDate AS 'PostingDate',
mf_invobject.Archived AS 'Archived',
mf_invobject.ArchivedBy 'ArchivedBy',
TotalNetWeight,
usr_ProductNo,
usr_ScheduleNo AS 'ScheduleNo',
RIGHT(REPLICATE('0', 10) + CONVERT(VARCHAR(10),usr_LotNo),10) AS 'Lot',
usr_SubLot AS 'SubLot',
usr_SSCC AS 'BatchNo',
usr_RackNo AS 'RackNo',
usr_MixNo,
MaterialId,
usr_OvenNo
FROM mf_invobject (NOLOCK)
INNER JOIN usr_TransCode (NOLOCK) ON usr_TransCode.TransCode = mf_invobject.TransCode

WHERE ObjectNo = @DOT
AND mf_invobject.TransCode = 2
AND Direction = 1
ORDER BY PostingDate

which works fine but the next step I'm having trouble with:

INSERT INTO #mp_Dept44 --TC4 Insert
SELECT TransferDeptID AS 'FromDept',
DepartmentID AS 'ToDept',
mf_invobject.TransCode AS 'TransCode',
usr_TransCode.Description AS 'TransCodeDesc',
ObjectNo AS 'DOT',
usr_ParentObjectNo AS 'OutputDOT',
PostingDate AS 'PostingDate',
mf_invobject.Archived AS 'Archived',
mf_invobject.ArchivedBy 'ArchivedBy',
TotalNetWeight,
usr_ProductNo,
usr_ScheduleNo AS 'ScheduleNo',
RIGHT(REPLICATE('0', 10) + CONVERT(VARCHAR(10),usr_LotNo),10) AS 'Lot',
usr_SubLot AS 'SubLot',
usr_SSCC AS 'BatchNo',
usr_RackNo AS 'RackNo',
usr_MixNo,
MaterialId,
usr_OvenNo
FROM mf_invobject (NOLOCK)
INNER JOIN usr_TransCode (NOLOCK) ON usr_TransCode.TransCode = mf_invobject.TransCode

WHERE
mf_invobject.usr_ScheduleNo = #mp_Dept44.usr_ScheduleNo
and mf_invobject.usr_SSCC = #mp_Dept44.usr_SSCC
and mf_invobject.materialid = #mp_Dept44.materialid
and mf_invobject.usr_ovenno = #mp_Dept44.usr_ovenno
AND mf_invobject.TransCode = 4
AND Direction = 1
ORDER BY PostingDate

I'm getting this error when running the code "Msg 107, Level 16, State 3, Line 130
The column prefix '#mp_Dept44' does not match with a table name or alias name used in the query."

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-11-10 : 05:03:41

You must use table #mp_Dept44 After "FROM" in order to use columns of table.
I think you are missing table to join.

Vaibhav T

If I cant go back, I want to go fast...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources