Posted by Roy Harvey on 10/27/06 15:38

On 27 Oct 2006 07:32:38 -0700, "rcamarda" <robc390@hotmail.com> wrote:

>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

[Back to original message]