Icon View Thread

The following is the text of the current message along with any replies.
Messages 1 to 4 of 4 total
Thread Filter optimization
Wed, Feb 15 2006 2:03 PMPermanent Link

Kai Peters


Hi ~

just  tried everything I could think of and could not get it to work:

Is there no way to set a fully optimized filter in the scenario below?

Field: Last_Name  (String) contains last names in proper case, upper
case &  lower case, i.e.

SMITH
Smith
SMitH

I'd like to set a filter like 'LAST_NAME =3D '''SMITH''' and have all three
records found with OptimizeLevel =3D foFull

I thought that setting the index on last_name to be case-insensitive would
achieve that - but apparently not - it always yields foNone

Setting a filter to 'Upper(last_name) =3D 'SMITH' also loses all optimization
so is there anything I can do?

TIA,
Kai
Wed, Feb 15 2006 2:27 PMPermanent Link

"Ralf Mimoun"
Kai Peters wrote:
....
> I'd like to set a filter like 'LAST_NAME = '''SMITH''' and have all
> three records found with OptimizeLevel = foFull

1) Define a case insensitve index for LAST_NAME

2) try UPPER(LAST_NAME)=UPPER('SMITH') as filter

Without that UPPER(), DBISAM will do a case sensitive filtering, no matter
what index exists. And that's a very good thing, because the structure of
some herlp constructs like indexes should never change the result of a
SELECT statement or a filter (except the order in a SELECT statement if no
ORDER BY is used - in that case, order is undefined and you can't rely on
getting exactly what you want).

Ralf

Wed, Feb 15 2006 2:27 PMPermanent Link

Roy Lambert

NLH Associates

Team Elevate Team Elevate

Kai

Everyone seems to fall into this trap. Use

'Upper(last_name) =Upper( 'SMITH')

You need both so DBISAM understands - its a bit thick


Roy Lambert - ducking the blow from Tim Smiley
Thu, Feb 16 2006 4:49 PMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email timyoung@elevatesoft.com

Roy,

<< You need both so DBISAM understands - its a bit thick

Roy Lambert - ducking the blow from Tim Smiley>>

I agree that it's thick, compared to us.   If I could transfer even a small
percentage of the "smarts" in humans to DBISAM (or any product, for that
matter), I'd be a rich man. Smiley

--
Tim Young
Elevate Software
www.elevatesoft.com

Image