Hi,
here are the remaining restrictions for derived indexes:
Limitations
Limitations on what an expression may contain for a derived-key index are
very similar to check constraints.
See the SQL Reference publication for a complete set of limitations. Some of
the more interesting ones follow:
1. UDFs aren?t supported for SQL indexes. However, casting functions for a
UDT are supported.
2. Built-ins are allowed but System UDFs aren?t supported.
3. Any SQL derived-key index can?t be saved to a release prior to 6.1.
4. A derived-key index has the same restrictions as a check constraint.
5. A primary key, unique or referential constraint can?t use or share an SQL
derived-key index.
Copied from IBM Systems Magazine: SQL Derived-Key Indexes Help Organize
Keyed Data - July 2008
http://www.ibmsystemsmag.com/i5/july08/technicalcorner/21013p1.aspx
Copied from the SQL Reference - Create Index:
A column-name must not identify a LOB or DATALINK column, or a distinct type
based on a LOB or DATALINK column.
If the expression is not a column name, it must not reference a ROWID or
DATALINK column, or a distinct type based on a ROWID or DATALINK column.
It must not contain any of the following:
v Subqueries v Aggregate functions
v Variables v Parameter markers
v Complex expressions that contain LOBs (such as concatenation)
v Special registers
v Sequence references
v OLAP specifications
v ROW CHANGE expressions
v User-defined functions other than functions that were implicitly generated
with the creation of a distinct type
v Any function that is not deterministic
v The following built-in scalar functions:
ATAN2
DECRYPT_DB
ENCRYPT_TDES
RAND
CURDATE
DIFFERENCE
GENERATE_UNIQUE
REPEAT
CURTIME
DLURLCOMPLETE 1
GETHINT
REPLACE
DATAPARTITIONNAME
DLURLPATH
IDENTITY_VAL_LOCAL
ROUND_TIMESTAMP
DATAPARTITIONNUM
DLURLPATHONLY
INSERT
SOUNDEX
DAYNAME
DLURLSCHEME
MONTHNAME
TIMESTAMP_FORMAT
DBPARTITIONNAME
DLURLSERVER
MONTHS_BETWEEN
TIMESTAMPDIFF
DECRYPT_BINARY
DLVALUE
NEXT_DAY
TRUNC_TIMESTAMP
DECRYPT_BIT
ENCRYPT_AES
NOW
VARCHAR_FORMAT
DECRYPT_CHAR
ENCRYPT_RC2
RAISE_ERROR
WEEK_ISO
Mit freundlichen Grüßen / Best regards
Birgitta Hauser
"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and loosing them? Not training them
and keeping them!"
-----Ursprüngliche Nachricht-----
Von: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von rob@xxxxxxxxx
Gesendet: Tuesday, 09. September 2008 16:24
An: midrange-l@xxxxxxxxxxxx
Betreff: V6R1 CREATE INDEX
I can't seem to get the V6R1 index enhancements to support either CASE or
UDFs. Is that a limitation or am I mucking it up?
create index rob/jeff10l1 on rob/jeff10 (mykey)
RCDFMT JEFF10L1R ADD mykey, pordcode, pord, cost, Myudf(cost, pordcode,
pord) as price
Token ( was not valid.
create index rob/jeff10l1 on rob/jeff10 (mykey)
RCDFMT JEFF10L1R ADD mykey, pordcode, pord, cost,
case when pordcode='P' then cast(cost*pord as dec(15,5))
else cast(cost+pord as dec(15,5)) end as price
Keyword WHEN not expected.
Rob Berendt
midrange.com
