Unfortunately we are using the Advantage Database Server Torture Edition Version 8.1. After I had finished my project, I heard that the data base is configured to be case sensitive. So I changed the table structure, all Char data types to CIChar, which is a case insensitive char field. But I get this error while executing my program:
Advantage.Data.Provider.AdsException:
Error 7200: AQE Error: State = HY000; NativeError = 2214; [Extended Systems][Advantage SQL Engine]Invalid coercion: Result of expression is an ambiguous character type.
I found that the ISNULL(myciChar,'') is causing this problem, but I don't understand, WHY? How could I solve this problem? Is there other known issues with the cichar data type?
Any help will be appreciated. Thanks.
[update]
I found the reason for this error. There was two points to clarify.
works in v7.1:
select myid, isnull(mytext, '-' COLLATE ads_default_ci) as mytext from mytable
error in v7.1:
select myid, isnull(mytext, '-') as mytext from mytable
Probably need to see more of your expression to tell you why you are getting this error. It doesn't have as much to do with the ISNULL as it does the expression the ISNULL is being used in. The engine likely encountered a comparison and couldn't automatically determine if you wanted the comparison to be case sensitive or not. See the documentation which describes case insensitive precedence and coercion.
If not used in an expression, it might just be a bug (I just tried with 10.1 and it worked), verify you are using the latest version of 8.1, which is 8.10.0.38