|
|
Posted by pbd22 on 07/18/07 17:10
On Jul 15, 6:33 pm, pbd22 <dush...@gmail.com> wrote:
> On Jul 13, 3:04 pm, Erland Sommarskog <esq...@sommarskog.se> wrote:
>
>
>
> > pbd22 (dush...@gmail.com) writes:
> > >> 1) and 3) makes perfect sense. The second I cannot understand. As far
> > >> as I understand, this query is likely to return multiple rows. But which
> > >> rows it returns - we don't know. Since @sort is NULL at this point,
> > >> the ORDER BY has no effect. It's probably the explanation to why your
> > >> @sort goes bad, but I can't say what you should do to correct, because
> > >> I have very little clue how your tables are related.
>
> > > It sounds to me like the middle code block is causing me my errors but
> > > I am not sure what I am doing wrong still. Would you mind taking a look
> > > at an Entity Relationship Diagram? It might give you a better
> > > understanding of how my data is designed and for what purpose. If that
> > > is OK, I'll email it to you via your address provided here.
>
> > And I don't know what you are doing wrong, because I don't know what you
> > are trying to achieve.
>
> > There is a common recommendation for this type of questions, and that is
> > that you post:
>
> > o CREATE TABLE statements for your tables.
> > o INSERT statements with sample data.
> > o The resired result given the sample.
>
> > Now, since your original query had some 7-8 tables whereof several repeated
> > in the FROM clause, you will need to simplify the problem down to the
> > core.
>
> > If I understand this correctly, this is about saved searches, so the
> > clou is certainly SavedSearches, but try to invent a similar case with
> > fewer tables. Yes, that may take you some time, but I rather have
> > you doing that than showing me an E-R diagramme that may not help me
> > to understand what you are trying to achieve. To wit, I am not sure
> > that you understand yourself. But if you spend some time with a simpler
> > case then maybe you get can get that understanding.
>
> > --
> > 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
>
> Thanks Erland.
>
> 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.
>
> This is where I am at the moment, I'll report back as progress is
> made.
> Comments always appreciated (if you see something I don't) along the
> way.
>
> Thanks again for your patience.
> Peter
Hi Erland (or anybody else),
OK. I have changed the procedure significantly to use the Row_Number()
method in SQL 2005 for paging.
In this procedure, I am trying to do the following:
1) used the passed-in parameters to figure out which saved search we
are using.
2) query the SavedSearch table to populate the local parameters with
the saved values
3) create a temporary table that is sorted against the local
paramerters.
I am having problems figuring out how to create this temporary table.
At a quick glance, does the "SELECT COALESCE" statement seem
like it has been logically placed or does it seem out of place? I keep
getting
"Msg 102, Level 15, State 1, Line 1 Incorrect syntax near ')'. "
I can't seem to build the temp table without errors.
At a quick glance does the logic in this procedure seem to make sense?
Any "trained-eye" corrections would be very helpful.
thanks.
CREATE PROCEDURE tre_SavedSearch
-- passed-in parameters
@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
-- first, we need to pull the saved values from the
-- SavedSearch table to understand what we are looking
-- for.
DECLARE
@saveddate VARCHAR(50),
@savedname VARCHAR(50),
@defaultsearch VARCHAR(50),
@sex VARCHAR(50),
@fromage VARCHAR(50),
@toage VARCHAR(50),
@country VARCHAR(50),
@miles VARCHAR(50),
@pictures VARCHAR(50),
@zipcode VARCHAR(50),
@sortID INT -- 1 = registration
-- 2 = recent changes
-- 3 = recent login
-- 4 = distance order
SET @saveddate = (SELECT saved_date FROM SavedSearches WHERE
search_name=@searchname AND email_address=@emailaddy)
SET @savedname = (SELECT saved_name FROM SavedSearches WHERE
search_name=@searchname AND email_address=@emailaddy)
SET @sex = (SELECT saved_sex FROM SavedSearches WHERE
search_name=@searchname AND email_address=@emailaddy)
SET @fromage = (SELECT saved_fage FROM SavedSearches WHERE
search_name=@searchname AND email_address=@emailaddy)
SET @toage = (SELECT saved_tage FROM SavedSearches WHERE
search_name=@searchname AND email_address=@emailaddy)
SET @country = (SELECT saved_country FROM SavedSearches WHERE
search_name=@searchname AND email_address=@emailaddy)
SET @miles = (SELECT saved_miles FROM SavedSearches WHERE
search_name=@searchname AND email_address=@emailaddy)
SET @pictures = (SELECT saved_pictures FROM SavedSearches WHERE
search_name=@searchname AND email_address=@emailaddy)
SET @zipcode = (SELECT saved_postal FROM SavedSearches WHERE
search_name=@searchname AND email_address=@emailaddy)
SET @sortID = (SELECT saved_sort FROM SavedSearches WHERE
search_name=@searchname AND email_address=@emailaddy)
WITH SavedSearch AS
(
SELECT ROW_NUMBER() OVER ( ORDER BY CASE @sortID
WHEN 1 THEN tab1.registerDate
WHEN 2 THEN tab3.edit_date
WHEN 3 THEN tab4.login_date
WHEN 4 THEN tab5.up_order
END DESC
) AS RowNum
FROM (
SELECT COALESCE
(
tab1.emailAddress,
tab2.user_name,
tab3.email_address,
tab4.email_address,
tab5.email_address,
tab6.email_address
)
id ,
tab1.bday_day ,
tab1.bday_month ,
tab1.bday_year ,
tab1.gender ,
tab1.zipCode ,
tab1.siteId ,
tab1.userID ,
tab2.photo_location ,
tab2.photo_name ,
tab2.photo_default ,
tab2.no_photo ,
tab3.headline ,
tab3.about_me ,
tab4.login_date ,
tab4.login_isonline
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 = @sex
AND tab1.country = @country
AND tab1.bday_year BETWEEN @fromage AND @toage
--AND tab2.photo_default = 1 + @photo
)
)
SELECT * FROM SavedSearch
WHERE RowNum BETWEEN (@PageNum - 1) * @PageSize + 1
AND @PageNum * @PageSize
ORDER BY CASE @sortID
WHEN 1 THEN tab1.registerDate
WHEN 2 THEN tab3.edit_date
WHEN 3 THEN tab4.login_date
WHEN 4 THEN tab5.up_order
END DESC
END
GO
Navigation:
[Reply to this message]
|