SQL index

[Marco Facchinetti <marco.facchinetti@xxxxxxxxx>]

Hi, we are at 7.2.

I'm trying to modify this logical file:

R LETTU                     PFILE(MULETTU00F) FORMAT(MULETTU00F)

K LCCDUTENTE
K LCDATALETT                DESCEND
K LCIDLETTUR                DESCEND

O LCSTATOREC                COMP(EQ 'A')
O LCSTATOLET                COMP(NE ' ')

into an SQL index with a WHERE clause.

I would like to insert in the where clause an UDF but looks like is not
possible, this is from SQL reference:

WHERE search-condition
Specifies the condition to apply for a row to be included in the index. The
search-condition cannot contain a predicate with a subquery . It must not
contain
any of the items listed as restrictions for  key-expression.

and key-expression states:

It must not contain any of the following:
...
- User-defined functions other than functions that were implicitly
generated with the creation of a distinct type
- Any function that is not deterministic
...

So I create a UDT with a function:

CREATE DISTINCT TYPE LUGDAT.StatoLettura AS CHAR (1) ;

CREATE FUNCTION LUGDAT.STATO_LETTURA( STATOLETTURAATTUALE
LUGDAT.StatoLettura ,  IDLETTURACOLLEGATA CHARACTER (10) )
RETURNS  CHARACTER (1)
LANGUAGE RPGLE
PARAMETER STYLE GENERAL
NO SQL EXTERNAL
NAME MUOBJ.MULET(MULET_TIPOLETTURAINTERPRETATO)
SPECIFIC LUGDAT.STATO_LETTURA
PROGRAM TYPE SUB FENCED
DETERMINISTIC
CALLED ON NULL INPUT
NO EXTERNAL ACTION
NOT SECURED ;

but when I try to create the Index:

CREATE INDEX LUGDAT.MULETTU35L
ON LUGDAT.MULETTU00F (LCCDUTENTE ASC, LCDATALETT DESC, LCIDLETTUR DESC)
WHERE LCSTATOREC  = ' ' AND LUGDAT.STATO_LETTURA(LCSTATOLET , LCIDLETRET) =
' '
RCDFMT LETTU ADD ALL COLUMNS UNIT ANY  KEEP IN MEMORY NO ;

receive this error:
Stato SQL: 42704
Codice fornitore: -204
Messagge: [SQL0204] STATO_LETTURA in LUGDAT type *N not found.

Any idea?

TIA
--
Marco Facchinetti

Mr S.r.l.

Tel. 035 962885
Cel. 393 9620498

Skype: facchinettimarco