using a string variable as a table name in an sql select

[d3an79]

Hi,

As from my previous post I am still trying to write a program to
count the number of objects that fall whithin a user created boundry
over multiple layers that are selectable in the current mapper window.

So far i have populated a global array with the names of the layers
with which I want to work with and now want to test if I can get them
to work in a sql query.

I created the initial query in mapinfo then copied the mapbasic
window and edited it to use the layer that I want but it keeps
flagging up the error;

(boundSelect2.mb:41) Unrecognised command: ..

which relates to the line;

Select * From layerNames(1), Cosmetic1 where Cosmetic1.Obj Contains
layerNames(1).Obj into result

If I hardcode the name into the query it works fine and I have also
printed layerNames(1) to check that it is the same.

Here is an exceprt from my code;

Sub ButtonPrompt

' creates a string array with the layers that need to be searched
against
Call getLayerNames

' test function - browse a table showing the objects in
layerNames(1) that are within the cosmetic layer
Select * From layerNames(1), Cosmetic1 where Cosmetic1.Obj Contains
layerNames(1).Obj into result
Browse * From result

End Sub

I think the problem lies when using the 'layerNames(1).obj' as if i
substitute the hardcode name in for this bit it works fine.

Is the problem that i'm using a string for a table name?

Any help that you can give would be greatly appreciated.

Thanks in advance.

[e.j.h.polle]

Hi d3an79,

Something like this should do the trick:

Dim sQuery as String

sQuery = "Select * From " + layerNames(1) + ", Cosmetic1 where
Cosmetic1.Obj Contains " + layerNames(1) + ".Obj into result"
Run Command sQuery

HTH,

Egge-Jan

[d3an79]

Thanks Egge-Jan,

That worked great.

Is it possible for you to explain what i was doing wrong for future
reference?

Also, could you explain why the code you supplied worked?

Sorry for being a pain.

Thanks.

[e.j.h.polle]

Hi d3an79,

It's good to hear that the trick worked :-)

But OK, you are right: I didn't do much to explain it. But here we go:

An SQL statement with variables does not work, as you already
discovered yourself.

So you have to take the following steps:

Step 1: declare a string variable
(Dim sQuery as String)

Step 2: populate this variable with your SQL statement (and during
this process you can use variables)
(sQuery = "Select * From " + layerNames(1) + ", Cosmetic1 where
Cosmetic1.Obj Contains " + layerNames(1) + ".Obj into result")

Step 3: give the resulting string as input to the Run Command
statement
(Run Command sQuery)

More information about the Run Command statement - including examples
- to be found in the MapBasic Help and the MapBasic Reference Guide.

HTH,

Egge-Jan

[d3an79]

Thanks again, that's really helped out.

[RF]

Hello Egge-Jan,

I've been banging my head with this for a few hours, then found the solution here but I'm not sure if I'm doing it correctly.

I have a table with column EntityName, this table has few hundred records with different names for the company. I wanted to create different tables for the different companies. So I did a select all and group them by EntityName to then do a loop to read the company name and then select all the records with the same name and then save them in a different table with the respective EntityName. Below the part of the code with the loop:

Select * From Paths group by EntityName into Operators

Commit Table Operators as "C:\Users\RFC\Documents\FCC\MW\ULS Imports\Operators.tab"

Browse * From Operators

Fetch First from Operators

Do While Not EOT (Operators)

Operator=Operators.EntityName

Note Operator

sQuery="Select * from Paths where EntityName="+Operator+"into List"

Run Command sQuery

Commit Table List as "C:\Users\RFC\Documents\FCC\MW\ULS Imports\"+Operator+"-MW Paths.tab"

Fetch Next from Operators

Loop

From the Note command I've confirmed the name is being read correctly but keep getting the following error:

"Variable of Field Puerto not defined"

Not sure what is going on, but Puerto is the beginning of the EntityName read. I've tried writing the sQuery in different ways but keep getting errors.

Will appreciate any assistance!

Thanks,

Roger

[Tony Pilkington]

in the squery= line, "into List" should be " into List"

--
--
You received this message because you are subscribed to the
Google Groups "MapInfo-L" group.To post a message to this group, send
email to mapi...@googlegroups.com
To unsubscribe from this group, go to:
http://groups.google.com/group/mapinfo-l/subscribe?hl=en
For more options, information and links to MapInfo resources (searching
archives, feature requests, to visit our Wiki, visit the Welcome page at
http://groups.google.com/group/mapinfo-l?hl=en

---
You received this message because you are subscribed to the Google Groups "MapInfo-L" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mapinfo-l+...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

No virus found in this message.
Checked by AVG - www.avg.com
Version: 2014.0.4716 / Virus Database: 4007/8075 - Release Date: 08/21/14

[RF]

Thank you Tony!