Can someone tell me why SQL seems to ignore my order by clause?
I tried to run through the debugger, but the debugger stops at the
select statement line and then returns the result set; so, I have no
idea how it is evaluating the order by clause.
THANK YOU!
CREATE proc sprAllBooks
@SortAscend varchar(4),
@SortColumn varchar(10)
as
If @SortAscend = 'DESC'
Select titles.title_id, title, au_lname, au_fname,
Convert(varchar(12), pubdate, 101) as PubDate
from authors
inner join
titleauthor
on
authors.au_id = titleauthor.au_id
inner join
titles
on
titleauthor.title_id = Titles.title_id
ORDER BY au_lname
CASE @SortColumn WHEN 'title' THEN title END,
CASE @SortColumn WHEN 'au_lname' THEN au_lname END,
CASE @SortColumn WHEN 'PubDate' THEN PubDate END
DESC
ELSE
Select titles.title_id, title, au_lname, au_fname,
Convert(varchar(12), pubdate, 101) as PubDate
from authors
inner join
titleauthor
on
authors.au_id = titleauthor.au_id
inner join
titles
on
titleauthor.title_id = Titles.title_id
ORDER BY
CASE @SortColumn WHEN 'title' THEN title END,
CASE @SortColumn WHEN 'au_lname' THEN au_lname END,
CASE @SortColumn WHEN 'PubDate' THEN PubDate END
GO 7 2727
On 25 Feb 2005 08:39:07 -0800, JJ****@hotmail.com wrote: Can someone tell me why SQL seems to ignore my order by clause? I tried to run through the debugger, but the debugger stops at the select statement line and then returns the result set; so, I have no idea how it is evaluating the order by clause. THANK YOU!
Hi JJ,
You forgot to tell us how you call the procedure (what values for
@SortAscend and @SortColumn you use) and what results you get.
I tried your code and after fixing a syntax error, it works as I would
expect it to. You apparently expected something else, but what you
expected is not clear from your post.
Some small pointers to what might be your problem:
If @SortAscend = 'DESC'
(snip)ORDER BY au_lname CASE @SortColumn WHEN 'title' THEN title END, CASE @SortColumn WHEN 'au_lname' THEN au_lname END, CASE @SortColumn WHEN 'PubDate' THEN PubDate END DESC
This resulted in an error. You either have to append a comma after ORDER
BY au_lname, or remove au_lname. I figured the latter is what you want,
so that's how I tested it.
The last CASE expression won't refer to the formatted pubdate, but to
the pubdate column in the table. Even though the ORDER BY does allow you
to refer to an alias from the SELECT clause, it does not allow you to do
so in an expression.
The DESC will only apply to the ordering by pubdate. The title and
au_lname are in seperate expressions that require a seperate DESC:
ORDER BY
CASE @SortColumn WHEN 'title' THEN title END DESC,
CASE @SortColumn WHEN 'au_lname' THEN au_lname END DESC,
CASE @SortColumn WHEN 'PubDate' THEN pubdate END DESC
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
Thank you Hugo. Good points. For debugging purposes, I've been calling
the sproc from the debugger with the parameters @SortAscend = 'DESC'
and @SortColumn = 'title. The result set is not sorted by title desc,
as I would expect it should have been. Quite inexplicably (to me), the
result set now seems to be sorting by au_lname desc (!)
Note that the order by clause should only feature the case evaluation
statement (au_lname was there as a left-over from a debugging
attempt...):
ORDER BY
CASE @SortColumn WHEN 'title' THEN title END,
CASE @SortColumn WHEN 'au_lname' THEN au_lname END,
CASE @SortColumn WHEN 'PubDate' THEN PubDate END
I think I see what you are getting at about the PubDate (alias) and
pubdate (table column) name and will give that a try...
Also, you related that one part of the code resulted in an error...can
you tell me how to unearth that? The Sql debugger seemed to be silent
on that point to me...
Thank you again...
J.
Hugo Kornelis wrote: On 25 Feb 2005 08:39:07 -0800, JJ****@hotmail.com wrote:
Can someone tell me why SQL seems to ignore my order by clause? I tried to run through the debugger, but the debugger stops at the select statement line and then returns the result set; so, I have no idea how it is evaluating the order by clause. THANK YOU! Hi JJ,
You forgot to tell us how you call the procedure (what values for @SortAscend and @SortColumn you use) and what results you get.
I tried your code and after fixing a syntax error, it works as I
would expect it to. You apparently expected something else, but what you expected is not clear from your post.
Some small pointers to what might be your problem:
If @SortAscend = 'DESC' (snip)ORDER BY au_lname CASE @SortColumn WHEN 'title' THEN title END, CASE @SortColumn WHEN 'au_lname' THEN au_lname END, CASE @SortColumn WHEN 'PubDate' THEN PubDate END DESC
This resulted in an error. You either have to append a comma after
ORDER BY au_lname, or remove au_lname. I figured the latter is what you
want, so that's how I tested it.
The last CASE expression won't refer to the formatted pubdate, but to the pubdate column in the table. Even though the ORDER BY does allow
you to refer to an alias from the SELECT clause, it does not allow you to
do so in an expression.
The DESC will only apply to the ordering by pubdate. The title and au_lname are in seperate expressions that require a seperate DESC: ORDER BY CASE @SortColumn WHEN 'title' THEN title END DESC, CASE @SortColumn WHEN 'au_lname' THEN au_lname END DESC, CASE @SortColumn WHEN 'PubDate' THEN pubdate END DESC
Best, Hugo --
(Remove _NO_ and _SPAM_ to get my e-mail address)
I figured it out. The order by clause has to look like this:
ORDER BY
CASE @SortColumn WHEN 'title' THEN title END DESC,
CASE @SortColumn WHEN 'au_lname' THEN au_lname END DESC,
CASE @SortColumn WHEN 'PubDate' THEN pubdate END DESC
I put the 'DESC' parameter inside each of the case statements and now
my result set is sorting! JJ****@hotmail.com wrote: Thank you Hugo. Good points. For debugging purposes, I've been
calling the sproc from the debugger with the parameters @SortAscend = 'DESC' and @SortColumn = 'title. The result set is not sorted by title desc, as I would expect it should have been. Quite inexplicably (to me),
the result set now seems to be sorting by au_lname desc (!)
Note that the order by clause should only feature the case evaluation statement (au_lname was there as a left-over from a debugging attempt...):
ORDER BY CASE @SortColumn WHEN 'title' THEN title END, CASE @SortColumn WHEN 'au_lname' THEN au_lname END, CASE @SortColumn WHEN 'PubDate' THEN PubDate END
I think I see what you are getting at about the PubDate (alias) and pubdate (table column) name and will give that a try...
Also, you related that one part of the code resulted in an
error...can you tell me how to unearth that? The Sql debugger seemed to be silent on that point to me...
Thank you again...
J.
Hugo Kornelis wrote: On 25 Feb 2005 08:39:07 -0800, JJ****@hotmail.com wrote:
Can someone tell me why SQL seems to ignore my order by clause? I tried to run through the debugger, but the debugger stops at the select statement line and then returns the result set; so, I have
noidea how it is evaluating the order by clause. THANK YOU! Hi JJ,
You forgot to tell us how you call the procedure (what values for @SortAscend and @SortColumn you use) and what results you get.
I tried your code and after fixing a syntax error, it works as I would expect it to. You apparently expected something else, but what you expected is not clear from your post.
Some small pointers to what might be your problem:
If @SortAscend = 'DESC' (snip)ORDER BY au_lname CASE @SortColumn WHEN 'title' THEN title END, CASE @SortColumn WHEN 'au_lname' THEN au_lname END, CASE @SortColumn WHEN 'PubDate' THEN PubDate END DESC
This resulted in an error. You either have to append a comma after ORDER BY au_lname, or remove au_lname. I figured the latter is what you want, so that's how I tested it.
The last CASE expression won't refer to the formatted pubdate, but
to the pubdate column in the table. Even though the ORDER BY does
allow you to refer to an alias from the SELECT clause, it does not allow you
to do so in an expression.
The DESC will only apply to the ordering by pubdate. The title and au_lname are in seperate expressions that require a seperate DESC: ORDER BY CASE @SortColumn WHEN 'title' THEN title END DESC, CASE @SortColumn WHEN 'au_lname' THEN au_lname END DESC, CASE @SortColumn WHEN 'PubDate' THEN pubdate END DESC
Best, Hugo --
(Remove _NO_ and _SPAM_ to get my e-mail address)
On 25 Feb 2005 09:15:39 -0800, JJ****@hotmail.com wrote: Thank you Hugo. Good points. For debugging purposes, I've been calling the sproc from the debugger with the parameters @SortAscend = 'DESC' and @SortColumn = 'title. The result set is not sorted by title desc, as I would expect it should have been. Quite inexplicably (to me), the result set now seems to be sorting by au_lname desc (!)
Note that the order by clause should only feature the case evaluation statement (au_lname was there as a left-over from a debugging attempt...):
Hi J,
I was unable to reproduce this. If I run the query you posted here
(after removing the left-over au_lname) with argument DESC and title, I
get the results in ascending order of title. Not sorted by au_lname.
The only way to get it to sort by descending au_lname is to leave the
left-over au_lname in and remove or comment the three CASE expressions.
Also, you related that one part of the code resulted in an error...can you tell me how to unearth that? The Sql debugger seemed to be silent on that point to me...
I'm talking about the code as you posted it here in your original
message (with the left-over au_lname included). I get an error when I
try to create the procedure, or when I try to run that statements by
themselves. The only ways tol solve it are to remove au_lname, to add a
comma at the end of the line or to remove the three case expressions.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
Thanks very much again Hugo. All is well now -- with the DESC keywords
within each CASE statement:
ORDER BY
CASE @SortColumn WHEN 'title' THEN title END DESC,
CASE @SortColumn WHEN 'au_lname' THEN au_lname END DESC,
CASE @SortColumn WHEN 'PubDate' THEN pubdate END DESC
I am just starting to use the debugger in SQL and therefore am *very*
interested in learning as much as I can about using it to advantage.
Again, it didn't "complain" about:
ORDER BY
CASE @SortColumn WHEN 'title' THEN title END,
CASE @SortColumn WHEN 'au_lname' THEN au_lname END ,
CASE @SortColumn WHEN 'PubDate' THEN pubdate END
DESC
but, nor did the stored procedure return the intended result set!
I have used QA and the Profiler exclusively to help debug problems.
Jules
On 25 Feb 2005 13:00:22 -0800, JJ****@hotmail.com wrote:
(snip) Again, it didn't "complain" about:
ORDER BY CASE @SortColumn WHEN 'title' THEN title END, CASE @SortColumn WHEN 'au_lname' THEN au_lname END , CASE @SortColumn WHEN 'PubDate' THEN pubdate END DESC
but, nor did the stored procedure return the intended result set!
Hi Jules,
It should not complain about this - it's valid T-SQL syntax. It
specifies that the results should be ordered by three columns:
* first by (depending on the value of @SortColumn) either title or NULL,
ascending (since no direction is specified);
* next by (depending on the value of @SortColumn) either au_lname or
NULL, again ascending;
* and finally, if the previous two didn't suffice to define the sort
order, by (depending on @SortColumn) either pubdate or NULL, but for
this column a descending sort is specified.
I have used QA and the Profiler exclusively to help debug problems.
Those tools are the best (IMO), both for writing and debugging SQL code.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
hmm...I understand what you are saying...interesting
it makes sense now - thanks!
Hugo Kornelis wrote: On 25 Feb 2005 13:00:22 -0800, JJ****@hotmail.com wrote:
(snip)Again, it didn't "complain" about:
ORDER BY CASE @SortColumn WHEN 'title' THEN title END, CASE @SortColumn WHEN 'au_lname' THEN au_lname END , CASE @SortColumn WHEN 'PubDate' THEN pubdate END DESC
but, nor did the stored procedure return the intended result set! Hi Jules,
It should not complain about this - it's valid T-SQL syntax. It specifies that the results should be ordered by three columns: * first by (depending on the value of @SortColumn) either title or
NULL, ascending (since no direction is specified); * next by (depending on the value of @SortColumn) either au_lname or NULL, again ascending; * and finally, if the previous two didn't suffice to define the sort order, by (depending on @SortColumn) either pubdate or NULL, but for this column a descending sort is specified.
I have used QA and the Profiler exclusively to help debug problems. Those tools are the best (IMO), both for writing and debugging SQL
code. Best, Hugo --
(Remove _NO_ and _SPAM_ to get my e-mail address) This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Peroq |
last post by:
Hi all
I'm not sure ASP is the problem, but my SQL statement seems fine to me.
This works fine :
strSQL = "SELECT .* FROM _RechPat INNER JOIN NivPatri ON
.cod_niv = NivPatri.cod_niv WHERE...
|
by: Scott |
last post by:
I have a view that contains the following SQL1 and it takes about 500ms
to run with Explain Plan EXP1. However, if I add an order by
clause(e.g. select * from view1 order by ID desc), it will then...
|
by: gary b |
last post by:
Hello
When I use a PreparedStatement (in jdbc) with the following query:
SELECT store_groups_id
FROM store_groups
WHERE store_groups_id IS NOT NULL
AND type = ?
ORDER BY group_name
|
by: Neo-LISPer |
last post by:
Hey
Recently, I researched using C++ for game programming and here is what
I found:
C++ game developers spend a lot of their time debugging corrupted
memory. Few, if any, compilers offer...
|
by: deko |
last post by:
I'm trying to open a Recordset based on a parameterized query. I'm kind of
new to parameterized queries, so I'm sure I'm missing something simple.
Set qdfs = db.QueryDefs
Set qdf =...
|
by: bevanward |
last post by:
Hi All
I am finding unexpected results when inserted into a newly created
table that has a field of datatype int identity (1,1).
Basically the order I sort on when inserting into the table is...
|
by: rdemyan via AccessMonster.com |
last post by:
I can't seem to get the following SQL statement with the ORDER BY clause to
work. It does work if I order by any of the fields in the SELECT part of the
statement but not if it is the field in the...
|
by: Roland Hall |
last post by:
In Access you use "*" + + "*", + can be replaced with &
Calling a parameterized query in Access requires % be used in place of *,
however, all that I have read show dynamic SQL passed to Access:
...
|
by: Paul Elliott |
last post by:
How are parameterized translation strings commonly handled?
Suppose I need to create a string like:
"file %1 failed to open", where %1 exists at runtime, but I need to
create it in a way that...
|
by: DolphinDB |
last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation.
Take...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: ArrayDB |
last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
|
by: PapaRatzi |
last post by:
Hello,
I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
| |