Re: Establishing Precedence In ORDERBY Condition Causing Problems.

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
.