Query working in one DB, not in another

• AndrePek

  Grasshopper

  Points: 21

  More actions

  March 10, 2005 at 4:27 am

  #546320

  You guys are on the right track with your advice to the original poster, on how to avoid problems with his SQL.

  (To the original poster: From SQLServer's point of view, 2619 is an integer value, and '2619' is a "character string that just happens to contain only numeric digits".  If you write 2619 in the WHERE clause of your SQL query, SQLServer will try to convert the contents of VALUE1 in every record to integer for comparison.  If any of the records have, say, VALUE1='abc', then SQLServer will be unable to convert that to an integer, and will respond with the error that you have been getting.  If you write '2619' in the WHERE clause of your SQL query, SQLServer will do a character-wise comparison of the contents of VALUE1, and VALUE1='abc' will not cause any problems, it will just compare "not equal".

  Moral of the story: Putting quotes around the value in your WHERE clause is the only safe option)

  As to why the query works on one database but not the other, I'll bet that the non-numeric values in the one database are not records for the state of California.  If you do the following query on the 2 databases, I'll bet that you get different answers:

  SELECT DISTINCT IsNumeric(VALUE1) FROM TABLE1 WHERE ST IN ('CALIFORNIA')

• Sergiy

  SSC Guru

  Points: 110208

  More actions

  March 11, 2005 at 12:27 am

  #546544

  > Moral of the story: Putting quotes around the value in your WHERE clause is the only safe option)

   

  Not really.

   

  Keep in mind: ' 2619', '002619' and '2619' are not equal strings, in spite of equality of numeric values in these strings.

  Are you sure you can control the way values are stored in the tables?

   

  Changing column type to varchar you are loosing data type fuctionality. So you need to restore it using something like this:

   

  WHERE case when isnumeric(VALUE1) = 1 then convert (real, replace (VALUE1, ',', '')) else NULL end = 2619

   

  REPLACE is to eliminate commas valid for money but not valid for real.

   

  _____________
  Code for TallyGenerator