|
|
Posted by pbd22 on 07/20/07 19:36
On Jul 20, 2:17 am, Erland Sommarskog <esq...@sommarskog.se> wrote:
> pbd22 (dush...@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, esq...@sommarskog.se
>
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Hi Erland,
Thanks again. Your advice did the job with the alias problem.
It turns out the SELECT DISTINCT issue was a bigger problem.
I am wondering if we are talking about the same "SELECT DISTINCT"?
I wasn't referring to the DISTINCT naming in the JOIN/UNION block, but
the SELECT DISTINCT at the bottom of the stored procedure:
FROM (SELECT DISTINCT registerDate
--,tab3.edit_date
,login_date
[snip]
,userID
FROM SavedSearch
WHERE RowNum BETWEEN (@PageNum - 1) * @PageSize + 1
AND @PageNum * @PageSize) V
ORDER BY CASE @sortID ... [snip]
without the above select distinct, there is a pretty bad duplicate
problem in the results. The problem is that the above seems to throw
off paging. When I remove the SELECT DISTINCT from the above, the
paging problem is solved but the duplicate problem is back.
If the above was the SELECT DISTINCT you meant, then sorry for
misreading. I
will continue to try to work out why the duplicates are happening in
the stored procedure logic.
Navigation:
[Reply to this message]
|