Re: Error using a derived table
From: Adam Machanic (amachanic_at_air-worldwide.nospamallowed.com)
Date: 03/29/04
- Next message: Chris: "Error assigning result to variable"
- Previous message: Adam Machanic: "Re: Table has identity field"
- In reply to: James Rowland-Jones: "Error using a derived table"
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 29 Mar 2004 12:59:23 -0500
The problem is your CASE expression; it doesn't evaluate to anything. Try
to re-write your query without all of the nested SELECTs and the CASE
statement and instead put your filtration logic into a WHERE clause.
"James Rowland-Jones" <anonymous@discussions.microsoft.com> wrote in message
news:71BB5719-9C91-4CE4-9435-9863B17CF44B@microsoft.com...
> 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
- Next message: Chris: "Error assigning result to variable"
- Previous message: Adam Machanic: "Re: Table has identity field"
- In reply to: James Rowland-Jones: "Error using a derived table"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
