Ivan,<br><br>thanks so much for the quick response. Now I see I find my self between a rock and a hard place! As I'm using V4Rev, the Rev User Manual says this:<br><br><blockquote style="margin: 0pt 0pt 0pt 0.8ex; border-left: 1px solid rgb(204, 204, 204); padding-left: 1ex;" class="gmail_quote">
6.4.6 Manipulating Unicode – Using UTF-8<br>While Revolution fields support display of Unicode in UTF-16, Revolution’s chunk expressions are currently not aware of Unicode. Support is planned in the future. At present you should write your own functions to process Unicode text.<br>
<br>Important: We recommend you use UTF-8 in your application whenever you want to<br>store or manipulate Unicode data. UTF-8 has the advantage that it preserves ASCII<br>punctuation. This means that you can still use the word, item and line chunk expressions<br>
to manipulate UTF-8. You cannot use the character chunk as that will return individual<br>bytes.<br></blockquote><br>Looks like I'm going to need to write some function to handle Unicode - there's no point using Rev if you can't use chunk expressions:-(<br>
<br>Thanks also for the conversion How Too, worked perfectly, now I can DISTINCT:-)<br><br>Very much appreciated.<br><br><div class="gmail_quote">On Mon, Jun 21, 2010 at 8:11 PM, Ivan Smahin <span dir="ltr"><<a href="mailto:ivan_smahin@paradigmasoft.com">ivan_smahin@paradigmasoft.com</a>></span> wrote:<br>
<blockquote class="gmail_quote" style="margin: 0pt 0pt 0pt 0.8ex; border-left: 1px solid rgb(204, 204, 204); padding-left: 1ex;"><div class="im">Hello Kay,<br>
<br>
Monday, June 21, 2010, 8:50:19 AM, you wrote:<br>
<br>
</div><div><div></div><div class="h5">> Hi Ruslan,<br>
<br>
> No it is not. I clicked the box to test it but it came up with a<br>
> confirmation message, suggesting that I probably should really<br>
> understand what I'm about to do - which I don't.<br>
<br>
> I did a quick search of the Wiki but didn't learn much. If<br>
> IndexedByWords has to be checked what are the other consequences -<br>
> why would I, why wouldn't I? What constitutes a word - my varchar<br>
> fields can contain things like AMS#deg°C, or maybe a URL with all sorts of funny characters.<br>
<br>
> I could extract a distinct list by other means if I had to.<br>
<br>
> DISTINCT just worked in mySQL and postgreSQL so curious to learn<br>
> why Valentina my handle it differently.<br>
<br>
> Thanks again<br>
<br>
> On Mon, Jun 21, 2010 at 1:15 AM, Ruslan Zasukhin<br>
> <<a href="mailto:ruslan_zasukhin@valentina-db.com">ruslan_zasukhin@valentina-db.com</a>> wrote:<br>
> On 6/20/10 5:13 PM, "Kay C Lan" <<a href="mailto:lan.kc.macmail@gmail.com">lan.kc.macmail@gmail.com</a>> wrote:<br>
<br>
> Hi Kay,<br>
<br>
> Is field marked as IndexedByWords?<br>
<br>
<br>
>> simple SQL query:<br>
>><br>
>> SELECT DISTINCT category FROM products<br>
>><br>
>> returns every single row, all category returned multiple times.<br>
>><br>
>> Tested in VStudio 4.5 and same result.<br>
>><br>
>> Searching the List I see Christian reported getting Errors 1 year ago when<br>
>> doing DISTINCT on varchar fields. My field is varchar but I don't get an<br>
>> error I just get exactly the same result as if I'd done:<br>
>><br>
>> SELECT category FROM products<br>
>><br>
>> Did a quick test on unsigned short and date field and got correct results.<br>
>><br>
>> Is there some trick to doing DISTINCT on varchar fields?<br>
<br>
</div></div>Well, your db storage is set to UTF-8. It is not officially supported<br>
yet. There are a lot of issues like you trap in. You should use UTF-16<br>
only.<br>
<br>
<br>
Steps to convert your db:<br>
<br>
1. Make a SQL-dump of your db.<br>
2. Edit result - it should be<br>
<br>
SET PROPERTY<br>
[Mode],<br>
[IdentsCaseSensitive],<br>
[SegmentSize],<br>
[SchemaVersion],<br>
[DateTimeFormat],<br>
[DateSeparator],<br>
[TimeSeparator],<br>
[LocaleName],<br>
[StorageEncoding],<br>
[FrenchCollation],<br>
[AlternateHandling],<br>
[CaseFirst],<br>
[CaseLevel],<br>
[NormalizationMode],<br>
[Strength],<br>
[HiraganaQuaternaryMode],<br>
[NumericCollation]<br>
of DATABASE TO<br>
'kDsc_Dat_Blb_Ind',<br>
false,<br>
32768,<br>
1,<br>
'kDMY',<br>
'.',<br>
':',<br>
'en_US_POSIX',<br>
'UTF-16',<br>
'kOff',<br>
'kNonIgnorable',<br>
'kOff',<br>
'kOff',<br>
'kOff',<br>
'kTertiary',<br>
'kOff',<br>
'kOff';<br>
<br>
<br>
instead of<br>
<br>
SET PROPERTY<br>
[Mode],<br>
[IdentsCaseSensitive],<br>
[SegmentSize],<br>
[SchemaVersion],<br>
[DateTimeFormat],<br>
[DateSeparator],<br>
[TimeSeparator],<br>
[LocaleName],<br>
[StorageEncoding],<br>
[FrenchCollation],<br>
[AlternateHandling],<br>
[CaseFirst],<br>
[CaseLevel],<br>
[NormalizationMode],<br>
[Strength],<br>
[HiraganaQuaternaryMode],<br>
[NumericCollation]<br>
of DATABASE TO<br>
'kDsc_Dat_Blb_Ind',<br>
false,<br>
32768,<br>
1,<br>
'kDMY',<br>
'.',<br>
':',<br>
'en_US_POSIX',<br>
'UTF-8',<br>
'kOff',<br>
'kNonIgnorable',<br>
'kOff',<br>
'kOff',<br>
'kOff',<br>
'kTertiary',<br>
'kOff',<br>
'kOff';<br>
<br>
<br>
3. Load this dump<br>
<div class="im"><br>
<br>
--<br>
Best regards,<br>
Ivan Smahin<br>
Senior Software Engineer<br>
Paradigma Software, Inc<br>
Valentina - The Ultra-Fast Database<br>
<a href="http://www.valentina-db.com" target="_blank">http://www.valentina-db.com</a><br>
<br>
_______________________________________________<br>
</div><div><div></div><div class="h5">Valentina mailing list<br>
<a href="mailto:Valentina@lists.macserve.net">Valentina@lists.macserve.net</a><br>
<a href="http://lists.macserve.net/mailman/listinfo/valentina" target="_blank">http://lists.macserve.net/mailman/listinfo/valentina</a><br>
</div></div></blockquote></div><br>