Error using a derived table

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: James Rowland-Jones (anonymous_at_discussions.microsoft.com)
Date: 03/29/04 

Date: Mon, 29 Mar 2004 07:21:14 -0800

SQL Server is returning the following error :

Server: Msg 107, Level 16, State 2, Line 4
The column prefix 'u' does not match with a table name or alias name used in the query.

With this SQL Statement :

declare @datasetuploadid int
set @datasetuploadid = 1

select
        ( case when u.otherregion is not null
                and u.resort is not null
                        Then ( select d.regionid
                                        from ( select top 1 resort.name,max(resort.regionid)as 'regionid',count(*) as 'counter'
                                                        from ( select r.name as 'Resortname'
                                                                                                ,r.regionid as 'RegionID'
                                                                                                ,r.regiontypeid as 'RegionTypeID'
                                                                                        from dbo.region r
                                                                                        where name = u.resort
                                                                                        and r.active = 1
                                                                                        and r.isresort = 1
                                                                                        union all
                                                                                        select s.misspelledname as 'Resortname'
                                                                                                ,s.regionid as 'RegionID'
                                                                                                ,r1.regiontypeid as 'RegionTypeID'
                                                                                        from dbo.similarname s
                                                                                                                join dbo.region r1
                                                                                        on s.regionid = r1.regionid
                                                                                        where misspelledname = u.Resort
                                                                                        and r1.active = 1
                                                                                        and r1.isresort = 1
                                                                                        union all
                                                                                        select a.aliasname as 'Resortname'
                                                                                                ,a.regionid as 'RegionID'
                                                                                                ,r2.regiontypeid as 'RegionTypeID'
                                                                                        from dbo.alias a
                                                                                                                join dbo.region r2
                                                                                        on a.regionid = r2.regionid
                                                                                        where aliasname = u.Resort
                                                                                        and r2.active = 1
                                                                                        and r2.isresort = 1
                                                                                ) resort
                                                                                        join dbo.childregion cr1
                                                        on resort.regionid = cr1.childid
                                                        where cr1.parentid IN ( select r.regionid as 'RegionID'
                                                                                        from dbo.region r
                                                                                        where name = u.OtherRegion
                                                                                        and r.active = 1
                                                                                        union all
                                                                                        select s.regionid as 'RegionID'
                                                                                        from dbo.similarname s
                                                                                                                join dbo.region r1
                                                                                        on s.regionid = r1.regionid
                                                                                        where misspelledname = u.OtherRegion
                                                                                        and r1.active = 1
                                                                                        union all
                                                                                        select a.regionid as 'RegionID'
                                                                                        from dbo.alias a
                                                                                                                join dbo.region r2
                                                                                        on a.regionid = r2.regionid
                                                                                        where aliasname = u.OtherRegion
                                                                                        and r2.active = 1
                                                                                )
                                                        and resort.active = 1
                                                        and resort.isresort = 1
                                                        and resort.name = u.resort
                                                        group by resort.name,resort.regiontypeid
                                                        order by resort.regiontypeid desc
                                                ) d
                                        where d.counter = 1
                                )
                End
        )
from dbo.uploadeditem u
where u.datasetuploadid = @datasetuploadid
and u.Resort_RegionID is null
and u.statuscodeid is null

I believe the error is due to the resort derived table not being able to resolve the 'u' table alias. I have found a Q article on this subject 225492 but it was only for SQL 7. I am using SQL Server 2000 EE.

So the question is am I doing something wrong or is this a bug?

Any help would be greatly appreciated.
James

Relevant Pages

  • Re: Query doesnt always sort
    ... If the query was "within" SQL Server, then alias ... gets treated differently than in a Jet query. ...
    (microsoft.public.access.queries)
  • Re: Change the Underlying Database
    ... alias -- use this in any references to the table. ... when you want to change the query to run on another table, ... and am connected to a SQL Server 2005 back end ... I have linked a table on that SQL DB and generated several queries ...
    (microsoft.public.access.queries)
  • Re: Change the Underlying Database
    ... click on the fieldlist and, in the Properties window, define a short alias -- use this in any references to the table. ... when you want to change the query to run on another table, go to the SQL view of the query and edit Tablename in the FROM clause:) ... I have linked a table on that SQL DB and generated several queries and reports based on that table. ... a database has been put on the same SQL Server instance with a slightly different DB name. ...
    (microsoft.public.access.queries)
  • Re: General (newbie) design question about fields naming and aliases
    ... Alias in query. ... Alias in DataSet. ... In my SQL Server tables I have odd field names, ... I know it's a very stupid question, but i'm always asking me what is ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Query doesnt always sort
    ... A SQL Server view cannot be ordered unless it uses a TOP, ... ORDER BY clause or when using ODBC to linked MS SQL tables. ... I don't believe the query is from SQL2K since ... you can set an alias ...
    (microsoft.public.access.queries)