I would like to know if there is a way to make a table index where one of the columns implied is a Soundex of a column:
In Oracle you can do that but in Teradata it gives me back the following error:
CREATE INDEX Failed.  Syntax error, expected something like a name or a Unicode delimited identifier or a 'ROWID' keyword between ',' and the 'SOUNDEX' keyword.
Thanks and regards
Solved! Go to Solution.
The columns in an index have to be columns in that table, not derived columns. You could conceivably add another column containing the soundex value and build the index on that. You could also create a trigger on the table so that the soundex value column is automatically populated on inserts and updates, thus basically yielding the same functionality (and overhead!) as that Oracle index.
Your best bet is a global index:
CREATE JOIN INDEX TRAMOS_BASE_IDX AS SELECT COD_PROVIN, SOUNDEX(ENTIDADRETOC) SNDEX_ENTIDADRETOC, ROWID FROM TRAMOS BASE PRIMARY INDEX (COD_PROVIN, SNDEX_ENTIDADRETOC);
This way you can have an indexed access (via rowid) to the base table rows when necessary (i.e.: columns not in the JI needed).
Keep in mind that SOUNDEX() only allows ASCII chars (no spaces, no 'ñ's, etc...)