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 |
|
hyefive
Starting Member
16 Posts |
 Posted - 2005-03-04 : 15:28:39
|
| Hi,I have identical tables in two different MS SQL Server databases that are behaving differently. Querying the table in db1 with the following works fine, but not in db2.SELECT * FROM TABLE1 WHERE ST IN ('CALIFORNIA') AND (NAME IN ('LOS ANGELES') OR VALUE1 IN (2619)) ORDER BY NAMEHowever, if I replace (2619) with ('2619'), the query IS successful in db2.Thanks in advance!hyefive |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2005-03-04 : 15:32:34
|
| In db2 you have some non-integer values in value1 that are causing an error when the server tries to convert them for the comparison.Doing a string comparison works.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
hyefive
Starting Member
16 Posts |
Posted - 2005-03-04 : 15:41:54
|
| Thanks for input,True, I do have some non-integer values in VALUE1. But why does db1 not give a hoot about that, and db2 does?I shall look up String Comparison... |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2005-03-04 : 16:01:12
|
| Probably because it doesn't try to exaluate the expression for values that would give an error.Maybe they are failing the first test or passing the first part of the or.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
hyefive
Starting Member
16 Posts |
Posted - 2005-03-04 : 16:08:51
|
| Thanks again,Is there any way to prevent the server from attempting to convert those mixed values? |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2005-03-04 : 16:28:43
|
| case when VALUE1 like '%[^0-9]%' then 0 else convert(int,VALUE1) end IN (2619)==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
|
|
Query working in one db, not in another