Is it possible to create indices with expression engine expressions using SQL?

Question

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.

Answer 1

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 );