3

We are trying to create an index on CDX and ADT tables that use the Advantage expression engine.

The code we tried so far looks like this:

CREATE INDEX IDX1 ON TBL1 (STR(SOME_NUMBER_FIELD,6)+DTOS(SOME_DATE_FIELD));

Is it possible to create an index with the expression STR(SOME_NUMBER_FIELD,6)+DTOS(SOME_DATE_FIELD) using SQL?

We tried quoting the expression with double quotes, single quotes and brackets.

Jens Mühlenhoff
  • 14,565
  • 6
  • 56
  • 113

3 Answers3

5

You can use the system procedure sp_CreateIndex to do that:

execute procedure sp_CreateIndex( 'test', null, 'idx1',
           'str(empid,6)+dtos(doh)', null, 0, 0 );
Mark Wilkins
  • 40,729
  • 5
  • 57
  • 110
1

The skeleton syntax is

CREATE INDEX index_name ON table_name(column_name);

So two things need to happen for your code to work.

  1. Your expression has to evaluate to the name of an existing column.
  2. Your dbms has to accept expressions in column names.

I think most dbms don't accept expressions for column names. But perhaps you can evaluate the expression and submit a valid SQL string instead.

Mike Sherrill 'Cat Recall'
  • 91,602
  • 17
  • 122
  • 185
1

Mark's answer is spot on.

One thing that you should be aware of with expression index in ADT is that any null value in the expression will render the result of the whole expression null. This sometimes causes problem for developer switching from CDX to ADT because NULL value is supported in ADT table. For example, the result of the above expression will be NULL if either empid or doh is NULL.

Another thing to watch out for is that certain expression index may not be usable for SQL optimization. If you intend to mostly using SQL to manipulate the data, it may be better to create the index using the standard SQL syntax:

CREATE INDEX idx1 ON test( empid, doh )

The server will take care of using the correct expression for the CDX and ADT index. And the index will be usable by the SQL engine for optimizing selecting data.

Alex W
  • 3,283
  • 1
  • 19
  • 25
  • Thanks for the good points. In our case however we have to specify the expression manually, because it is for an index reorganization tool. – Jens Mühlenhoff Jan 14 '11 at 07:50