Re: Establishing Precedence In ORDERBY Condition Causing Problems.

On Jul 18, 3:06 pm, Hugo Kornelis
<h...@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
On Sun, 15 Jul 2007 18:33:19 -0700, pbd22 wrote:

(snip)



OK, I have done what you said and reduced the tables used in the
search.
After much messing around with the stored procedure, I have figured
out that
by commenting out the following code (at the end of the procedure), I
can get
results:

WHERE

tab1.gender = @gender
AND tab1.country = @country
AND tab1.bday_year BETWEEN @low AND @high
AND tab2.photo_default = 1 + @photo--WHERE

(and, the ORDERBY code is commented out as it depends on this code).

I have also found that if I leave any one of the above lines the code
again
fails. So, for some reason, @gender, @country, @low, @high, and @photo
are not getting passed appropriately.

Hi Peter,

I found the stored procedure code in an earlier message in this thread.
I don't know how much you changed, so the following might or might not
apply.

Your query uses a lot of left (outer) joins. Are you sure that these
can't be inner joins?

The tab1 and tab2 tables are among the tables that are outer joined. By
adding a criterium in the WHERE clause, you effectively convert them to
inner joins - so you should either modify the query to use inner join
(improved readability and maintainability and probably better
performance as well), or move the filters to the ON part of the
appropriate JOIN clauses.

Note that I did not do a complete review of your code; it's too long for
that. Try to trim down the problem to a more simplified case that's
short enough for us to invest our time in, yet similar enough to your
real problem that you can translate our soultions back to your original
situation. If you're not able to simplify the problem, you should
probably hire a SQL developer to aide you with this issue.

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog:http://sqlblog.com/blogs/hugo_kornelis


Thanks Hugo and Erland (again).

Erland - thank you for your suggestions. The revising of the SET
statement
to a SELECT statement is an obvious time-saver. I don't want you to
think
I haven't tried to test my code - I was trying but every time I tried
to run it to completion I was getting errors that prevented a clean
compile. I have since
isolated some of my problems and redesigned my procedure with more
success.
Per Hugo's suggestion, I have cut a lot of the BS out of the code and
left a single join block as the core of the procedure - much more
logical (to me) and easier on the eyes.

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

Hugo - I'll try to digest your idea behind changing the join block to
inner joins later tonight. By this, do you mean replacing all "LEFT
JOIN" statements with
"INNER JOIN"?

Otherwise, I hope the updated procedure makes more logical sense.

Thanks again for your tremendous help.
Peter

ALTER PROCEDURE [dbo].[tre_SavedSearch]
@searchname VARCHAR(50) = null, -- The Name Of The User-Defined
Search
@emailaddy VARCHAR(50) = null, -- The ID (email) of the User
@PageNum INT = 1, -- The Starting Page
@PageSize INT = 10, -- The Number of Rows Per Page
@debug INT = 0 -- Debug Value

AS
BEGIN

SET NOCOUNT ON

DECLARE
@saveddate VARCHAR(50),
@savedname VARCHAR(50),
@defaultsearch VARCHAR(50),
@gender VARCHAR(50),
@fromage VARCHAR(50),
@toage VARCHAR(50),
@country VARCHAR(50),
@miles VARCHAR(50),
@pictures VARCHAR(50),
@zipcode VARCHAR(50),
@whereSQL VARCHAR(1000),
@sortID INT -- 1 = registration
-- 2 = recent changes
-- 3 = recent login
-- 4 = distance order

SELECT @saveddate = saved_date, @savedname = saved_name, @gender =
saved_sex,
@fromage = saved_fage, @toage = saved_tage, @country =
saved_country , @miles = saved_miles,
@pictures = saved_pictures, @zipcode = saved_postal, @sortID =
saved_sort
FROM SavedSearches
WHERE saved_name=@searchname
AND email_address=@emailaddy

WITH SavedSearch AS
(

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
,tab1.bday_day
,tab1.bday_month
,tab1.bday_year
,tab1.gender
,tab1.zipCode
,tab1.siteId
,tab1.userID
--,tab5.up_order
FROM
(select distinct emailAddress
from Users with(nolock) union select distinct user_name
from PersonalPhotos with(nolock) union select distinct email_address
from EditProfile with(nolock) union select distinct email_address
from SavedSearches with(nolock) union select distinct email_address
from UserPrecedence with(nolock) union select distinct email_address
from LastLogin with(nolock)) drv
Left Join Users tab1 on (drv.emailAddress = tab1.emailAddress)
--Inner Join UserPrecedence tab5 on tab5.UserID=tab1.UserID
Left Join PersonalPhotos tab2 on (drv.emailAddress = tab2.user_name)
Left Join LastLogin tab4 on (drv.emailAddress = tab4.email_address)
Left Join EditProfile tab3 on (drv.emailAddress = tab3.email_address)
Left Join SavedSearches tab6 on (drv.emailAddress =
tab6.email_address)

WHERE
(tab1.gender = @gender OR CASE WHEN COALESCE(@gender,'Show All') =
'Show All' THEN 1 ELSE 0 END = 1)
AND
(tab1.country = @country OR CASE WHEN COALESCE(@country,'Show All') =
'Show All' THEN 1 ELSE 0 END = 1)
AND
(tab1.bday_year BETWEEN @toage AND @fromage)
--AND tab2.photo_default = 1 + @photo
)

SELECT registerDate
--,tab3.edit_date
,login_date
,bday_day
,bday_month
,bday_year
,gender
--,up_order
,zipCode
,siteId
,userID
FROM (SELECT DISTINCT registerDate
--,tab3.edit_date
,login_date
,bday_day
,bday_month
,bday_year
,gender
--,up_order
,zipCode
,siteId
,userID
FROM SavedSearch
WHERE RowNum BETWEEN (@PageNum - 1) * @PageSize + 1
AND @PageNum * @PageSize) V
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;
END

.