Re: Establishing Precedence In ORDERBY Condition Causing Problems.

On Jul 20, 2:17 am, Erland Sommarskog <esq...@xxxxxxxxxxxxx> wrote:
pbd22 (dush...@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, esq...@xxxxxxxxxxxxx

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.

.

Relevant Pages

  • 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: Establishing Precedence In ORDERBY Condition Causing Problems.
    ... in the query, prefix all your columns with aliases (or the table ... of the procedure to avoid duplicates it seems to have thrown off the ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ...
    (comp.databases.ms-sqlserver)
  • Re: Multi-Level GROUP By Clause is not allowed in subquery
    ... It should work with UNION, ... If there can not be any duplicates in your original ... Note that the WHERE clause in the silly Select query should ... MS Access lets me create the report using the query. ...
    (microsoft.public.access.reports)
  • Re: Removing duplicates from query, but not from table
    ... Removing exact, total duplicates from a UNION is automatic as long as it's ... > SQL query - leaving one of the records behind of course. ...
    (microsoft.public.access.queries)
  • Re: Combining records from two queries
    ... combining UNION and SELECT queries to best effect. ... The list in each case has to have the same number & types of fields (for example, if the first Query begins with a Date/Time field, the second one should do so as well). ... I like to keep my Union Queries short and simple and do the rest of the work elsewhere. ... tblMentors comprises Subject Mentors and Professional Mentors and the Placement subform has a combo for each - the Subject Mentor combo puts the chosen MentorID in the SubjectMentorID field and the Professional Mentor combo puts the chosen MentorID in the ProfessionalMentorID field. ...
    (microsoft.public.access.queries)