title: Advantage Database Server (ADS) CIChar data type ISNULL
body: <p>Unfortunately we are using the Advantage Database Server <em>Torture Edition</em> 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 <em>insensitive</em> char field. But I get this error while executing my program:</p>
  
  <blockquote>
  <pre><code>Advantage.Data.Provider.AdsException:
  </code></pre>
    
    <p>Error 7200:  AQE Error:  State =
    HY000;   NativeError = 2214; 
    [Extended Systems][Advantage SQL
    Engine]Invalid coercion: Result of
    expression is an ambiguous character
    type.</p>
  </blockquote>
  
  <p>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?</p>
  
  <p>Any help will be appreciated. Thanks.</p>
  
  <p><strong>[update]</strong></p>
  
  <p>I found the reason for this error. There was two points to clarify.</p>
  
  <ol>
  <li>The data base has the version 8.1, but the data architect has the version 7.1 and in local mode it takes the architect engine version 7.1. That means it's a v7.1 issue.</li>
  <li>The 2nd parameter in the isnull function is in default case sensitive collation in version 7.1 while my column mytext is cichar and that's the ambiguous character type. So if someone has the same problem, it will work in v7.1 with the collate declaration:</li>
  </ol>
  
  <p>works in v7.1:</p>
  
  <pre><code>select myid, isnull(mytext, '-' COLLATE ads_default_ci) as mytext from mytable
  </code></pre>
  
  <p>error in v7.1:</p>
  
  <pre><code>select myid, isnull(mytext, '-') as mytext from mytable
  </code></pre>