Hello Bill,
Try this:
SELECT CAST(COUNT(DISTINCT Color_Code) AS Char(3)) "COLOR_CODE" FROM MYTABLE
UNION
Select Distinct Color_Code From MyTable
Saturday, February 02, 2002, 1:35:41 AM, you wrote:
BT> I was looking for a way to query SQL Server via ODBC from
BT> MapBasic and get the number of rows produced by the query.
BT> Apparently the only ways to do this with MapBasic are:
BT> 1.) Run the query twice; once to return count(*), and a second
BT> time to retrieve the data.
BT> 2.) Download the table into MapInfo and use TableInfo()
BT> Neither of these solutions are great because they involve either
BT> working the server twice as much or incurring the overhead of a
BT> download (which is slow via ODBC.) Also count(*) with 'Group By'
BT> doesn't work in SQL server the way it does in MapInfo. What I
BT> really wanted was the total number of unique items, not a count
BT> of how many of each there are. So I need to use the select
BT> distinct... clause (which doesn't allow count(*)).
BT> The following should illustrate the problem. This fictional SQL
BT> Server table has a column called 'Color_Code' which can contain
BT> values like 'Red', 'Green', 'Blue', etc. Records can be assigned
BT> any string denoting a color. What I want is a count of how many
BT> unique color_codes are in the database.)
BT> This query does the job in SQL Server's Query Analyzer:
BT> Select distinct Color_Code from MyTable
BT> --And this (suggested by David Cautley) gives me the count:
BT> Select @@ROWCOUNT
BT> However, from MapInfo I can't get the count. (Following this is a
BT> sql script to build table in question) Can I get a row count in
BT> one SQL query? Here's a MapBasic example, but I don't know how to
BT> access that row count:
BT> Dim h, hdbc, nCount, nStat As Integer
BT> Dim sColor_code As String
BT> 'Connect to Database "Test"
BT> hdbc = SERVER_CONNECT("ODBC", "DLG=1")
BT> 'Execute SQL query
BT> h = SERVER_EXECUTE (hdbc,
BT> "Select Distinct Color_Code From MyTable; Select @@ROWCOUNT")
BT> Server h Bind Column 1 To sColor_code, nStat
BT> Server h Fetch First
BT> Do While NOT SERVER_EOT(h)
BT> Print sColor_Code
BT> Server h Fetch Next
BT> Loop
BT> Server h Close
BT> Server hdbc Disconnect
BT> This will build the Test database in SQL Server, produce the list
BT> I want with a row count:
BT> Create Database Test
BT> Go
BT> Use test
BT> Create Table MyTable (
BT> ID Integer NOT Null PRIMARY KEY,
BT> Color_Code Varchar(25))
BT> Insert Into MyTable (ID, Color_Code) Values (1, 'Red')
BT> Insert Into MyTable (ID, Color_Code) Values (2, 'Red')
BT> Insert Into MyTable (ID, Color_Code) Values (3, 'Blue')
BT> Insert Into MyTable (ID, Color_Code) Values (4, 'Red')
BT> Insert Into MyTable (ID, Color_Code) Values (5, 'Blue')
BT> Insert Into MyTable (ID, Color_Code) Values (6, 'Green')
BT> Select Distinct Color_Code From MyTable; Select @@ROWCOUNT
BT> -- end script
BT> If anyone knows how to this, I'd sure like to know, but thanks
BT> for the help so far!
--
Best regards,
Edward Smirnov
microOLAP Technologies LLC
[EMAIL PROTECTED]
http://www.microolap.com/gis/
_______________________________________________________________________
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.
