#1  
Old 5th February 2004, 02:37 AM
Stefan Paege
 
Posts: n/a
Default 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


  #2  
Old 5th February 2004, 04:00 AM
Per Larsen
 
Posts: n/a
Default Re: Some SQL questions


"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



  #3  
Old 5th February 2004, 07:10 PM
Stefan Paege
 
Posts: n/a
Default Re: Some SQL questions

> 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


  #4  
Old 6th February 2004, 12:45 AM
Per Larsen
 
Posts: n/a
Default Re: Some SQL questions


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


This was not one of my greater sentences :/




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
questions on Chat Hannes Danzl[NDD] nexusdb.public.support.portal 5 14th October 2010 08:52 AM
Portal Questions Dennis Landi nexusdb.public.support.portal 9 16th February 2010 02:41 AM
Some Questions Danijel Tkalcec nexusdb.public.support.portal 2 3rd October 2009 12:46 AM
Some questions Alessandro Romano nexusdb.public.support.thirdparty 10 6th August 2005 03:36 AM
Questions... Curt nexusdb.public.support.odbc 1 30th May 2004 09:57 AM


All times are GMT +11. The time now is 10:59 PM.


Powered by vBulletin® Version 3.6.8
Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.