Select from table using array variable

[alfie]

Hi all,

I want to select objects from a table where the Object_ID (float field
in table) matches variables in an array. In some cases the array will
contain only one record, in other cases the array may contain several
records (corresponding to different objects in the table).

Any ideas about the easiest way to do this?

Thanks
A

[esrispy]

select * from your_table where Object_ID = yourArray(i) into
matching_array_recs browse * from matching_array_recs

that should do it

[Peter Horsbøll Møller]

A,

 

Here are some ideas.

 

1. Use the Run Command Statement and build the SQL Select statement on the fly

With this one you need to be careful if you have really many records in the array.

If there is too many the Select statement can get too complex

 

For i = 1 To Ubound(arrIDs)

   If i = 1 Then

      sCond = arrIDs(i)

   Else

      sCond = sCond & "," & arrIDs(i)

Next

Run Command "Select * From MYTABLE Where Object_ID In (" & sCond & ") Into MATCH__OBJECT_IDS NoSelect"

2. Insert the value into a temporary table and use a sub select to match the records.

This will work even with many records.

 

Create Table TEMP__IDS (OBJECT_ID Integer) File TempFileName$("")

For i = 1 To Ubound(arrIDs)

   Insert Into TEMP__IDS (OBJECT_ID) Values (arrIDs(i))

Next

Select * From MYTABLE Where Object_ID In (Select OBJECT_ID From TEMP__IDS) Into MATCH__OBJECT_IDS NoSelect
Drop Table TEMP__IDS

 

 

Peter Horsbøll Møller
Pitney Bowes Business Insight - MapInfo

 

2009/11/25 alfie <alfred.w...@gmail.com>

[Driver, Greg 9434]

If you want to match multiple IDs then you'd have to build the SQL select as you would in MapInfo:

Select * from your_table where object_id = n OR object_id = n OR object_id = n

So that means building the SQL as string and then adding on the condition tests based on the size of the array. This should work regardless of the array size:

Dim sqlcmd as string
Dim x, cnt as integer
Dim array_var() as float

sqlcmd = "Select * from Tablename Where "

cnt = UBOUND(array_var)

For x=1 to cnt
sqlcmd = sqlcmd + " col_name = " + array_var(x)
If x <> cnt Then
sqlcmd = sqlcmd + " OR "
End If
Next

sqlcmd = sqlcmd + " into Temp_Tab"

Print sqlcmd

Run command sqlcmd

HTH

Greg Driver

System Administrator
Applications Support
ICT
Surrey Police
NOT PROTECTIVELY MARKED

--

You received this message because you are subscribed to the Google Groups "MapInfo-L" group.
To post to this group, send email to mapi...@googlegroups.com.
To unsubscribe from this group, send email to mapinfo-l+...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/mapinfo-l?hl=en.


Information about this E-mail
This email and any files or attachments with it are intended solely for the use of the individual(s) or organisation(s) to whom it is addressed.
It may contain information that is confidential or subject to legal and/or professional privilege.
If you have received this email in error please notify the sender and delete it including any files or attachments from your e-mail account or computer.
Any opinions expressed in this email are those of the individual and not necessarily those of Surrey Police.
Surrey Police monitor incoming and outgoing e-mail.