Combined indexes (date-type +char type)? 
Author Message
 Combined indexes (date-type +char type)?

Hello all,

I want to use a dbf with an index on  dtoc(field1)+field2   (field1 is
of type date and field2 is of type char) I've created this index in
dbase. Using (browse or append to the database) this index in delphi
is no problem, but how can I use findkey (it expects a combination of
fields)  and how can I create such a index at runtime. Do I have to
use the dbiAddIndex procedure ?

Greetings

Kees



Sun, 26 Jul 1998 03:00:00 GMT  
 Combined indexes (date-type +char type)?

Quote:

>I want to use a dbf with an index on  dtoc(field1)+field2   (field1 is
>of type date and field2 is of type char) I've created this index in
>dbase. Using (browse or append to the database) this index in Delphi
>is no problem, but how can I use findkey (it expects a combination of
>fields)  and how can I create such a index at runtime. Do I have to
>use the dbiAddIndex procedure ?

What follows is a copy of the technical article "dBASE Expression Indexes:
a Primer," available as TI2838. It discusses this and a number of other
pertinent issues when using dBASE tables with expression indexes.

Essentially, you cannot use either the FindKey, GotoKey, or FindNearest
methods of the TTable component with a dBASE expression index (the "why"
explained in article). Instead, you will need to use the GotoNearest
method.

BTW, the dBASE Data Manipulation Language (DML) function DTOC will convert
a Date type field value to character, but in an index will _not_ produce a
chronological ordering. Character indexes sort data first on the first
character in the field, then on the second, on the third ... and so on.
With a Date type field so converted, it will be in the format MM/DD/YYYY,
causing the sort to effectively be on the month, the day, and then the
year. If you need a chronological ordering for a converted Date type field,
use the DTOS function instead.

Technical article TI2838 follows...

Indexes for dBASE tables may be based on a the values from a single field,
unmodified, or on an expression. Index expressions, unique to dBASE
indexes, may be composed of multiple fields, modifications of field values,
or combinations of these. The expression for a dBASE expression index is
created by using dBASE functions and syntax to concatenate multiple fields
or to perform the modifications of field values for fields included in the
index expressions.

Two section are included at the end of this technical article which
describe the mechanics of creating dBASE expression indexes, one applicable
to doing this in the Database Desktop utility and the other for including
this capability in Delphi applications.

Expression Indexes Based On Multiple Fields
===========================================

dBASE functions are available for use in Delphi or the Database Desktop for
the express use in index expressions, and then only in conjunction with
dBASE indexes. That is, you cannot use dBASE functions or syntax to build
an index expression for a Paradox or Local InterBase Server (LIBS) table.
Nor can dBASE functions be used in Delphi programming. They are only
available for dBASE expression indexes. The dBASE functions and syntax that
can be used for expression indexes are provided by the Borland Database
Engine (BDE) Dynamic Linked Library (DLL) file IDDBAS01.DLL.

When creating a dBASE index that is to be based on the values from two or
more fields in the table for which the index is being created, the two or
more fields are concatenated (connected together) in a manner similar to
how String type values are concatenated in Delphi syntax: the "+" operator.
For example, the expression needed to create an index that orders first on
a LastName field and then on a FirstName field would be:

  LastName + FirstName

Unlike in dBASE itself, such indexes that are based on multiple fields are
limited to using just those fields in the one table. dBASE allows the
creation of indexes based on multiple fields contained in different tables.
This is possible only by having the other table open at the time the index
is created or when the table containing the index is used.

With multi-field indexes for other table types (e.g., Paradox and
InterBase), the multiple fields are delimited by the semi-colon (;), as in:

  LastName;FirstName

In dBASE expression indexes that concatenate multiple fields, an actual
expression is used:

  LastName + FirstName

When creating index expressions that concatenate two or more fields, all of
the fields included in the index expression must be of the same data type.
Additionally, if they are to be concatenated instead of added together, the
fields must all be of String type. For example, given two Integer type
fields, Value1 and Value2, the index expression...

  Value1 + Value2

...would not cause an error. But then, neither would it concatenate the two
field values; it would add them together. Thus, if Value1 for a given
record contained 4 and Value2 5, the resulting index node would be an
Integer value of 9, not a String concatenation "45".

If fields to be included in an expression index are not of String type,
they must be converted. Here are some dBASE functions to convert various
data types to String for purposes of creating index expressions:

  STR(<numeric value> [, <width> [, <decimal places>]])
  Converts from either Float or Numeric dBASE types to Character (String)

  DTOS(<date value>)
  Converts Date value to Character, format YYYYMMDD

  MLINE(<memo field>, <line number>)
  Extracts a single line from a memo field as a Character value

Another consideration in creating indexes based on the concatenation of
multiple field is the maximum allowable length of the index value. The
value returned by an index expression may not exceed 100 characters. This
is a limit on the length of the value returned by the expression, not on
the length of the expression itself. For example, you cannot index on the
concatenation of two fields that both have a length of 255 characters.

Expression Indexes Based On Modifications Of Field Values
=========================================================

In addition to creating indexes based on the concatenation of two or more
field values, it is also possible to construct an index that is based on a
modification of a field value. Examples of this include indexing on just
the first three characters of a String type field, on just the year and
month from a Date field, indexing on a contantenation of a String and Date
field such that the ordering of the String field is ascending but the Date
descending, and even indexing on Boolean field values.

Creating indexes that are based on modifications of field values requires
at least a working knowledge of dBASE functions and syntax -- because the
process uses dBASE, and not Delphi, functions and syntax. The dBASE
function SUBSTR() extracts a substring of a String value. The Delphi
equivalent for this dBASE function is Copy. But, of these two functions
that serve the same purpose, only SUBSTR() may be used in dBASE index
expressions.

To use dBASE functions in dBASE index expressions, simply include the
function wherever an index expression is called for, using the appropriate
dBASE syntax for the function, along with a reference to the name(s) of the
field(s) used in the function. For example, an index expression based on
only the last three characters of a String type field called Code, that is
20 characters long, would be:

  RIGHT(Code, 3)

Caution should be used in constructing dBASE index expressions that modify
field values to ensure that the resulting expression would return a value
of a consistent length for every record in the table. For instance, the
dBASE TRIM() function removes the trailing blanks (ASCII decimal 32) from a
String type value. If this were used in conjunction with concatenating two
String fields where the field does not contain values of the same length
for all records, the value resulting from the expression will not be the
same for all records. Case in point, an index expression based on the
concatenation of a LastName and a FirstName field, where the TRIM()
function is applied to the LastName field:

  TRIM(LastName) + FirstName

This expression would not return values of a consistent length for all
records. If the LastName and FirstName fields contained the values...

  LastName FirstName
  -------- ---------
  Smith    Jonas
  Wesson   Nancy

...the result of applying the index expression above would be:

  SmithJonas
  WessonNancy

As can be seen, the length of the value for the first record would be 10
characters, while that for the second 11 characters. The index nodes
created for this index expression would be based on the field values for
the first record encountered. This would result in an index node 10
characters long being applied to the field values for all record. In this
example, that would result in the truncation of the expression value for
the second record to "WessonNanc". This would subsequently cause searches
based on the full field value to fail.

The solution to this dilemma would be to not use the TRIM() function so
that the full length of the LastName field, including padding from the
trailing spaces, is used. In indexes that use the IIF() function to order
by one field or another, based on the evaluation of a logical expression in
the IIF(), if the two fields are of different lengths, the shorter field
would need to be padded with spaces to make it the same length as the
longer field. For example, assuming an index that uses the IIF() function
to index either on a Company or a Name field, based on the contents of
Category field, and where the Company field is 40 characters long but the
Name field is 25 characters long, the Name field would need to be padded
with 15 spaces; accomplished with the dBASE function SPACE(). That index
expression would then be:

  IIF(Category = "B", Company, Name + SPACE(15))

Searches And dBASE Expression Indexes
=====================================

dBASE expression indexes are exceptions to the norm in how they are handled
by Delphi and the BDE as opposed to how multiple field indexes for other
table types are handled.

This puts such dBASE indexes into a separate class. Handling of such
indexes by Delphi and the BDE is different than those for other table
types. One of these differences is that not all index-based searching using
Delphi ...

read more »



Thu, 30 Jul 1998 03:00:00 GMT  
 Combined indexes (date-type +char type)?


[...]

Quote:
>BTW, the dBASE Data Manipulation Language (DML) function DTOC will convert
>a Date type field value to character, but in an index will _not_ produce a
>chronological ordering. Character indexes sort data first on the first
>character in the field, then on the second, on the third ... and so on.
>With a Date type field so converted, it will be in the format MM/DD/YYYY,
>causing the sort to effectively be on the month, the day, and then the
>year. If you need a chronological ordering for a converted Date type field,
>use the DTOS function instead.

Ommission:

If you do use the DTOS function, it will translate the Date value to String
in the format YYYYMMDD. This gives a true chronological ordering, but
requires you do a little fancy footwork if you want to search on this field
(ensuring the search date, too, is translated to this format).

**************************************************************************
Steve Koterski                    "Knowledge advances by steps, and not by
Product Group Manager             leaps."
Delphi Technical Support                       -- Lord Macaulay, 1800-1859
Borland International, Inc.



Fri, 31 Jul 1998 03:00:00 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. changing real type data to word type

2. Money field type same as Currency data type??

3. numeric field type same as float type??

4. Types and Type-casting ...

5. DBASE IV with CHAR + DATE IN INDEX?

6. Query with dates ==> Type Mismatch

7. Date type fields

8. Interbase Date Type

9. Usage of date-type in SQL!

10. DBD/DBI and DATE Type

11. date type object using turbo vision

12. Date types and MS-SQL

 

 
Powered by phpBB® Forum Software