Hi,

First try using "or" not "in" in your query
Ifyou really need to create expression index then think about result type of expression . In your statement result type is boolean and is not supported before fb 3.0

Write case statement in expression

Regards,
Karol Bieniaszewski

----- Reply message -----
Od: "Tim Ward" <tdw@...>
Do: <firebird-support@yahoogroups.com>
Temat: [firebird-support] Computed index
Data: śr., paź 9, 2013 13:29


I've got a query containing

.... WHERE ...  (MYFIELD in (1,3,4,5,7,8,9,11,12,13))

which runs horribly slowly, because (quite reasonably) the query
optimiser gives up on that, even though it's got an index on MYFIELD.

So, I thought, what about a computed index, as in:

CREATE ASC INDEX xxx ON MYTABLE COMPUTED BY (MYFIELD in
(1,3,4,5,7,8,9,11,12,13))

but trying to create that index gives

Dynamic SQL Error
SQL error code = -104
Token unknown - line 1, column <nn>
in
while executing:
CREATE ASC INDEX xxx ON MYTABLE COMPUTED BY (MYFIELD in
(1,3,4,5,7,8,9,11,12,13))

I can find nothing in the Firebird book, or anywhere else, suggesting
that "in" is not allowed in the expression for a computed index.

Any suggestions?

--
Tim Ward



------------------------------------

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Visit http://www.firebirdsql.org and click the Resources item
on the main (top) menu.  Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Yahoo! Groups Links

<*> To visit your group on the web, go to:
   http://groups.yahoo.com/group/firebird-support/

<*> Your email settings:
   Individual Email | Traditional

<*> To change settings online go to:
   http://groups.yahoo.com/group/firebird-support/join
   (Yahoo! ID required)

<*> To change settings via email:
   firebird-support-digest@yahoogroups.com
   firebird-support-fullfeatured@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
   firebird-support-unsubscribe@yahoogroups.com

<*> Your use of Yahoo! Groups is subject to:
   http://info.yahoo.com/legal/us/yahoo/utos/terms/