Is it possible to create indexes with expression 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 is as follows:

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

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

We tried to quote an expression with double quotes, single quotes, and brackets.

Answer 1

You can use the system procedure sp_CreateIndexto do this:

execute procedure sp_CreateIndex( 'test', null, 'idx1',
           'str(empid,6)+dtos(doh)', null, 0, 0 );   

Answer 2

Skeleton Syntax

CREATE INDEX index_name ON table_name(column_name);

So there are two things you need to do for your code.

• Your expression must evaluate the name of an existing column.
• dbms .

, dbms . , , SQL.

Answer 3

.

, ADT, , null null. CDX ADT, NULL ADT. , NULL, empid doh NULL.

, , - , SQL. SQL , , SQL:

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 used using the SQL engine to optimize data selection.