RE: index on calculated column

Joseph Cullipher wrote: > I am trying to create an index on a calculated column but keep getting > a syntax error. > > create temp table t1 (started smallint,finished smallint); > create index t1 on t1(started-finished)> > I couldn't find anything in the manuals that say I cannot do this but > it still won't let me. Any suggestions on how I can accomplish this = task? > > = ------------------------------------------------------------------------- > Joseph Cullipher <joseph@cannonexpress.com> Standard Disclaimers Apply > = ------------------------------------------------------------------------- > Joseph: The syntax diagram in the 'Informix Guide to SQL, Syntax' (my copy is = v7.2, page 1-111) is pretty explicit. The relevant segment of the = diagram says that the column or columns you wish to index must suit the = "Identifier" segment. You are attempting to index an "Expression" = segment. (Segments are discussed from p1-640 onwards in my manual). Basically, whatever you wish to index must exist as a column in it's own = right. You might have to create a third column containing the result you = require, and index it. Thinking laterally for a minute, you are currently storing A and B, and = requiring an index on C, where C =3D B - A. Could you perhaps store A = and C in your table, and compute B when required? Hope that helps, RET +------------------------------------------+ | Richard Thomas | | DBA - Marketing Information Systems | | Optus IT | | email: richard_thomas@yes.optus.com.au | | Ph: +61 2 9342 7188 | | "My opinions are my opinions" | +------------------------------------------+