Aage Johansen írta:

>KamiHír wrote:
> > Hi!
> >
> > Thank you for the lighting fast answer. I would happy with exact match
> > because
> >
> > WHERE LANG1='something' could be very different.
> > I doesn't know the form of searched string...
> > It may be like:
> > something
> > something...
> > ~something
> > some~thing
> > so~mething...
> >
> > So I don't know how to do the match. In my opinion if I remove ~ and ...
>from LANG1 (also pre/post spaces and whitespaces) I will get a better
>match. I think I should use procedure and call it like:
> > SELECT STRIPUNNECESSARYCHARS(LANG1) AS STRIPED, LANG2 FROM TRANSLAT
>WHERE STRIPED='something';
> >
>
>Since this cannot use an index you will have a table scan. Likewise,
>"containing" will not use index. If a table scan is fast enough you can
>use an SP to StripUnnecessaryChars. I'm not sure you can (currently) use
>the field alias in the WHERE clause.
>An alternative could be to store a fixed-up string in another field - you
>wouldn't have to use the SP. Further, you might store fixed-up "words" in
>a separate table - more work, but you might be able to use an index on
>that table to speed up retrieval.
>
>
>--
>Aage J.
>
>
>
>

Yes, you are right I can not use alias in WHERE condition but in ORDER
BY, GROUP BY under Firebird 2 (Alfa 02)...

I found it in the release notes of FB2:

Expression Indexes
O. Loa,
D. Yemanov,
A. Karyakin
Arbitrary expressions applied to values in a row in dynamic DDL can now
be indexed, allowing indexed
access paths to be available for search predicates that are based on
expressions.
Syntax Pattern:
CREATE [UNIQUE] [ASC[ENDING] | DESC[ENDING]] INDEX <index name>
ON <table name>
COMPUTED BY ( <value expression> )
Examples
1.
CREATE INDEX IDX1 ON T1
COMPUTED BY ( UPPER(COL1 COLLATE PXW_CYRL) );
COMMIT;
/**/
SELECT * FROM T1
WHERE UPPER(COL1 COLLATE PXW_CYRL) = 'ÔÛÂÀ'
-- PLAN (T1 INDEX (IDX1))
2.
CREATE INDEX IDX2 ON T2
COMPUTED BY ( EXTRACT(YEAR FROM COL2) || EXTRACT(MONTH FROM COL2) );
COMMIT;
/**/
SELECT * FROM T2
ORDER BY EXTRACT(YEAR FROM COL2) || EXTRACT(MONTH FROM COL2)
-- PLAN (T2 ORDER IDX2)
Points to Note
The expression used in the predicate must match exactly the expression
used in the index declaration,
in order to allow the engine to choose an indexed access path. The given
index will not be available for
any retrieval or sorting operation if the expressions do not match.
1.
Expression indices have exactly the same features and limitations as
regular indices, except that, by
definition, they cannot be composite (multi-segment).

It is a good news if I would like to play with SP in this situation.


KAMI