Databases
StackOverflow2013
Postsdbo
POAdvantage Database Server (ADS) CIChar data type ISNULL
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POAdvantage Database Server (ADS) CIChar data type ISNULL
    text
    copied!<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>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload