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.
| Author |
Topic |
|
cardgunner
326 Posts |
 Posted - 2007-05-17 : 10:31:56
|
| New to SQL with no formal training.I have this in my select statement:convert(varchar (8), (case when dext850.t_stat='14' then 'ON ORDER' ELSE dext850.t_stat end)) as 'Status',Which produces the following error:Syntax error converting the varchar value 'ON ORDER' to a column of data type tinyint.I spent an hour and half searching Books Online and this forum with no results, or at least results I could understand.Any help would be great.For the future any suggestions of a good book to have on my desk for a beginner with an Access background?Card Gunner |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-05-17 : 10:37:31
|
| I assume that dext850.t_stat is NOT a varchar datatype, in which case you need to convert it to Varchar for the CASE to be comparing like-with-like:convert(varchar (8), (case when dext850.t_stat='14' then 'ON ORDER' ELSE CONVERT(varchar(20), dext850.t_stat) end)) as 'Status',Kristen |
 |
|
|
cardgunner
326 Posts |
Posted - 2007-05-17 : 10:47:37
|
| That worked perfect. Thank you.Is there a correct way to have done this or what I did okay. It seems like I'm doing things because it works and I don't know if it's the "right" way.Card Gunner |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2007-05-17 : 10:58:26
|
| 1 If you are new to sql, learn ithttp://www.sql-tutorial.net/ http://www.firstsql.com/tutor.htm http://www.w3schools.com/sql/default.asp 2 You can also docase when dext850.t_stat='14' then 'ON ORDER' ELSE CONVERT(varchar(20), dext850.t_stat end) as 'Status',3 If you want to show the data inf front end, just return t_stat and when showing format itMadhivananFailing to plan is Planning to fail |
|
|
|
cardgunner
326 Posts |
Posted - 2007-05-17 : 11:09:06
|
| Thanks madhivanan. I bookmarked those links. They all seem very usefull. Any Suggestions on written material?Card Gunner |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-05-17 : 14:00:49
|
| "Is there a correct way to have done this or what I did okay"Assuming that your dext850.t_stat column is numeric (integer, float, whatever!) then:case when dext850.t_stat='14'is going to force an implicit conversion because the Left side is numeric and the Right side is a string. This is not ideal, it would be better to use:case when dext850.t_stat=14to indicate that the Right side is numeric. Furthermore, if dext850.t_stat is defined as a floating point datatype, rather than an integer, then this would be:case when dext850.t_stat = 14.0Then you are wanting to return a numeric value (dext850.t_stat) but instead return 'ON ORDER' if dext850.t_stat = 14. This is where the problem arises.You can convert dext850.t_stat to a VARCHAR (as I did above) and return the string value of dext850.t_stat or the constant string 'ON ORDER'. That satisfies what your application needs to do.However, your application can no longer treat the return value as a number.So, to answer your question, generally it is better to return the raw data to the application and "format" it in the application. That way the application gets the data in Raw form (a number in this instance, but this situation also commonly applies to Dates which usually need formatting before display) your application could perform arithmetic on the number (Round, etc.) and also format (display to 2 decimal places, right-hand-justify, etc.) AND display "ON ORDER" when appropriate.So my answer would be "Do the formatting in the application, if at all possible"Kristen |
|
|
|
|
|
|
|
|
modify data type