DISTINCT not working - varchar

[Kay C Lan]

Ivan,

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:

6.4.6    Manipulating Unicode – Using UTF-8
> 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.
>
> Important: We recommend you use UTF-8 in your application whenever you want
> to
> store or manipulate Unicode data. UTF-8 has the advantage that it preserves
> ASCII
> punctuation. This means that you can still use the word, item and line
> chunk expressions
> to manipulate UTF-8. You cannot use the character chunk as that will return
> individual
> bytes.
>

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:-(

Thanks also for the conversion How Too, worked perfectly, now I can
DISTINCT:-)

Very much appreciated.

On Mon, Jun 21, 2010 at 8:11 PM, Ivan Smahin
<ivan_smahin at paradigmasoft.com>wrote:

> Hello Kay,
>
> Monday, June 21, 2010, 8:50:19 AM, you wrote:
>
> > Hi Ruslan,
>
> > No it is not. I clicked the box to test it but it came up with a
> > confirmation message, suggesting that I probably should really
> > understand what I'm about to do - which I don't.
>
> > I did a quick search of the Wiki but didn't learn much. If
> > IndexedByWords has to be checked what are the other consequences -
> > why would I, why wouldn't I? What constitutes a word - my varchar
> > fields can contain things like AMS#deg°C, or maybe a URL with all sorts
> of funny characters.
>
> > I could extract a distinct list by other means if I had to.
>
> > DISTINCT just worked in mySQL and postgreSQL so curious to learn
> > why Valentina my handle it differently.
>
> > Thanks again
>
> > On Mon, Jun 21, 2010 at 1:15 AM, Ruslan Zasukhin
> > <ruslan_zasukhin at valentina-db.com> wrote:
> > On 6/20/10 5:13 PM, "Kay C Lan" <lan.kc.macmail at gmail.com> wrote:
>
> > Hi Kay,
>
> > Is field marked as IndexedByWords?
>
>
> >> simple SQL query:
> >>
> >> SELECT DISTINCT category FROM products
> >>
> >> returns every single row, all category returned multiple times.
> >>
> >> Tested in VStudio 4.5 and same result.
> >>
> >> Searching the List I see Christian reported getting Errors 1 year ago
> when
> >> doing DISTINCT on varchar fields. My  field is varchar but I don't get
> an
> >> error I just get exactly the same result as if I'd done:
> >>
> >> SELECT category FROM products
> >>
> >> Did a quick test on unsigned short and date field and got correct
> results.
> >>
> >> Is there some trick to doing DISTINCT on varchar fields?
>
> Well,  your  db storage is set to UTF-8. It is not officially supported
> yet. There are a lot of issues like you trap in. You should use UTF-16
> only.
>
>
> Steps to convert your db:
>
> 1. Make a SQL-dump of your db.
> 2. Edit result  - it should be
>
> SET PROPERTY
>        [Mode],
>        [IdentsCaseSensitive],
>        [SegmentSize],
>        [SchemaVersion],
>        [DateTimeFormat],
>        [DateSeparator],
>        [TimeSeparator],
>        [LocaleName],
>        [StorageEncoding],
>        [FrenchCollation],
>        [AlternateHandling],
>        [CaseFirst],
>        [CaseLevel],
>        [NormalizationMode],
>        [Strength],
>        [HiraganaQuaternaryMode],
>        [NumericCollation]
> of DATABASE  TO
>        'kDsc_Dat_Blb_Ind',
>        false,
>        32768,
>        1,
>        'kDMY',
>        '.',
>        ':',
>        'en_US_POSIX',
>        'UTF-16',
>        'kOff',
>        'kNonIgnorable',
>        'kOff',
>        'kOff',
>        'kOff',
>        'kTertiary',
>        'kOff',
>        'kOff';
>
>
> instead of
>
> SET PROPERTY
>        [Mode],
>        [IdentsCaseSensitive],
>        [SegmentSize],
>        [SchemaVersion],
>        [DateTimeFormat],
>        [DateSeparator],
>        [TimeSeparator],
>        [LocaleName],
>        [StorageEncoding],
>        [FrenchCollation],
>        [AlternateHandling],
>        [CaseFirst],
>        [CaseLevel],
>        [NormalizationMode],
>        [Strength],
>        [HiraganaQuaternaryMode],
>        [NumericCollation]
> of DATABASE  TO
>        'kDsc_Dat_Blb_Ind',
>        false,
>        32768,
>        1,
>        'kDMY',
>        '.',
>        ':',
>        'en_US_POSIX',
>        'UTF-8',
>        'kOff',
>        'kNonIgnorable',
>        'kOff',
>        'kOff',
>        'kOff',
>        'kTertiary',
>        'kOff',
>        'kOff';
>
>
>  3. Load this dump
>
>
> --
> Best regards,
> Ivan Smahin
> Senior Software Engineer
> Paradigma Software, Inc
> Valentina - The Ultra-Fast Database
> http://www.valentina-db.com
>
> _______________________________________________
> Valentina mailing list
> Valentina at lists.macserve.net
> http://lists.macserve.net/mailman/listinfo/valentina
>