Order By problem with CASE (Data Type different)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Order By problem with CASE (Data Type different)

Author

Topic

calvinfoo
Posting Yak Master

129 Posts

Posted - 2011-04-01 : 04:44:55

Dear Gurus

Errors when I run the SP. it was working alright, until I made some changes to the ORDER BY cause... and problem when @OrderBy = 2
I got this msg

Msg 8115, Level 16, State 6, Procedure SP_PR_Details_Report, Line 29
Arithmetic overflow error converting nvarchar to data type numeric.

CASE
WHEN @OrderBy = 1 THEN PR_num --nvarchar(66)
WHEN @OrderBy = 2 THEN item -- nvarchar(128)
WHEN @OrderBy = 3 THEN TotalPrice --decimal(38, 6)
WHEN @OrderBy = 4 THEN CAST(DeliveryDate AS VARCHAR(50)) --Datetime
-- more OrderBy options... but all are nvarchar...
END

I don't understand as I didn't do any converting

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-04-01 : 04:49:27

if you have various data type you will need to convert one to another, the best option is to convert to string data type (ie. varchar)

"WHEN @OrderBy = 4 THEN CAST(DeliveryDate AS VARCHAR(50)) --Datetime"
if you need to convert a datetime to string, use convert() with specific style code like 112 or 121 else your ORDERing might be not what you intended

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-04-01 : 04:52:11

alternatively use Dynamic SQL to do it

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-04-01 : 04:53:51

ok. Another trick is use multiple case statement. One case statement for each of your @OrderBy option


ORDER BY
CASE WHEN @OrderBy = 1 THEN PR_num END,
CASE WHEN @OrderBy = 2 THEN item END,
CASE WHEN @OrderBy = 3 THEN TotalPrice END,
. . . .

KH
[spoiler]Time is always against us[/spoiler]

calvinfoo
Posting Yak Master

129 Posts

Posted - 2011-04-01 : 05:02:44

Ok, you mean I have to put CAST everything after THEN?

calvinfoo
Posting Yak Master

129 Posts

Posted - 2011-04-01 : 05:08:00

Ok. I think
CASE WHEN @OrderBy = 1 THEN PR_num END,
CASE WHEN @OrderBy = 2 THEN item END,
CASE WHEN @OrderBy = 3 THEN TotalPrice END,

is easier for me to digest, will use this method

calvinfoo
Posting Yak Master

129 Posts

Posted - 2011-04-01 : 05:15:11

I tried this, but it gives me error on first line:
Msg 102, Level 15, State 1, Procedure SP_PR_Details_Report, Line 44
Incorrect syntax near ','.

ORDER BY
CASE
WHEN @DESC <> 1 THEN
CASE WHEN @OrderBy = 1 THEN PR_num END, --This is line 44
CASE WHEN @OrderBy = 2 THEN item END,
END ASC

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-04-01 : 05:15:56

quote:
Originally posted by calvinfoo

Ok, you mean I have to put CAST everything after THEN?

Yes. For the method that you used in your original query. You have to convert() all non-string data type to string

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-04-01 : 05:27:03

quote:
Originally posted by calvinfoo

I tried this, but it gives me error on first line:
Msg 102, Level 15, State 1, Procedure SP_PR_Details_Report, Line 44
Incorrect syntax near ','.

ORDER BY
CASE
WHEN @DESC <> 1 THEN
CASE WHEN @OrderBy = 1 THEN PR_num END, --This is line 44
CASE WHEN @OrderBy = 2 THEN item END,
END ASC

You still got the case statement syntax wrong. Case statement can return only 1 value. In this statement you are returning 2 value when @DESC <> 1

In your case, you have 2 variable that will determine the ORDERing sequence, using dynamic SQL will probably be easier. Using case statement to do it will be a bit complicated.

Here is what you can do with case statement

for numeric data type like TotalPrice, you can do this. You multiply the value by -1 if you want descending order. For ascending order just multiply by 1.


ORDER BY 
case when @OrderBy = 3 THEN TotalPrice END * Case when @desc = 1 then -1 else 1 end
. . .

for string or datetype data type like PR_num


case when @OrderBy = 1 and @Desc <> 1 THEN PR_num END,
case when @OrderBy = 1 and @Desc =  1 THEN PR_num END DESC,
. . .

So for each @OrderBy option, you will required 2 case statement.

Good Luck

KH
[spoiler]Time is always against us[/spoiler]

calvinfoo
Posting Yak Master

129 Posts

Posted - 2011-04-01 : 05:29:13

If I were do this, all OK, except the Price:

Msg 8115, Level 16, State 5, Procedure SP_PR_Details_Report, Line 29
Arithmetic overflow error converting numeric to data type varchar.

ORDER BY
CASE
WHEN @DESC <> 1 THEN
CASE
WHEN @OrderBy = 1 THEN CONVERT(char(10), PR_headerID)
WHEN @OrderBy = 2 THEN CONVERT(char(10), item)
WHEN @OrderBy = 3 THEN CONVERT(char(10), TotalPrice) WHEN @OrderBy = 4 THEN CONVERT(char(10), itemDelivery_Date, 120)
END
END

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-04-01 : 05:37:53

"Msg 8115, Level 16, State 5, Procedure SP_PR_Details_Report, Line 29
Arithmetic overflow error converting numeric to data type varchar."
what is the data type size for TotalPrice ? increase to char(10) to like 20 or 30

KH
[spoiler]Time is always against us[/spoiler]

calvinfoo
Posting Yak Master

129 Posts

Posted - 2011-04-01 : 05:53:31

I see!
it is actually Decimal(38,6)
and I changed to char(20) and it works, but the sorting became char sorting 1, 10, 100, 2, 20, 200 etc..

my instict tells me that I should use RIGHT('0000000000000000'+TotalPrice, 10), but somehow it doesn't quite make sense to me if I would do to sort the Total price...

also, it leads to another question... I have Curency code in my data, MYR, SGD, THB etc... which can't be sorted based on value alone, I have to sort by CurrencyCode, Price. But problem I understand from you, CASE can't return two value like as if in ASP > "THEN CurrencyCode, Price END". What's your advice?

calvinfoo
Posting Yak Master

129 Posts

Posted - 2011-04-01 : 05:57:41

argh... I think to keep it simple... I'll try your earlier suggestion first and see how it goes...
case when @OrderBy = 1 and @Desc <> 1 THEN PR_num END,
case when @OrderBy = 1 and @Desc = 1 THEN PR_num END DESC,

calvinfoo
Posting Yak Master

129 Posts

Posted - 2011-04-01 : 06:06:21

Yes... it works perfectly with individual CASEs... coding seems to be a little repeatative... well... who cares, as long as it is simple and problem free.

THANKS!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources