Query working in one DB, not in another
-
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 NAME
However, if I replace (2619) with ('2619'), the query IS successful in db2.
Any help would be greatly appreciated!
Thanks,
monquatch
-
Is the data the same, or just the table structure the same ?
What is the data type of column VALUE1 ? If you run this query in both databases, what does it tell you:
SELECT DISTINCT IsNumeric(VALUE1) FROM TABLE1
-
Thanks for the input PW,
VALUE1 is a datatype of varchar, and I have both Integer and Non-Integer data in there.
When I ran that query, it gave me a 0 and a 1.
-
If the column is a varchar, you need to quote the value you're comparing it to.
Otherwise, SqlServer tries to cast the column to the datatype of your hard-coded value - and if the column contains an alpha value that won't cast to a number, you get an error.
-
Thanks again,
Is there any way to prevent the server from trying to convert the column to the datatype of my hard-coded value?
Also, why would one db do this and not the other?
-
I>>s there any way to prevent the server from trying to convert the column to the datatype of my hard-coded value?
Yes, make your hard-coded value the same datatype as the column
>>Also, why would one db do this and not the other?
Is the data exactly the same in both ? Did the query I posted above (checking IsNumeric() ) return 2 rows in *both* databases ?
-
>> Is the data exactly the same in both ? Did the query I posted above (checking IsNumeric() ) return 2 rows in *both* databases ? <> Yes, make your hard-coded value the same datatype as the column [Smile] <<
But it is! I never understood varchar to mean "no integers allowed". Does it? In any case, that column needs to have both Integers and Non-Integers.
I can say, however, that whenever the query looks in VALUE1 for data, it will *always* be looking for Integer-type data, so is there any way to just inform the server of that?
-
But it is! I never understood varchar to mean "no integers allowed". Does it? In any case, that column needs to have both Integers and Non-Integers.
2619 and '2619' may both look the same, but they are not. Maybe this illustration will help. Run this in QA:
declare @int integer
declare @char varchar(10)
set @int = 2619
set @char = '2619'
select @int+@int
select @char+@char
I can say, however, that whenever the query looks in VALUE1 for data, it will *always* be looking for Integer-type data, so is there any way to just inform the server of that?
Sure you can! Change the datatype of the value1 column to a numeric type.
-
<<>>
VALUE1 contains both Integer and Non-Integer data, so changing the datatype isn't possible.
-
Then I guess you are only interested in records where value1 is numeric data? If that is the case, maybe something like this will work:
SELECT vt.* FROM
(SELECT * FROM MyTable Where IsNumeric(Value1)=1) vt
If this is not what you are looking for respond back. I don't think we are very far from your solution. RH
-
When you include "VALUE1 IN (2619) " into WHERE clause it means that every value in column VALUE1 will be converted to datatype INT and copmpared to integer value 2619. If there is value 'abc' in the column query will return an error.
If there are values '002619', '2619.00' etc. they will be matched to 2619.
"VALUE1 IN ('2619')" will not match mentioned values, only '2619' will be picked, but it will not generate an error trying to match '2619' with value 'abc'.
Probably one of tables contains not numeric data in the column and another one does not.
_____________
Code for TallyGenerator -
Thanks all for the input.
I found the following article, which applies to this situation where there are both Integer and Non-Integer values in this VARCHAR column:
http://support.microsoft.com/kb/271566
Despite my desire to find a workaround, I'll probably just have to start quoting the values. As far as rhunt's suggestion goes...
>>
SELECT vt.* FROM
(SELECT * FROM MyTable Where IsNumeric(Value1)=1) vt
<<
... I am completely baffled by that, and don't understand where to even begin using that in my example above.
-
YOU SAID: I can say, however, that whenever the query looks in VALUE1 for data, it will *always* be looking for Integer-type data, so is there any way to just inform the server of that?
I SAID: Sure you can! Change the datatype of the value1 column to a numeric type.
YOU SAID: VALUE1 contains both Integer and Non-Integer data, so changing the datatype isn't possible.
I Interpreted: (BASED ON YOUR ORGINAL Statement "whenever the query looks in VALUE1 for data, it will *always* be looking for Integer-type data") to mean that you only want your query to search for VALUE1 values that are numeric compatible.
Therefore, I suggested:
SELECT vt.* FROM
(SELECT * FROM MyTable Where IsNumeric(Value1)=1) vt
-
>>Despite my desire to find a workaround, I'll probably just have to start quoting the values.
You haven't explained just exactly why that is such a problem ? If you have a varchar column containing BOTH numeric and non-numeric values, you only have 2 options:
1) Reduce the resultset before querying, to eliminate non-numerics. RHunt has shown you twice how to do this
2) Quote your search term
Is it really such a problem to quote your numeric ?
Let's end this. Code for the 2 options:
1)
SELECT vt.*
FROM
(
SELECT *
FROM YourTable
WHERE IsNumeric(Value1) =1
) vt
WHERE vt.Value1 = 2619 -- Look ma, no quotes
2)
SELECT * FROM YourTable WHERE Value1 = '2619'
Pick 1 if you get paid by the line of code, pick 2 if you like to write code that you (and others) can maintain a month from now.
-
Be careful with ISNUMERIC!
Look at this:
declare @a nvarchar(50)
SET @a = '10,000.00'
select isnumeric(@A)
-- returms 1 - @a is numeric because it's valid money type value
select 1
where @a = 5
-- generates error:
Server: Msg 245, Level 16, State 1, Line 6
Syntax error converting the nvarchar value '10,000.00' to a column of data type int.
Value is numeric but we cannot compare it to int or real values!
_____________
Code for TallyGenerator
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply