[thelist] Query successful in one db, not in another

[Brooking, John]

Mark Kamian said:
> Can someone tell me why the following query is successful in one 
> MS SQL Server db table, but not in another?
> 
> SELECT *
> FROM TABLE1
> WHERE ST IN ('CA') AND (NAME IN ('LOS ANGELES') OR
>     VALUE1 IN (2619))
> ORDER BY NAME
> 
> ** Tables structures are identical
> ** When replacing (2619) with ('2619'), query IS successful in the 
> other table.

In addition to the other replies you've gotten on this, I think it can
depend on the data, too. Is your VALUE1 field is a character field, but
contain mostly numbers? If so, I have the same situation in one of my
databases here. I often search using a number, and it often works. On
certain fields, however, I tend to run into a type conversion error. I
suspect it must be converting the field values to numerics to compare
them with what you've passed. If all the rows actually have values that
can be converted to numeric, fine. If not, however, I get an error. So
two databases with the exact same structure and settings, but different
data, can work differently. (My database is Oracle, but I'm speculating
that others may behave similarly.)

In any case, bottom line is, just don't rely on implicit type casting.

- John
-- 


This message may contain information which is private, privileged or confidential and is intended solely for the use of the individual or entity named in the message. If you are not the intended recipient of this message, please notify the sender thereof and destroy / delete the message. Neither the sender nor Sappi Limited (including its subsidiaries and associated companies) shall incur any liability resulting directly or indirectly from accessing any of the attached files which may contain a virus or the like.