473,324 Members | 1,678 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

Strange behaviour in ORDER BY Statement

Hi

I've done a search and found plenty on ORDER BY problems, but mines
hopefully a syntax error.

This is a test segment from my stored procedure.

DECLARE @Name varchar(50), @SortType Int
SET @Name = "canal lock"
SET @SortType = 8
SELECT dbo.ite_Item.ite_ID
FROM dbo.ite_Item LEFT OUTER JOIN
dbo.iti_ItemImages ON
dbo.ite_Item.ite_ID = dbo.iti_ItemImages.iti_LinkToItem
WHERE iti_ItemImages.iti_LinkToPhotoType=1
AND ite_Name LIKE '%' + @Name + '%'
ORDER BY
CASE
WHEN @SortType = 3 THEN dbo.ite_Item.ite_Date
WHEN @SortType = 5 THEN dbo.ite_Item.ite_Views
WHEN @SortType = 7 THEN dbo.ite_Item.ite_Description
END ASC,
CASE
WHEN @SortType = 2 THEN dbo.ite_Item.ite_Name
WHEN @SortType = 4 THEN dbo.ite_Item.ite_Date
WHEN @SortType = 6 THEN dbo.ite_Item.ite_Views
WHEN @SortType = 8 THEN dbo.ite_Item.ite_Description
END DESC,
CASE
WHEN @SortType <> 2 THEN dbo.ite_Item.ite_Name
END ASC

I get various errors:

@SortType = 2. This is a Name field varchar(50)
Syntax error converting the varchar value 'Canal Lock' to a column of
data type smallint.

I only have one smallint col and that is the ite_Views column. Why is
it trying to convert a varchar(50) col?

@SortType = 3. This is a ISO date field nchar(8)
The conversion of the nvarchar value '20030909' overflowed an INT2
column. Use a larger integer column.

Why?

@SortType = 7, 8. These are description fields varchar(5000)
Syntax error converting the varchar value '<snip>' to a column of data
type smallint.

Again, why?

All other sort types are fine.

What I'm thinking is that when the ORDER BY code is run, it does some
conversion but what?

Many thanks in advance if anyone has any ideas?

Sam
Jul 20 '05 #1
3 4619
Samuel,

the CASE expression can only return one data type. You cannot mix data
types. So if you put sort type 3, 5 and 7 in the same CASE expression,
then all these three cases should return the same data type, otherwise
you get these types of errors.

Gert-Jan

Samuel Hon wrote:

Hi

I've done a search and found plenty on ORDER BY problems, but mines
hopefully a syntax error.

This is a test segment from my stored procedure.

DECLARE @Name varchar(50), @SortType Int
SET @Name = "canal lock"
SET @SortType = 8
SELECT dbo.ite_Item.ite_ID
FROM dbo.ite_Item LEFT OUTER JOIN
dbo.iti_ItemImages ON
dbo.ite_Item.ite_ID = dbo.iti_ItemImages.iti_LinkToItem
WHERE iti_ItemImages.iti_LinkToPhotoType=1
AND ite_Name LIKE '%' + @Name + '%'
ORDER BY
CASE
WHEN @SortType = 3 THEN dbo.ite_Item.ite_Date
WHEN @SortType = 5 THEN dbo.ite_Item.ite_Views
WHEN @SortType = 7 THEN dbo.ite_Item.ite_Description
END ASC,
CASE
WHEN @SortType = 2 THEN dbo.ite_Item.ite_Name
WHEN @SortType = 4 THEN dbo.ite_Item.ite_Date
WHEN @SortType = 6 THEN dbo.ite_Item.ite_Views
WHEN @SortType = 8 THEN dbo.ite_Item.ite_Description
END DESC,
CASE
WHEN @SortType <> 2 THEN dbo.ite_Item.ite_Name
END ASC

I get various errors:

@SortType = 2. This is a Name field varchar(50)
Syntax error converting the varchar value 'Canal Lock' to a column of
data type smallint.

I only have one smallint col and that is the ite_Views column. Why is
it trying to convert a varchar(50) col?

@SortType = 3. This is a ISO date field nchar(8)
The conversion of the nvarchar value '20030909' overflowed an INT2
column. Use a larger integer column.

Why?

@SortType = 7, 8. These are description fields varchar(5000)
Syntax error converting the varchar value '<snip>' to a column of data
type smallint.

Again, why?

All other sort types are fine.

What I'm thinking is that when the ORDER BY code is run, it does some
conversion but what?

Many thanks in advance if anyone has any ideas?

Sam

Jul 20 '05 #2
Thanks for the help!!!

Dont suppose you could recommend a website or book? I've got two
fairly simple books but neither does the trick?

Thanks
Sam

Gert-Jan Strik <so***@toomuchspamalready.nl> wrote in message news:<3F***************@toomuchspamalready.nl>...
Samuel,

the CASE expression can only return one data type. You cannot mix data
types. So if you put sort type 3, 5 and 7 in the same CASE expression,
then all these three cases should return the same data type, otherwise
you get these types of errors.

Gert-Jan

<snip>
Jul 20 '05 #3
Sorry, I don't know many (beginner) books. I have seen this trick in
this newsgroup many times, and have never seen the trick in a book...

Gert-Jan
Samuel Hon wrote:

Thanks for the help!!!

Dont suppose you could recommend a website or book? I've got two
fairly simple books but neither does the trick?

Thanks
Sam

Gert-Jan Strik <so***@toomuchspamalready.nl> wrote in message news:<3F***************@toomuchspamalready.nl>...
Samuel,

the CASE expression can only return one data type. You cannot mix data
types. So if you put sort type 3, 5 and 7 in the same CASE expression,
then all these three cases should return the same data type, otherwise
you get these types of errors.

Gert-Jan

<snip>

Jul 20 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: Neculai Macarie | last post by:
Hi! Using Union and Order By gives strange behaviour in the following test-case: drop table if exists gallery; drop table if exists gallery_categ; # create test tables create table gallery...
3
by: Bruno van Dooren | last post by:
Hi All, i have some (3) different weird pointer problems that have me stumped. i suspect that the compiler behavior is correct because gcc shows the same results. ...
6
by: Edd Dawson | last post by:
Hi. I have a strange problem involving the passing of command line arguments to a C program I'm writing. I tried posting this in comp.programming yesterday but someone kindly suggested that I'd...
10
by: tborn | last post by:
Hi there, Not sure if any one has experienced this before and can tell me what's wrong with this statement: if verified = false then dataObjects.HasError = true This is all on one line...
5
by: Praveen_db2 | last post by:
Hi All db2 8.1.3 Windows I have folowing table structures CREATE TABLE tb_RTB( EMP_ID INTEGER, DESC VARCHAR(20)); CREATE TABLE tb_ERROR( SQL_STATE CHAR(5), SQL_DESC VARCHAR(20),
23
by: g.ankush1 | last post by:
#include <stdio.h> /* 1st example int a() { return 1; }
5
by: paulo | last post by:
Can anyone please tell me how the C language interprets the following code: #include <stdio.h> int main(void) { int a = 1; int b = 10; int x = 3;
8
by: matthewperpick | last post by:
Check out this toy example that demonstrates some "strange" behaviour with keyword arguments and inheritance. ================================= class Parent: def __init__(self, ary = ):...
8
by: Dox33 | last post by:
I ran into a very strange behaviour of raw_input(). I hope somebody can tell me how to fix this. (Or is this a problem in the python source?) I will explain the problem by using 3 examples....
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
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...
1
isladogs
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...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
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...
1
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)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.