I had asked how to determine the number of rows generated from a
remote database SQL query in which I needed to select a unique
list of the values contained within a particular field. (See
previous messages under this subject for a script that creates an
example table of multiple "Color Codes" in SQL Server.)
A pure SQL solution came from Jeff Koncz (Jeff is the guy at
Directions Magazine who keeps our MapInfo-L server machine
running).
Submit this via the Server_Execute() function:
SELECT DISTINCT Color_Code,
(SELECT COUNT(DISTINCT Color_Code)
FROM MyTable) AS numrows
FROM MyTable
This works by producing a unique list of values from the
Color_Code field with a field called numrows that contains the
full row count in every record. An example result might look
like:
Color_Code numrows
-----------|--------
Red 3
Green 3
Blue 3
A better solution (for those in the Windows environment) came
from the MapBasic online help (Yes, one must RTFM, but that
doesn't do any good until one understands what TFM is telling
you!)
The MB function Server_GetOdbcStmt() provides a handle to the
ODBC32 API functions which really extend what you can do with MB
Remote database SQL. The example in the help shows the use of the
SQLRowCount() API function, which reports the number of rows
produced from the last SQL query. This works perfectly, so it's
what I'll use.
Thanks for the help everyone!
--
- Bill Thoen
------------------------------------------------------------
GISnet, 1401 Walnut St., Suite C, Boulder, CO 80302
tel: 303-786-9961, fax: 303-443-4856
mailto:[EMAIL PROTECTED], http://www.gisnet.com/
------------------------------------------------------------
_______________________________________________________________________
List hosting provided by Directions Magazine | www.directionsmag.com |
To unsubscribe, send e-mail to [EMAIL PROTECTED] and
put "unsubscribe MapInfo-L" in the message body.
