Error Created Inverted Index in Version 5.01.1210185363

Post Reply
sroth
Posts: 44
Joined: Mon Jul 23, 2007 11:21 am

Error Created Inverted Index in Version 5.01.1210185363

Post by sroth »

Hi,

I am now getting the following errror when trying to create an inverted index. I'm 90% sure this related my upgrading to the latest version of Texis.

Has something change with inverted index requirements?

SQL 1>create inverted index Catalog_PRAllX on Catalog(PR1\PR2\PR3\PR4\PR5\PR6\PR7);
create inverted index Catalog_PRAllX on Catalog(PR1\PR2\PR3\PR4\PR5\PR6\PR7);
015 Field must be integral and 64 bits or less in the function openvind
000 SQL failed


I can create the index as non-inverted the data type for these fields is INT.

TABLE DEF:

SQL 1>SELECT * FROM SYSCOLUMNS WHERE TBNAME='Catalog';
SELECT * FROM SYSCOLUMNS WHERE TBNAME='Catalog';
NAME TBNAME TYPE SIZE ORDINAL_POSITION IDX NULLABLE SQLTYPE PRECIS LENGTH SCALE RADIX REMARK
------------+------------+------------+------------+------------+------------+------------+------------+------------+------------+------------+------------+------------+
id Catalog counter 1 1 1 -2 20 8 0 10
CatalogID Catalog int 1 2 1 4 10 4 0 10
ItemNumberID Catalog int 1 3 1 4 10 4 0 10
ProductTypeSort Catalog int 1 9 1 4 10 4 0 10
CategorySort Catalog int 1 13 1 4 10 4 0 10
IsTrophy Catalog int 1 15 1 4 10 4 0 10
MinPrice Catalog double 1 16 1 8 15 8 0 10
MaxPrice Catalog double 1 17 1 8 15 8 0 10
DisplayArtistID Catalog int 1 21 1 4 10 4 0 10
DateNumber Catalog int 1 23 1 4 10 4 0 10
EventID Catalog int 1 24 1 4 10 4 0 10
Year Catalog int 1 28 1 4 10 4 0 10
Month Catalog int 1 29 1 4 10 4 0 10
Day Catalog int 1 30 1 4 10 4 0 10
DateEnteredNumber Catalog int 1 31 1 4 10 4 0 10
PR1 Catalog int 1 45 1 4 10 4 0 10
PR2 Catalog int 1 46 1 4 10 4 0 10
PR3 Catalog int 1 47 1 4 10 4 0 10
PR4 Catalog int 1 48 1 4 10 4 0 10
PR5 Catalog int 1 49 1 4 10 4 0 10
PR6 Catalog int 1 50 1 4 10 4 0 10
PR7 Catalog int 1 51 1 4 10 4 0 10
ItemNumber Catalog varchar 80 4 0 -1 80 80 0 10
ItemNumberBase Catalog varchar 80 5 0 -1 80 80 0 10
ProductTypeID Catalog varchar 80 6 0 -1 80 80 0 10
ProductTypeName Catalog varchar 80 7 0 -1 80 80 0 10
User avatar
John
Site Admin
Posts: 2597
Joined: Mon Apr 24, 2000 3:18 pm
Location: Cleveland, OH
Contact:

Error Created Inverted Index in Version 5.01.1210185363

Post by John »

What exactly are you trying to do? The \ operator is intended for joining string fields, and and inverted index for sort queries.

The syntax you used would normally be used with a METAMORPH INVERTED INDEX, however the fields should be varchar or other text type for that.
John Turnbull
Thunderstone Software
sroth
Posts: 44
Joined: Mon Jul 23, 2007 11:21 am

Error Created Inverted Index in Version 5.01.1210185363

Post by sroth »

I assumed this syntax created a "compound" index. Do I need to create individual inverted indexes on each of the 7 columns?
User avatar
John
Site Admin
Posts: 2597
Joined: Mon Apr 24, 2000 3:18 pm
Location: Cleveland, OH
Contact:

Error Created Inverted Index in Version 5.01.1210185363

Post by John »

That would be a compound index, which does not apply well to INVERTED INDEX, but can to METAMORPH INVERTED INDEX, though the fields should be text fields.

What SQL queries are you aiming to have the indexes help with?
John Turnbull
Thunderstone Software
sroth
Posts: 44
Joined: Mon Jul 23, 2007 11:21 am

Error Created Inverted Index in Version 5.01.1210185363

Post by sroth »

These fields indicate if a product belongs to a certain pricing group. The price groups are used to refined various text and integer-bases searches.

The pricing field refines the searach and is rolled-up (aggregates) for total production in each price group.

Here's the slq the generate the product count in each price group.


<pr>
<!-- 202 /staging/search:449: SELECT SUM(PR1) AS PR1,SUM(PR2) AS PR2,SUM(PR3) AS PR3,SUM(PR4) AS PR4,SUM(PR5) AS PR5,SUM(PR6) AS PR6,SUM(PR7) AS PR7 from Catalog c WHERE IndexData LIKE 'HENDRIX' ; -->
<f id="1" n="Under $25" c="23" a="" sn="under-25"/>
<f id="2" n="$25 - $49" c="43" sn="25-49"/>
<f id="3" n="$50 - $99" c="25" sn="50-99"/>

<f id="4" n="$100 - $499" c="99" sn="100-499"/>
<f id="5" n="$500 - $999" c="96" sn="500-999"/>
<f id="6" n="$1,000 & over" c="55" sn="1000-over"/>
<f id="7" n="Big Ticket" c="54" sn="big-ticket" />
</pr>
User avatar
John
Site Admin
Posts: 2597
Joined: Mon Apr 24, 2000 3:18 pm
Location: Cleveland, OH
Contact:

Error Created Inverted Index in Version 5.01.1210185363

Post by John »

For the kind of query you have shown then indexes on the PR fields will not help. If you were including clauses such as ... AND PR1 = 1 ... to the query then a compound index including IndexData and the PR fields would help, e.g.

CREATE METAMORPH INVERTED INDEX IndexName on Catalog(IndexData, PR1, PR2, PR3, PR4, PR5, PR6, PR7);

I don't see IndexData in the columns you had listed before.
John Turnbull
Thunderstone Software
sroth
Posts: 44
Joined: Mon Jul 23, 2007 11:21 am

Error Created Inverted Index in Version 5.01.1210185363

Post by sroth »

Thanks, I get. In many cases, I do you the PR fields data to strict the query along with index data.

I'll experiment the the compound metamorph index. Thanks.
Post Reply