Cannot create function-based index

[Former Member]

Hi,

I use MaxDB 7.7.06 and have a problem creating a function-based index. E.g. I create this table:

create table temp1 as select 1 id, 'BLAbla' col1 from dual;

Then I use the lower() function within a select:

select lower(col1) from temp1;

As expected, the result is "blabla". Now I want to build a function-based index using the lower() function:

create index temp1_idx1 on temp1(lower(col1));

But this doesn't work, it always results in:

Error: [-4023] (at 34): Unknown function name:LOWER
SQLState:  I4023
ErrorCode: -4023

What's wrong? I spent much time searching for solution, but I don't find anything helpful.

[Former Member · Accepted solution]

Hi,

you missed some important fact:

<create_index_statement> ::=

CREATE [UNIQUE] INDEX <index_name> ON <table_name> (<index_spec>)

<index_spec> ::=

<index_column_name>,...

| <dbfunction_name> (<column_name>,...) [ASC|DESC]

As index_spec a dbfunction_name can be specified, no 'normal' pre-defined SQL-function.

--> create your own dbfunction (Create function ), use the pre-defined function lower inside and use this dbfunction-name in the create index.

To avoid changing half of the application (if lower is used in every second line...), it is allowed to use the name LOWER for a dbfunction as well.

Elke

[Former Member · Accepted solution]

Thanks, that solved my problem!