Create Index on Upper( Fieldname )...?! 1

[BOH2007]

Hi@all,

isn't it possible to create an index with using the function upper()...?!

Like this:

Create Index Indexname on Tablename( Upper( Fieldname ) );

I'm using Pervasive.SQL 2000i Service Pack 4

Kind regards
BOH

 

[mirtheil]

No. Why would you want that?
If you're trying to create a case insensitive index, you need to add the CASE flag to the column (either in the CREATE TABLE or with an ALTER TABLE) and then create the index normally.

Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician

http://www.mirtheil.com

 

[BOH2007]

Good question... ;-)

I'm trying to convert dBase files from an advantage database server to pervasive. Building the table structures with sql queries getting from an advantage tool. These old dbase files have indexes as I described above, on upper( fieldname )...

You think, if I change the automatic builded queries from creating indexes on upper( fieldname ) to creating tables with fieldname inclusive the CASE flag, should solve my problem?

A lot more work, but mainly a solution...

Could you please show me how to set the flag in a query...?!

Thanks a lot

 

[BOH2007]

I've got it, just reading the manual... ;-)

 

[mirtheil]

One more comment. PSQL 2000i SP4 is very old. Pervasive just released v10 which is 4 major releases newer than PSQL 2000. You might consider upgrading to the current version.

Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician

http://www.mirtheil.com

 

[BOH2007]

That's absolutly right, but no option at this moment :-(