Posted by Erland Sommarskog on 07/20/07 09:17

pbd22 (dushkin@gmail.com) writes:
> The below procedure seems to work except for one major error and a
> minor one:
>
> major: when I simply leave the edit_date column as is, I get the
> error:
>
> "ambiguous column name edit_date"
>
> and, when I include the alias with the edit_date column, I get the
> following:
>
> "The multi-part identifier "tab3.edit_date" could not be bound."

The problem is that when inlucde the alias you do it all over town.
Don't do that. With in the CTE you should do it:

SELECT ROW_NUMBER() OVER (ORDER BY CASE @sortID
WHEN 1 THEN registerDate
--WHEN 2 THEN tab3.edit_date
WHEN 3 THEN login_date
--WHEN 4 THEN up_order
END DESC) AS RowNum,
tab1.registerDate, tab3.edit_date ,tab4.login_date,

And a more general comment, as soon as more than one table is included
in the query, prefix all your columns with aliases (or the table
name). That makes the query easier to follow for an outsider, and
also saves you from accidents if you would add, say, an up_order
column to some other table later on.

However, in the query where you use the CTE:

(SELECT DISTINCT registerDate
--,tab3.edit_date
,login_date
,bday_day
,bday_month
,bday_year
,gender
--,up_order
,zipCode
,siteId
,userID
FROM SavedSearch

You cannot use tab3, because it is not visible at this point. It's
private to the CTE. And since this is a one-table query, there is no
need to use aliases, although it would not be wrong to do so. But then
it would be like:

(SELECT DISTINCT ss.registerDate
,ss.edit_date
,ss.login_date
...
FROM SavedSearch ss


I noticed another issue:

(select distinct emailAddress from Users
union
select distinct user_name from PersonalPhotos
union
select distinct email_address from EditProfile
union
select distinct email_address from SavedSearches
union
select distinct email_address from UserPrecedence
union
select distinct email_address from LastLogin ) d

First a minor point: As you see I have removed the locking hints. I only
did so, to get less noise. But I recommend that you leave out all hints,
until you have your query working. That helps you to focus on the
essentials.

Then a little bigger point: you can remove the DISTINCT, as UNION
implies DISTINCT. (Use UNION ALL to retain duplicates.)

But the major point is that this just feels wrong. I can't really
say what it is right, because I don't know your tables. But it smells
like an error in the database design. All I can say is that you should
not have to do that.

> The lesser problem is that when I added SELECT DISTINCT at the bottom
> of the procedure to avoid duplicates it seems to have thrown off the
> paging. I have designated 10 rows as a default page parameter.
> This worked well before I changed the bottom select statement to
> eliminate duplicates.

In my experience an urge to add DISTINCT is a token of that the
query is not written in the best way, or that the data model is
problematic. As an explanation of the first, maybe there is a JOIN
that should have been a WHERE EXISTS instead.



--
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

[Back to original message]