Some SQL questions

1.) What is the syntax if I want to use SQL to add an index to a table?
Possible with Nexus?

2.) I have two tables, one acting as a lookup for the other one. I'm loading
both tables from an external server into an embedded serverengine at
application start. Here's the table structure

Table ModuleSectors:
ModuleID
SectorID
other fields

Table Sectors:
SectorID
SectorName
other fields

There is a one to one relation between the tables based on SectorID.
What I want is to add the field SectorName to the ModuleSectors table in
order to remove that lookup. I thought I try to use SQL for this. Here is
the working result:

// Add the SectorName field to the ModuleSectors table. This removes the
need
// to have a lookup field for the grdModuleSectors display grid
qryLocalModuleSectors.SQL.Text :=
'ALTER TABLE ModuleSectors' + #13#10 +
'ADD COLUMN SectorName char(30)';
qryLocalModuleSectors.ExecSQL;

qryLocalModuleSectors.SQL.Text :=
'UPDATE ModuleSectors' + #13#10 +
'SET SectorName = ' + #13#10 +
' (SELECT SectorName FROM Sectors' + #13#10 +
' WHERE Sectors.SectorID = ModuleSectors.SectorID)';
qryLocalModuleSectors.ExecSQL;
tblLocalModuleSectors.Open;

So what's my problem? I have a gut feeling that the second part (the UPDATE)
is far from optimal. So long story, short question: Is there a better way to
achieve the same?

TIA

--
elektronik-labor Carls GmbH & Co. KG
Stefan Paege

Kontakt: +49 (0)5973 9497-23 Fax: +49 (0)5973 9497-19

"Stefan Paege" <paege@el-carls.de> wrote in message
news:40211205$1@wic040d....
> 1.) What is the syntax if I want to use SQL to add an index to a table?
> Possible with Nexus?

Absolutely.

Example:

CREATE INDEX MyNewIndex ON MyTable
(MyField1, MyField2 DESC, MyField3 IGNORE CASE);

> 2.) I have two tables, one acting as a lookup for the other one. I'm
loading
> both tables from an external server into an embedded serverengine at
> application start. Here's the table structure
....
>Is there a better way to achieve the same?

I believe not - assuming you have an index on Sectors.SectorID, which I
assume you do.

- Per

> CREATE INDEX MyNewIndex ON MyTable
> (MyField1, MyField2 DESC, MyField3 IGNORE CASE);

Great. I was stupid enough to think that I had to use some form of ALTER
statement.

>> Is there a better way to achieve the same?
>
> I believe not - assuming you have an index on Sectors.SectorID, which
> I assume you do.


Yes, index is there.

Thanks

--
elektronik-labor Carls GmbH & Co. KG
Stefan Paege

Kontakt: +49 (0)5973 9497-23 Fax: +49 (0)5973 9497-19

> > I believe not - assuming you have an index on Sectors.SectorID, which
> > I assume you do.

This was not one of my greater sentences :/