Posted by rcamarda on 10/27/06 14:32

I was looking through our vendors views, searching for something I
needed for our Datawarehouse and I came across something I do not
understand: I found a view that lists data when I use it in t-sql,
however when I try to use the statement when I modified the view (via
MS SQL Server Management Studio) I can not execute the statement. I get

The column prefix 'dbo.tbl_5001_NumericAudit' does not match with a
table name or alias name used in the query.

Upon closer inspection, I found two ON for the inner join, which I dont
think is correct.
So, how can the view work, but not the SQL that defines the view?
SQL Server 2000, up to date patches:

SELECT dbo.tbl_5001_NumericAudit.aEventID,
dbo.tbl_5001_NumericAudit.nParentEventID,
dbo.tbl_5001_NumericAudit.nUserID,
dbo.tbl_5001_NumericAudit.nColumnID,
dbo.tbl_5001_NumericAudit.nKeyID,
dbo.tbl_5001_NumericAudit.dChangeTime,
CAST(dbo.tbl_5001_NumericAudit.vToValue AS
nVarchar(512)) AS vToValue, dbo.tbl_5001_NumericAudit.nChangeMode,
dbo.tbl_5001_NumericAudit.tChildEventText, CASE
WHEN nConstraintType = 3 THEN 5 ELSE tblColumnMain.nDataType END AS
nDataType,
dbo.tbl_5001_NumericAudit.nID,
CAST(dbo.tbl_5001_NumericAudit.vFromValue AS nVarchar(512)) AS
vFromValue
FROM dbo.tbl_5001_NumericAudit WITH (NOLOCK) LEFT OUTER JOIN
dbo.tblColumnMain WITH (NoLock) INNER JOIN
--
-- Posters comment: here is the double ON
--
dbo.tblCustomField WITH (NoLock) ON
dbo.tblColumnMain.aColumnID = dbo.tbl_5001_NumericAudit.nColumnID ON
dbo.tbl_5001_NumericAudit.nColumnID =
dbo.tblCustomField.nColumnID LEFT OUTER JOIN
dbo.tblConstraint WITH (NOLOCK) ON
dbo.tblCustomField.nConstraintID = dbo.tblConstraint.aConstraintID AND
(dbo.tblConstraint.nConstraintType = 4 OR
dbo.tblConstraint.nConstraintType = 9 OR
dbo.tblConstraint.nConstraintType = 3)
UNION ALL
SELECT aEventID, nParentEventID, nUserID, nColumnID, nKeyID,
dChangeTime, CAST(CAST(vToValue AS decimal(19, 6)) AS nVarchar(512)) AS
vToValue,
nChangeMode, tChildEventText, 5 AS nDataType,
nID, CAST(CAST(vFromValue AS decimal(19, 6)) AS nVarchar(512)) AS
vFromValue
FROM dbo.tbl_5001_FloatAudit WITH (NOLOCK)
UNION ALL
SELECT aEventID, nParentEventID, nUserID, nColumnID, nKeyID,
dChangeTime, CAST(vToValue AS nVarchar(512)) AS vToValue, nChangeMode,
tChildEventText, 2 AS nDataType, nID,
CAST(vFromValue AS nVarchar(512)) AS vFromValue
FROM dbo.tbl_5001_StringAudit WITH (NOLOCK)
UNION ALL
SELECT aEventID, nParentEventID, nUserID, nColumnID, nKeyID,
dChangeTime, CONVERT(nVarchar(512), vToValue, 121) AS vToValue,
nChangeMode,
tChildEventText, 3 AS nDataType, nID,
CONVERT(nVarchar(512), vFromValue, 121) AS vFromValue
FROM dbo.tbl_5001_DateAudit WITH (NOLOCK)

[Back to original message]