Excluding a field from an SQL

Skip to first unread message

Reg Halstead

Dec 11, 2022, 8:09:55 PM12/11/22
to MapInfo-L
Hi all.

Does anyone know if there's a way to specify which fields to exclude from an SQL rather than which fields to include?

For example, let's say I have 10 fields and I want to only include the first 9 in my SQL, then I could write the following:

Select COL1,COL2,COL3,COL4,COL5,COL6,COL7,COL8,COL9 from my_table where ...

However, a way to specify "all but col10" would be very handy for me and would speed some up my loops tremendously by not having to make extra copies of large files.

I'm hoping there's something very simple along these lines:

Select * not col10 from my_table where ...
Select *,-col10 from my_table where ...

Many thanks in advance,

Uffe Kousgaard

Dec 12, 2022, 5:01:55 AM12/12/22
to mapi...@googlegroups.com

I have wanted to do something like that too, but SQL doesn't support it.
Here is a small mapbasic application, which will do something like that and perhaps you can turn it into a function you can call from your application.

Uffe Kousgaard

include "mapbasic.def"

dim i as integer
dim s,t,exclude_field as string

exclude_field = ucase$("fieldB")
s = ""
for i = 1 to tableinfo(test_table,TAB_INFO_NCOLS)
  t = ucase$(columninfo(test_table,"COL"+(str$(i)),COL_INFO_NAME))
  if t<>exclude_field then
    if s="" then
      s = t
      s = s+","+t
    end if
  end if

t = "select "+s+" from test_table"
run command t
browse * from selection
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:
For more options, information and links to MapInfo resources (searching
archives, feature requests, to visit our Wiki, visit the Welcome page at

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.
To view this discussion on the web visit https://groups.google.com/d/msgid/mapinfo-l/4a753beb-a3fa-4c51-bcb9-26a8b82c2112n%40googlegroups.com.

Reg Halstead

Dec 19, 2022, 6:27:50 AM12/19/22
to MapInfo-L
Sorry about the delay. That was very useful. I hadn't considered using "run command" for an SQL so that's going to help with other scripts I need to write as well. Thanks a lot.
Reply all
Reply to author
0 new messages