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 |
|
Grofit
Starting Member
11 Posts |
 Posted - 2009-06-14 : 10:21:54
|
Hey,As part of the view im writing i need to be able to do a case to work out which tables to look at, but it seems to keep trying to cast my results as ints even though i havent specified it anywhere... here is an example of my query:SELECT (CASE dbo.bonuses.associated_type_id WHEN 1 THEN (SELECT name FROM products WHERE id = dbo.bonuses.associated_id) WHEN 2 THEN (SELECT name FROM store WHERE id = dbo.bonuses.associated_id) END) AS associated_name FROM bonuses and i keep getting the error:"Conversion failed when converting the nvarchar value 'Generic Product' to data type int."Do i need to do a cast each time? or something, as i always want the case results to be a varchar... |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2009-06-14 : 10:37:54
|
[code]select associated_name = coalesce(p.name, s.name)from bonuses b left join products p on b.associated_id = p.id and b.associated_type_id = 1 left join store s on b.associated_id = s.id and b.associated_type_id = 2[/code]
KH[spoiler]Time is always against us[/spoiler] |
 |
|
|
Grofit
Starting Member
11 Posts |
Posted - 2009-06-14 : 11:14:51
|
| Any sort of explanation to go with that nugget?Im going to have about 10+ cases, so would it ignore the joins if it doesnt match the criteria, also is it going to pull back the entire table for the match it gets, as isnt that a little inefficient? Also im still a bit puzzled as to why it is trying to convert it to an int, i can understand that it wont really know what the result type is until it runs through the case and pulls back the data, but is there a way i can just case it? This statement is ripped from a MySQL system we are converting over, its alot more complex than the example but the scenario is pretty much the same and it works great in there... |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-06-14 : 14:42:06
|
quote:
Originally posted by Grofit
Any sort of explanation to go with that nugget?Im going to have about 10+ cases, so would it ignore the joins if it doesnt match the criteria, also is it going to pull back the entire table for the match it gets, as isnt that a little inefficient? Also im still a bit puzzled as to why it is trying to convert it to an int, i can understand that it wont really know what the result type is until it runs through the case and pulls back the data, but is there a way i can just case it? This statement is ripped from a MySQL system we are converting over, its alot more complex than the example but the scenario is pretty much the same and it works great in there...
left join will bring back everything from left table irrespective of match on right side with matching value if found or null values if no match.Case always assumes return type to be return type of first condition field. |
|
|
|
|
|
|
|
|
Case statement problem, keeps casting to int