view works, but the sql from the view does not

[rcamarda]

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)

[Roy Harvey]

On 27 Oct 2006 07:32:38 -0700, "rcamarda" <ro*****@hotmail.comwrote:

>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:

I don't have a definite answer to your last question about how it is
working now, with the view in the current state. I have found that if
view A references view B, a change to view B will not be reflected in
A until A is recompiled. Likewise for a stored procedure referencing
B. Perhaps that is part of the answer.

But I can explain some of what you are seeing.

Two ON clauses in a row is actually legal. Consider these three
examples:

--The usual way to do things. This works.
select count(*)
from sysindexes as I
join sysobjects as O
on I.id = O.id
join syscolumns as C
on O.id = C.id

--The unusual way to do it. This also works.
--Note that the ON clause for the first JOIN appears last.
select count(*)
from sysindexes as I
join sysobjects as O
join syscolumns as C
on O.id = C.id
on I.id = O.id

--This looks a lot like the second example, but blows up
--when executed with the same message you are getting.
--Note that the ON clauses are reversed in order.
select count(*)
from sysindexes as I
join sysobjects as O
join syscolumns as C
on I.id = O.id
on O.id = C.id
Server: Msg 107, Level 16, State 2, Line 1
The column prefix 'I' does not match with a table name or alias name
used in the query.

The rule of thumb seems to be that the ON clause right after a JOIN
must apply to THAT join, but it is valid for an ON clause to be
deferred until after an intervening join. As though things are
reslolved from the inside working outward.

Anyway, you have two ways to fix it. One is to swap the two ON
clauses, the other is to move one up to follow the OUTER JOIN.

Roy Harvey
Beacon Falls, CT