Column Prefix does not match, using if statement 1

[Davidmc555]

Hi, I'm trying to write a stored procedure that updates our floor database. I want it to then check that the quantities produced and scanned match and to list the individual items as gone as it does so.

Declare @TotalMade integer
Declare @TotScanned integer

Update CamdenAccess

Set QuantityScanned = QuantityScanned + T1.SumQty, BarcodesScanned = BarcodesScanned + T1.CountQty,
@TotScanned = (SELECT BarCodesScanned FROM CamdenAccess Where AccessID = T1.AccessID),
@TotalMade = (SELECT BarCodesCreated FROM CamdenAccess WHERE AccessID = T1.AccessID)

From 

CamdenAccess Inner Join
(
SELECT  
AccessoryPackItems.AccessID, SUM(AccessoryPackItems.Quantity) AS SumQty, COUNT(AccessoryPackItems.Quantity) As CountQty
FROM CamdenAccess 

INNER JOIN
AccessoryPackItems ON 

CamdenAccess.AccessID = AccessoryPackItems.AccessID

--WHERE     (AccessoryPackItems.PackageID = @PackID)
WHERE     (AccessoryPackItems.PackageID = '44')

GROUP BY AccessoryPackItems.AccessID
) T1

On 
CamdenAccess.AccessID = T1.AccessID

if @TotScanned = @TotalMade	--all items for that line on the order have been completely delivered
	BEGIN
		UPDATE CamdenAccess SET Gone = 1 WHERE AccessID = T1.AccessID
	END

This portion is only for the query, I'll finish the Stored procedure later once this bit is correct. I get a run error of "Server: Msg 107, Level 16, State 2, Line 1
The column prefix 'T1' does not match with a table name or alias name used in the query." which is the bit in the if statement. I tried juggling the code and looking at other posts but couldn't find an answer.

Anybody got any suggestions? I'd be most grateful.

 

[hmckillop]

try

Declare @TotalMade integer
Declare @TotScanned integer

Update CamdenAccess

Set QuantityScanned = QuantityScanned + T1.SumQty, BarcodesScanned = BarcodesScanned + T1.CountQty,
@TotScanned = t2.BarCodesScanned, 
@TotalMade = t2.BarCodesCreated 

From 

CamdenAccess Inner Join
(
SELECT  
AccessoryPackItems.AccessID, SUM(AccessoryPackItems.Quantity) AS SumQty, COUNT(AccessoryPackItems.Quantity) As CountQty
FROM CamdenAccess 

INNER JOIN
AccessoryPackItems ON 

CamdenAccess.AccessID = AccessoryPackItems.AccessID

--WHERE     (AccessoryPackItems.PackageID = @PackID)
WHERE     (AccessoryPackItems.PackageID = '44')

GROUP BY AccessoryPackItems.AccessID
) T1

On 
CamdenAccess.AccessID = T1.AccessID
inner join (SELECT BarCodesScanned, BarCodesCreated  FROM CamdenAccess) t2 on t2.AccessID = T1.AccessID

if @TotScanned = @TotalMade    --all items for that line on the order have been completely delivered
    BEGIN
        UPDATE CamdenAccess SET Gone = 1 WHERE AccessID = T1.AccessID
    END

i think thats it

"I'm living so far beyond my income that we may almost be said to be living apart

 

[Davidmc555]

I have an unfortunately, that brought up the same error and some new ones (BarcodesScanned as ambiguous column, needed to be pointed at CamdenAccess table and t2 needed accessID included in it's select.) but thanks all the same.

I'll keep trying and post up a solution if I find one.

 

[Pattycake245]

I think it's because T1 is a derived table and not an actual physical table, and you wouldn't be able to reference this table in a separate statement, you'd have to rewriet the derived table query again.

Tim

 

[Davidmc555]

Cheers, I had a inkling it was something to do with that and that some brainy person would include the if within the update statement but if it works...

DECLARE @TotalMade integer
DECLARE @TotScanned integer

UPDATE CamdenAccess

SET QuantityScanned = QuantityScanned + T1.SumQty, BarcodesScanned = BarcodesScanned + T1.CountQty

FROM

CamdenAccess INNER JOIN
(
SELECT  
AccessoryPackItems.AccessID, SUM(AccessoryPackItems.Quantity) AS SumQty, COUNT(AccessoryPackItems.Quantity) AS CountQty
FROM CamdenAccess 

INNER JOIN
AccessoryPackItems ON 

CamdenAccess.AccessID = AccessoryPackItems.AccessID

--WHERE     (AccessoryPackItems.PackageID = @PackID)
WHERE     (AccessoryPackItems.PackageID = '44')

GROUP BY AccessoryPackItems.AccessID
) T1

On 
CamdenAccess.AccessID = T1.AccessID

--Update 'Gone' Status

Update CamdenAccess

Set Gone = 1 

From CamdenAccess INNER JOIN
(
SELECT 
AccessoryPackItems.AccessID
FROM CamdenAccess

INNER JOIN AccessoryPackItems ON CamdenAccess.AccessID = AccessoryPackItems.AccessID

WHERE AccessoryPackItems.PackageID = '44' and CamdenAccess.BarcodesScanned = CamdenAccess.BarcodesCreated

Group BY AccessoryPackItems.AccessID
) T2

ON CamdenAccess.AccessID = T2.AccessID

Not the prettiest piece of code by a long shot but it's functional. Thanks again to everyone for their help.