Syntax error when referencing a table in a Stored Procedure ?

[kpmvb]

Do you know how I can fix a syntax error:

Error 107: the column prefix 'tblExclusion' does not match with a table name or alias name used in the query

I believe this error is caused by the last line below:

CREATE PROCEDURE dbo.procSpACSExcl
@RptYear int
AS
If Exists(SELECT * FROM dbo.SYSOBJECTS WHERE NAME = 'tblSpACS' AND TYPE = 'U')
DROP TABLE tblSpACS
SELECT Null AS [Job Number], Null AS [Bus. Unit], CASE WHEN LEN(C.SSN) = 0 THEN C.TaxID ELSE C.SSN END AS [SSN], P.PropertyType AS [Prop CD],
Right(C.OfficeNumber,3) + ' ' + C.CustomerNumber AS [Acct Number],
P.CUSIP AS [Cusip Number], UPPER(P.SecurityName) AS [Cusip Name], P.Symbol AS [User 1], P.PlanNumber AS [User 2]
INTO tblSpACS
FROM tblStatesAll As S INNER JOIN (tblCustomers As C INNER JOIN tblProducts As P ON C.CustomerNumber = P .CustomerNumber AND C.OfficeNumber = P.OfficeNumber)
ON S.StateFS = C.ResStateCode
WHERE (S.FallCycle= 0 AND C.RedFlag = 'N' AND LEN(P.InsuranceContract) = 0) AND
C.CustomerNumber NOT IN (SELECT tblExclusion.CustomerNumber FROM tblExclusion)
AND C.OfficeNumber NOT IN (SELECT tblExclusion.OfficeNumber FROM tblExclusion) AND
((P .PropertyType='MUTUAL FUND' AND C.DateLost <= CAST((@RptYear - S.MutualFS) AS VARCHAR) + '-12-31' AND LEN(P.IRACode) = 0 AND (P.Quantity > 0 OR P.CashBalance > 0)) OR
(P .PropertyType='FIXED INCOME' AND C.DateLost <= CAST((@RptYear - S.BondsFS) AS VARCHAR) + '-12-31' AND LEN(P.IRACode) > 0 AND (P.Quantity > 0 OR P.CashBalance > 0) AND
(C.DateOfBirth <= CAST(((@RptYear - S.IRAFS)-71) AS VARCHAR) + '-06-30')))
AND (tblExclusion.CustomerNumber IS NOT NULL)

 

[andrea96]

You need to join tblExclusion in the query. It looks like you have only referenced it in a subquery.
Andrea

 

[Catadmin]

Andrea is correct. Your last line isn't a subquery matching something else in your WHERE clause, it *is* a condition of the WHERE clause. Since you don't join to tblExclusion previous to this, SQL is freaking out and doesn't understand what you want it to do.



Catadmin - MCDBA, MCSA
"The only stupid question is the one that *wasn't* asked.