Hello!

I am new to this group and I hope anyone can help me. I have an error
message which is very complicated to me. Okay this message is very
simpel, but I don`t understand how to build my SQL statement. I use
MsSQL 2000 and I am new to Microsoft SQL. I have searched the web and
read the online help, but it is strange to me. At first here is my
statement:

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

ALTER VIEW dbo.view_results_7
AS
SELECT TOP 100 PERCENT dbo.view_results_7a.*, dbo.table.MCC,
dbo.table.MNC, ...dbo.table.HPRP
FROM dbo.view_results_7a INNER JOIN
dbo.table ON dbo.view_results_7a.MCC COLLATE SQL_Latin1_CP1_CI_AS =
dbo.table.MCC
WHERE dbo.view_results_7a.IMSI <>
IMSI_Blacklist.tblIMSI_Stiering_Blacklist.IMSI
ORDER BY dbo.view_results_7a.BegTime DESC

GO
SET_QUOTED IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

The part in the WHERE clause throws this error. It is the error message
number 107 and the message:
"The column prefix '%.*ls' does not match with a table name or alias
name used in the query."
What is wrong with this statement? My only experience in SQL is MySQL
and I have written this statement like a MySQL statement. So is there
anyone who can help me? Please I need your help!

I don't think you can have dbo.table.* and probably that's what the
error says when it tried to expand dbo.view_results_7a.*
....not sure though...

Comagmbh@gmx.de wrote:
> Hello!
>
> I am new to this group and I hope anyone can help me. I have an error
> message which is very complicated to me. Okay this message is very
> simpel, but I don`t understand how to build my SQL statement. I use
> MsSQL 2000 and I am new to Microsoft SQL. I have searched the web and
> read the online help, but it is strange to me. At first here is my
> statement:
>
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_NULLS ON
> GO
>
> ALTER VIEW dbo.view_results_7
> AS
> SELECT TOP 100 PERCENT dbo.view_results_7a.*, dbo.table.MCC,
> dbo.table.MNC, ...dbo.table.HPRP
> FROM dbo.view_results_7a INNER JOIN
> dbo.table ON dbo.view_results_7a.MCC COLLATE SQL_Latin1_CP1_CI_AS =
> dbo.table.MCC
> WHERE dbo.view_results_7a.IMSI <>
> IMSI_Blacklist.tblIMSI_Stiering_Blacklist.IMSI
> ORDER BY dbo.view_results_7a.BegTime DESC
>
> GO
> SET_QUOTED IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
>
> The part in the WHERE clause throws this error. It is the error message
> number 107 and the message:
> "The column prefix '%.*ls' does not match with a table name or alias
> name used in the query."
> What is wrong with this statement? My only experience in SQL is MySQL
> and I have written this statement like a MySQL statement. So is there
> anyone who can help me? Please I need your help!

Hi,
I modified your query a little bit:

ALTER VIEW view_results_7
AS
SELECT TOP 100 PERCENT view_results_7a.*, table.MCC,
table.MNC, ...table.HPRP
FROM view_results_7a INNER JOIN
table ON view_results_7a.MCC = table.MCC
WHERE view_results_7a.IMSI <> tblIMSI_Stiering_Blacklist.IMSI
ORDER BY view_results_7a.BegTime DESC

Comagmbh@gmx.de wrote:
> Hello!
>
> I am new to this group and I hope anyone can help me. I have an error
> message which is very complicated to me. Okay this message is very
> simpel, but I don`t understand how to build my SQL statement. I use
> MsSQL 2000 and I am new to Microsoft SQL. I have searched the web and
> read the online help, but it is strange to me. At first here is my
> statement:
>
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_NULLS ON
> GO
>
> ALTER VIEW dbo.view_results_7
> AS
> SELECT TOP 100 PERCENT dbo.view_results_7a.*, dbo.table.MCC,
> dbo.table.MNC, ...dbo.table.HPRP
> FROM dbo.view_results_7a INNER JOIN
> dbo.table ON dbo.view_results_7a.MCC COLLATE SQL_Latin1_CP1_CI_AS =
> dbo.table.MCC
> WHERE dbo.view_results_7a.IMSI <>
> IMSI_Blacklist.tblIMSI_Stiering_Blacklist.IMSI
> ORDER BY dbo.view_results_7a.BegTime DESC
>
> GO
> SET_QUOTED IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
>
> The part in the WHERE clause throws this error. It is the error message
> number 107 and the message:
> "The column prefix '%.*ls' does not match with a table name or alias
> name used in the query."
> What is wrong with this statement? My only experience in SQL is MySQL
> and I have written this statement like a MySQL statement. So is there
> anyone who can help me? Please I need your help!

Hi

Thanks for your answer, but the problem still exists. There is the same
error message like before.

othellomy@yahoo.com schrieb:

>
> ALTER VIEW view_results_7
> AS
> SELECT TOP 100 PERCENT view_results_7a.*, table.MCC,
> table.MNC, ...table.HPRP
> FROM view_results_7a INNER JOIN
> table ON view_results_7a.MCC = table.MCC
> WHERE view_results_7a.IMSI <> tblIMSI_Stiering_Blacklist.IMSI
> ORDER BY view_results_7a.BegTime DESC
>


I have forgot to tell you that the table tblIMSI_Stiering_Blacklist is
from another database but on the same server, so I have to write in
this way:

ALTER VIEW view_results_7
AS
SELECT TOP 100 PERCENT view_results_7a.*, table.MCC,
table.MNC, ...table.HPRP
FROM view_results_7a INNER JOIN
table ON view_results_7a.MCC = table.MCC
WHERE view_results_7a.IMSI <>
IMSI_Blacklist.dbo.tblIMSI_Stiering_Blacklist.IMSI
ORDER BY view_results_7a.BegTime DESC

The error is still the same :-(

(Comagmbh@gmx.de) writes:
> I am new to this group and I hope anyone can help me. I have an error
> message which is very complicated to me. Okay this message is very
> simpel, but I don`t understand how to build my SQL statement. I use
> MsSQL 2000 and I am new to Microsoft SQL. I have searched the web and
> read the online help, but it is strange to me. At first here is my
> statement:
>
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_NULLS ON
> GO
>
> ALTER VIEW dbo.view_results_7
> AS
> SELECT TOP 100 PERCENT dbo.view_results_7a.*, dbo.table.MCC,
> dbo.table.MNC, ...dbo.table.HPRP
> FROM dbo.view_results_7a INNER JOIN
> dbo.table ON dbo.view_results_7a.MCC COLLATE SQL_Latin1_CP1_CI_AS =
> dbo.table.MCC
> WHERE dbo.view_results_7a.IMSI <>
> IMSI_Blacklist.tblIMSI_Stiering_Blacklist.IMSI
> ORDER BY dbo.view_results_7a.BegTime DESC
>...
> The part in the WHERE clause throws this error. It is the error message
> number 107 and the message:
> "The column prefix '%.*ls' does not match with a table name or alias
> name used in the query."
> What is wrong with this statement? My only experience in SQL is MySQL
> and I have written this statement like a MySQL statement. So is there
> anyone who can help me? Please I need your help!

Didn't you get a complete error message? That should tell you what is
wrong.

But I can see the error: IMSI_Blacklist.tblIMSI_Stiering_Blacklist comes
out of nowhere. I don't know what is supposed to be, so I can't suggest
an alternative. But I would not expect the above to work on MySQL either.

Two more things:
1) Remove TOP 100 PERCENT and ORDER BY. They don't mean anything logically,
but they can result in extra processing. In SQL 2000 it may seem that
if you run a SELECT on the view that you always get data in the order
of the ORDER BY clause, but that is mere chance, and it does not happen
that easily in SQL 2005.

2) Try to remove the COLLATE clause. Adding to the query when it is not
needed can prevent indexes from being used and hamper performance.



--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

Erland Sommarskog schrieb:
>
> Didn't you get a complete error message? That should tell you what is
> wrong.
>
> But I can see the error: IMSI_Blacklist.tblIMSI_Stiering_Blacklist comes
> out of nowhere. I don't know what is supposed to be, so I can't suggest
> an alternative. But I would not expect the above to work on MySQL either.
>
> Two more things:
> 1) Remove TOP 100 PERCENT and ORDER BY. They don't mean anything logically,
> but they can result in extra processing. In SQL 2000 it may seem that
> if you run a SELECT on the view that you always get data in the order
> of the ORDER BY clause, but that is mere chance, and it does not happen
> that easily in SQL 2005.
>
> 2) Try to remove the COLLATE clause. Adding to the query when it is not
> needed can prevent indexes from being used and hamper performance.
>

Hi! Okay I wrote this:

ALTER VIEW view_results_7
AS
SELECT view_results_7a.*, table.MCC,
table.MNC, ...table.HPRP
FROM view_results_7a INNER JOIN
table ON view_results_7a.MCC = table.MCC AND view_results_7a.MNC =
table.MNC
WHERE view_results_7a.IMSI <>
IMSI_Blacklist..tblIMSI_Stiering_Blacklist.IMSI


But there is still this error message:

Server: No 107, 16, state 3 procedure view_results_7
"The column prefix '%.*ls' does not match with a table name or alias
name used in the query."

(Comagmbh@gmx.de) writes:
> Hi! Okay I wrote this:
>
> ALTER VIEW view_results_7
> AS
> SELECT view_results_7a.*, table.MCC,
> table.MNC, ...table.HPRP
> FROM view_results_7a INNER JOIN
> table ON view_results_7a.MCC = table.MCC AND view_results_7a.MNC =
> table.MNC
> WHERE view_results_7a.IMSI <>
> IMSI_Blacklist..tblIMSI_Stiering_Blacklist.IMSI
>
>
> But there is still this error message:
>
> Server: No 107, 16, state 3 procedure view_results_7
> "The column prefix '%.*ls' does not match with a table name or alias
> name used in the query."

What ugly environment are you using that only gives the unexpanaded
error message?

In any case, that IMSI_Blacklist..tblIMSI_Stiering_Blacklist still
comes out of nowhere. You need to mention it in the FROM-JOIN clause.

Besides, you have something called "table" in the query above. Since
TABLE is a reserved keyword, I would expect that to yield a syntax error,
so I suspect that you are not even posting the query you are using.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

Hi,
As suggested by Erland, table is a reserve keyword therefore you
can't have it in your query. I thought you are using table to
reference some pseodo table in the from clause so I used that because
you did not post the actual query. You have to post the actual query
for two reasons. Reason 1: I can see if you are using the 'table'
keyword in your query. If yeas, then it is obvious that was the error.
Number 2: if you have a table name in the select clause but missing
from the from clause then it will also cause error and also if you have
more tables in the from clause but not joining them appropriately in
the join or where clasue then it will be error too. So, the best thing
is if you post the actual query exactly as is.

Comagmbh@gmx.de wrote:
> Hi
>
> Thanks for your answer, but the problem still exists. There is the same
> error message like before.
>
> othellomy@yahoo.com schrieb:
>
> >
> > ALTER VIEW view_results_7
> > AS
> > SELECT TOP 100 PERCENT view_results_7a.*, table.MCC,
> > table.MNC, ...table.HPRP
> > FROM view_results_7a INNER JOIN
> > table ON view_results_7a.MCC = table.MCC
> > WHERE view_results_7a.IMSI <> tblIMSI_Stiering_Blacklist.IMSI
> > ORDER BY view_results_7a.BegTime DESC
> >
>
>
> I have forgot to tell you that the table tblIMSI_Stiering_Blacklist is
> from another database but on the same server, so I have to write in
> this way:
>
> ALTER VIEW view_results_7
> AS
> SELECT TOP 100 PERCENT view_results_7a.*, table.MCC,
> table.MNC, ...table.HPRP
> FROM view_results_7a INNER JOIN
> table ON view_results_7a.MCC = table.MCC
> WHERE view_results_7a.IMSI <>
> IMSI_Blacklist.dbo.tblIMSI_Stiering_Blacklist.IMSI
> ORDER BY view_results_7a.BegTime DESC
>
> The error is still the same :-(