Index qualifaer.

Database
Enthusiast

Index qualifaer.

Hi guys!

I would like to create an index after I created the table. What I do is the follwing

create index IE1_INDEXNAMEE (USERNAME.TBNAME.COLNAME) ON USERNAME.TABLENAME;

Teradata complaints about syntax error.
3706 Syntax error. Expected something between the world 'USERNAME' and '.'

Thanks
5 REPLIES
Enthusiast

Re: Index qualifaer.

Hi,

Try this one,

CREATE INDEX IE1_INDEXNAMEE (COLUMN_NAME) ON (TABLE_NAME);

Regards,
Stration.
Enthusiast

Re: Index qualifaer.

I did it before and it works. I know that.
I had only one user before. Now I have 9 users.
Some of their tables have the same names.
For example 3 users have a table COUNTRY. I create index but how I will specify
that the INDEX1 belongs to USER1. For example
CREATE INDEX IE1_COUNTRY(COUNTRYCODE) ON (COUNTRY);
Should I do the following
CREATE INDEX IE1_COUNTRY(USER1.COUNTRY.COUNTRYCODE) ON (USER1.COUNTRY);

If yes then what is the syntax because it gives me the syntax error.

Thanks

Enthusiast

Re: Index qualifaer.

Hi,

Try this one, exactly it helps,

For User-1:

CREATE INDEX IE1_COUNTRY(COUNTRYCODE) ON USER1.COUNTRY;

Regards,
Stration.
Enthusiast

Re: Index qualifaer.

It works.
Thank you very much.

By the way do you think I should add a qualifier to the name of the index?
I am curios about that because I was trying to drop the index and it looks like it was dropped.
But then in Administrative tools I selected the table and then selected "list indexes" all of the indexes were there, nothing was really dropped. I had to drop all the tables and to recreate them again.
Enthusiast

Re: Index qualifaer.

Hi,

No, you should not add a qualifier to Index.

Instead of, you should add (User name to table name as) a qualifier if you have two or more tables at different users. Fire this query and see the result:

DROP INDEX INDEX_NAME ON USER_NAME.TABLE_NAME;

Eg: DROP INDEX IE1_COUNTRY ON USER1.COUNTRY;

If my solution doesn't works for you, please post your query, that you have used to drop the Index.

Regards,
Stration.