| Author |
Topic |
|
rosshughes
Starting Member
16 Posts |
 Posted - 2011-07-12 : 16:40:35
|
| Hi All,I am having trouble getting the following to work.opqtycompleted int,standardhrs varchar(50),laborstd varchar(50)I insert values in to this memory table and all works fine. While I extract data from the memory table, I have one field in a query that looks like this:CAST((CASE WHEN CAST(IsNull(standardhrs,0) as Int) <> 0 OR IsNull(opqtycompleted,0) <> 0 THEN 1/(standardhrs/opqtycompleted) ELSE standardhrs END) As varchar(50)) As laborstdWhen this conditional field is included in the query I get the following error:"Conversion failed when converting the varchar value '8.63' to data type int."The laborstd field is varchar and I am converting the resulting CASE statement results to varchar. What am I missing? Where is the int coming from?Thanks,Ross |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
rosshughes
Starting Member
16 Posts |
Posted - 2011-07-12 : 16:51:25
|
| Thanks for the quick response.I did try it the other way at first and didn't have any luck.I tried this again:CAST((CASE WHEN IsNull(standardhrs,0) <> 0 OR IsNull(opqtycompleted,0) <> 0 THEN 1/(standardhrs/opqtycompleted) ELSE standardhrs END) As varchar(50)) As laborstd...and got the same result. Unless you had something else in mind?Thanks Tara,Ross |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
rosshughes
Starting Member
16 Posts |
Posted - 2011-07-12 : 17:46:36
|
| Thanks. I did that too and then it gives me a "can't divide by a varchar" error.It has me puzzled. Is there something implicit that converts datatypes because I am dividing into 1? I tried to CAST that too and can't get it to work.I will keep working at it.Thanks,Ross |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2011-07-12 : 18:15:51
|
| Your standard hours may not be ints. They may actually be like 1.23 or .7. You'll need to convert standardhours to a proper type -- numeric(p,s) as Tara suggested -- everywhere it is used in your query. Also, each part of your case statment needs to return the same data type throughout. The THEN part of your case expression returns an int, but the THEN part returns a varchar(50).JimEveryday I learn something that somebody else already knew |
|
|
|
rosshughes
Starting Member
16 Posts |
Posted - 2011-07-13 : 09:39:11
|
| Thank you both for your help. I went back to my declarations and started with everything numeric and then cast back to varchar at the very end. That solved it.Ross |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
Conversion Failed varchar to int
Tara Kizer