Hi,

i have an index on string field (field OPIS - see below) , case insensitive
(default option).

When I select this index I see records in following order

a....
a....
A....
a....
a...

I would expect A... to be at the end.

I changed index to sensitive and I get the same result.

What could be wrong?

Dbisam 4.22

Regards,

Joze


*******************
My table is created so:

CREATE TABLE "Seznam_storitev" (
 "ID_storitve" AUTOINC DESCRIPTION 'ID storitve',
 "ID_laboratorija" INTEGER DESCRIPTION 'ID laboratorija' NOT NULL,
 "ID_tip_storitve" INTEGER DESCRIPTION 'ID tipa storitve' NOT NULL,
 "Opis" VARCHAR(80) DESCRIPTION 'Opis storitve' NOT NULL,
 "Material" MONEY DESCRIPTION 'Cena materiala' NOT NULL DEFAULT 0,
 "Delo" MONEY DESCRIPTION 'Cena dela' NOT NULL DEFAULT 0,
 "Amortizacija" FLOAT DESCRIPTION 'Amortizacija' NOT NULL DEFAULT 0,
 "Rezija1" FLOAT DESCRIPTION 'Re¾ija 1' NOT NULL DEFAULT 0,
 "Rezija2" FLOAT DESCRIPTION 'Re¾ija 2' NOT NULL DEFAULT 0,
 "Rezija3" FLOAT DESCRIPTION 'Re¾ija 3' NOT NULL DEFAULT 0,
 "Trajanje_meritve" INTEGER DESCRIPTION 'Èas trajanja meritve (v minutah)' NOT
NULL DEFAULT 0,
 "Opomba" VARCHAR(200) DESCRIPTION 'Opomba',
 "Ustvarjeno" TIMESTAMP DESCRIPTION 'Datum nastanka' NOT NULL DEFAULT
CURRENT_TIMESTAMP,
 "Zadnja_sprememba" TIMESTAMP DESCRIPTION 'Datum zadnje spremembe' NOT NULL
DEFAULT CURRENT_TIMESTAMP,
 PRIMARY KEY ("ID_storitve")

 DESCRIPTION 'baza storitev'

 LOCALE sloven¹èina

 USER MAJOR VERSION 1

 USER MINOR VERSION 0
);

CREATE INDEX "ID_laboratorija" ON "Seznam_storitev"("ID_laboratorija");

CREATE INDEX "ID_tip_storitve" ON "Seznam_storitev"("ID_tip_storitve");

CREATE INDEX "Opis" ON "Seznam_storitev"("Opis") COMPRESS FULL;

Joze,

<< i have an index on string field (field OPIS - see below) , case
insensitive (default option). >>

Two things:

1) The CREATE INDEX that you show is not case-insensitive, it is
case-sensitive.

2) Did you try repairing the table to see if it is corrupted ?

--
Tim Young
Elevate Software
www.elevatesoft.com

>1) The CREATE INDEX that you show is not case-insensitive, it is
>case-sensitive.

I recreate index using DBsys to case-sensitive and next time to
case-insensitive: both times I get same records order like:

record#1 - field OPIS.value= aa
record#2 - field OPIS.value= aA
record#3 - field OPIS.value= Aa
record#4 - field OPIS.value= AA
record#5 - field OPIS.value= aaA
record#6 - field OPIS.value= AaA
record#7 - field OPIS.value= asd

Is it normal that records starting with letter 'A' (like record# 3 and #4) are
before records starting with letter 'a' (record # 5 and #7)?


>2) Did you try repairing the table to see if it is corrupted ?

Yes. I repaired and optimized, no problems there. Still wrong records order. Or
I don't understand how index is generated.

Regards,

Joze

"Joze" <Joze.NoSpam.Stihec@nospam.siol.net> wrote in message
news:8hfnt3l0fll29gplotgrgqlr7obmoa2mcp@4ax.com...
>
>
> I recreate index using DBsys to case-sensitive and next time to
> case-insensitive: both times I get same records order like:
>

This is not the case. See posting in binaries. You are doing something
wrong.

Robert

>This is not the case. See posting in binaries. You are doing something
>wrong.

Robert,

thanks for your interest in this case.

Please try to change Locale in your tables. I am using "sloven¹èina". I have
both times (case-sensitive and case-insensitive table  same records order - see
pictures in binaries).

I am using DBisam 4.22, Delphi 7, Windows XP in slovene language

CREATE TABLE IF NOT EXISTS "test_insens"
(
  "ID" AUTOINC,
  "Opis" VARCHAR(80),
PRIMARY KEY ("ID") COMPRESS NONE
DESCRIPTION 'MyCaseInsensitive'
LOCALE CODE 1060
USER MAJOR VERSION 1
);

CREATE NOCASE INDEX IF NOT EXISTS "Opis" ON "test_insens" ("Opis") COMPRESS
FULL;


CREATE TABLE IF NOT EXISTS "test_sens"
(
  "ID" AUTOINC,
  "Opis" VARCHAR(80),
PRIMARY KEY ("ID") COMPRESS NONE
DESCRIPTION 'MyCaseSensitive'
LOCALE CODE 1060
USER MAJOR VERSION 1
);

CREATE INDEX IF NOT EXISTS "Opis" ON "test_sens" ("Opis") COMPRESS FULL;

"Joze" <Joze.NoSpam.Stihec@nospam.siol.net> wrote in message
news:kl8ot3htf3mja2hl9ajvd3b3cvudthirv7@4ax.com...
>
> Please try to change Locale in your tables. I am using "sloven¹èina". I
> have
> both times (case-sensitive and case-insensitive table  same records
> order - see
> pictures in binaries).
>

You're right there's something weird here. I did not get the same order see
binaries. Using 4.25 Build 4

Robert

On Sat, 15 Mar 2008 17:15:07 -0400, "Robert"
>You're right there's something weird here. I did not get the same order see
>binaries. Using 4.25 Build 4
>
>Robert

Maybe Tim will have an answer...

Using my locale ('sloven¹èina') it doesn't work as expected.
After changing locale to ANSI everything works OK again.

So it is related to locale...

Best regards,

Joze

Joze,

<< Please try to change Locale in your tables. I am using "sloven¹èina". I
have both times (case-sensitive and case-insensitive table  same records
order - see pictures in binaries).

I am using DBisam 4.22, Delphi 7, Windows XP in slovene language >>

Did you try this using 4.25 ?  There is always the possibility that it has
been fixed since 4.22.

--
Tim Young
Elevate Software
www.elevatesoft.com

Robert,

<< You're right there's something weird here. I did not get the same order
see binaries. Using 4.25 Build 4 >>

Just to confirm - are you saying that you didn't get the same order as Joze,
or that you replicated what he's seeing ?

Thanks,

--
Tim Young
Elevate Software
www.elevatesoft.com

"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote in message
news:5FC020DC-98A0-4470-A31A-98B4505AF2DC@news.elevatesoft.com...
> Robert,
>
> << You're right there's something weird here. I did not get the same order
> see binaries. Using 4.25 Build 4 >>
>
> Just to confirm - are you saying that you didn't get the same order as
> Joze, or that you replicated what he's seeing ?
>

After looking at the example again, I'm not sure what I meant. Sorry.

Robert