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_N umericAudit' 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_Nu mericAudit.aEve ntID,
dbo.tbl_5001_Nu mericAudit.nPar entEventID,
dbo.tbl_5001_Nu mericAudit.nUse rID,
dbo.tbl_5001_Nu mericAudit.nCol umnID,
dbo.tbl_5001_Nu mericAudit.nKey ID,
dbo.tbl_5001_Nu mericAudit.dCha ngeTime,
CAST(dbo.tbl_50 01_NumericAudit .vToValue AS
nVarchar(512)) AS vToValue, dbo.tbl_5001_Nu mericAudit.nCha ngeMode,
dbo.tbl_5001_Nu mericAudit.tChi ldEventText, CASE
WHEN nConstraintType = 3 THEN 5 ELSE tblColumnMain.n DataType END AS
nDataType,
dbo.tbl_5001_Nu mericAudit.nID,
CAST(dbo.tbl_50 01_NumericAudit .vFromValue AS nVarchar(512)) AS
vFromValue
FROM dbo.tbl_5001_Nu mericAudit WITH (NOLOCK) LEFT OUTER JOIN
dbo.tblColumnMa in WITH (NoLock) INNER JOIN
--
-- Posters comment: here is the double ON
--
dbo.tblCustomFi eld WITH (NoLock) ON
dbo.tblColumnMa in.aColumnID = dbo.tbl_5001_Nu mericAudit.nCol umnID ON
dbo.tbl_5001_Nu mericAudit.nCol umnID =
dbo.tblCustomFi eld.nColumnID LEFT OUTER JOIN
dbo.tblConstrai nt WITH (NOLOCK) ON
dbo.tblCustomFi eld.nConstraint ID = dbo.tblConstrai nt.aConstraintI D AND
(dbo.tblConstra int.nConstraint Type = 4 OR
dbo.tblConstrai nt.nConstraintT ype = 9 OR
dbo.tblConstrai nt.nConstraintT ype = 3)
UNION ALL
SELECT aEventID, nParentEventID, nUserID, nColumnID, nKeyID,
dChangeTime, CAST(CAST(vToVa lue AS decimal(19, 6)) AS nVarchar(512)) AS
vToValue,
nChangeMode, tChildEventText , 5 AS nDataType,
nID, CAST(CAST(vFrom Value AS decimal(19, 6)) AS nVarchar(512)) AS
vFromValue
FROM dbo.tbl_5001_Fl oatAudit 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_St ringAudit WITH (NOLOCK)
UNION ALL
SELECT aEventID, nParentEventID, nUserID, nColumnID, nKeyID,
dChangeTime, CONVERT(nVarcha r(512), vToValue, 121) AS vToValue,
nChangeMode,
tChildEventText , 3 AS nDataType, nID,
CONVERT(nVarcha r(512), vFromValue, 121) AS vFromValue
FROM dbo.tbl_5001_Da teAudit WITH (NOLOCK)

[Roy Harvey]

Re: view works, but the sql from the view does not

On 27 Oct 2006 07:32:38 -0700, "rcamarda" <robc390@hotmai l.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_N umericAudit' 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