Performing multiple selection using MapBasic?

Question

I am new to MapBasic programming.

I am trying to make multiple selection from a table where the ID number exist in my array and save all the values into another table which called results. ARRContainsValue() is a function which checks the values. This function works well. My only problem is the table result only contains the last selection, not all of them. Can anyone help me with this.

fetch first from structurepoint
for i=1 to tableinfo(structurepoint,8)
    if ARRContainsValue(Route_IDs_Used(),route.id)=1 then
        select * From structurepoint where rowid=i into results noselect
    end if
    fetch next from structurepoint
next

Answer 1

You could take a slightly different approach with this. If you create a temporary table containing all your array values, let's call it RouteIDs, then you can use just one SQL select which may be more efficient than selecting each row independently. So your code could look something like this.

Create Table RouteIDs (ID Integer) File "C:\Temp\RouteIDs.TAB"
Open Table "C:\Temp\RouteIDs.TAB" as RouteIDs

for i=1 to tableinfo(structurepoint,8)
    if ARRContainsValue(Route_IDs_Used(),route.id)=1 then
        Insert into RouteIDs (ID) Values (i)
    end if
next

Select * from structurepoint where rowid in(Select ID from RouteIDs) into Results

Drop table RouteIDs

Also, you should be careful using rowID to identify records. If a table has deleted rows and has not been packed you may find that you end up selecting the wrong records as the table index will not have been updated. It is generally best to generate a static ID column and use this to match records rather than rowID.