|
|
Posted by Erland Sommarskog on 12/19/06 11:48
(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/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|