Re: Establishing Precedence In ORDERBY Condition Causing Problems.

On Jul 13, 3:04 pm, Erland Sommarskog <esq...@xxxxxxxxxxxxx> wrote:
pbd22 (dush...@xxxxxxxxx) 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...@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

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

.

Relevant Pages

  • Re: Establishing Precedence In ORDERBY Condition Causing Problems.
    ... this query is likely to return multiple rows. ... Since @sort is NULL at this point, ... since your original query had some 7-8 tables whereof several repeated ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ...
    (comp.databases.ms-sqlserver)
  • Re: finding unique values
    ... Build a totals query with just the Part# field in it twice. ... Sort and group by on the first, for the second, use count. ... part A123 could be listed in multiple rows if it is used on multiple models. ...
    (microsoft.public.access.queries)
  • Re: Make Table Query - Sorting Errors
    ... Dim OutputTable As DAO.Recordset ... The query to concatenate the CAMPNO values will need to be LOOKING at the ... If you post the query you are using to do this, I beleive we can get it to ... Is there a command that I can use in the module code to first sort the ...
    (microsoft.public.access.queries)
  • Re: Make Table Query - Sorting Errors
    ... You sort in a query, ... You can do a compound sort in a query. ... "Tom Ellison" wrote: ... resulting table only has one record for each value of "CAMPNO". ...
    (microsoft.public.access.queries)
  • Re: Query doesnt always sort
    ... You have to use a query to sort the data in to your required order and then ... Access MVP 2002-2005, 2007 ... University of Maryland Baltimore County ... How do I impose a sort on the datasheet view? ...
    (microsoft.public.access.queries)