Re: Establishing Precedence In ORDERBY Condition Causing Problems.

pbd22 (dushkin@xxxxxxxxx) 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@xxxxxxxxxxxxx

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
.

Relevant Pages

  • Re: Establishing Precedence In ORDERBY Condition Causing Problems.
    ... in the query, prefix all your columns with aliases (or the table ... in the query where you use the CTE: ... of the procedure to avoid duplicates it seems to have thrown off the ...
    (comp.databases.ms-sqlserver)
  • Re: split records
    ... A UNION query is constructed by writing a set of SELECT queries, ... The words UNION or UNION ALL are then placed between the SELECT ... be duplicates, or when you want to preserve duplications, use ALL. ... constraints). ...
    (microsoft.public.access.queries)
  • Re: question about a query
    ... JOIN QuesNans AS B ON A.topicid = B.topicid ... CROSS JOIN (SELECT 1 AS n UNION ALL ... This is a crosstab query. ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ...
    (comp.databases.ms-sqlserver)
  • Re: Error using a derived table
    ... to re-write your query without all of the nested SELECTs and the CASE ... > union all ... > select s.misspelledname as 'Resortname' ... I am using SQL Server 2000 EE. ...
    (microsoft.public.sqlserver.programming)
  • Re: Indexes being improperly used when selecting data through a view
    ... I run the same query against the table directly and it looks ... What happens if you actually have the UNION ALL, ... With UNION SQL Server will have to do an operation ... Books Online for SQL Server 2005 at ...
    (comp.databases.ms-sqlserver)