Hello

I use the following script as the basis for some memory tables, and it works well, except where I create an index in the table. If I then re-create
the table using different parameters that do not require an index, I get the error message "inxLastName" does not exist. I guess I could re-create
the index for the singleton query, but maybe I can have the script clear out the index at the same time the table is dropped.

If I call the script below with the SQLStatement similar to 'select * from Client where OrderValue > 30.0', then run an ALTER TABLE statement to
add an index inxLastName to the new table, all is well, and the table is sorted on LastName. If I then call the script below with a different
statement, such as the singleton 'select * from Client where ClientID = 345' then the index is redundant, but the inxLastName persists from when
the same table had been created earlier.

SCRIPT (IN SQLStatement VARCHAR, IN TableName VARCHAR, IN AddBool BOOLEAN)
BEGIN
DECLARE InfoCursor SENSITIVE CURSOR FOR InfoStmt;
DECLARE ResultCursor SENSITIVE CURSOR WITH RETURN FOR ResultStmt;
PREPARE InfoStmt FROM 'SELECT * FROM Information.Tables WHERE Name=?';
OPEN InfoCursor USING TableName;
IF (ROWCOUNT(InfoCursor) > 0) THEN
 EXECUTE IMMEDIATE 'DROP TABLE "'+TableName+'"';
END IF;
CLOSE InfoCursor;
PREPARE ResultStmt FROM 'CREATE TABLE "'+TableName+'" AS '+SQLStatement+' WITH DATA';
EXECUTE ResultStmt;
IF AddBool THEN
 EXECUTE IMMEDIATE 'ALTER TABLE "'+TableName+'" ADD COLUMN "SelRow" BOOLEAN DEFAULT %s';
END IF;
END

How can I force the indexes to be dropped, without the calling method knowing the names of the indexes?

Let me know if I haven't explained it properly. I'm using ANSI, version 2.03.

Regards & TIA

Peter

Peter


As far as I know dropping the table drops the indices as well so I'm guessing your problem is with the table component you then hook the created memory table up to. Since there's no way to do that in the script then before you call the script set the table component's indexname to ''


Roy Lambert [Team Elevate]

Roy

Doh! Of course, I should have seen that. Thanks again.

Peter