Use

SELECT Count(*) as NumStations from ....


Lindsay

-----Original Message-----
From: Eric Tishler [mailto:etishler@...]
Sent: 14 April 2004 14:05
To: IBObjects@yahoogroups.com
Subject: [IBO] Trouble with Count(*)

Hi,

I am a bit of a neophyte with SQL ...

I am trying to get the count for a number of records matching certain
criteria.

I looked in the LANGREF.PDF that comes with and the syntax shows either:

SELECT COUNT(*) INTO :field FROM TABLE WHERE condition;

Or

SELECT COUNT(*)FROM TABLE WHERE condition INTO :field;

In my actual Delphi (6) code using IBObjects I have:

qrySiteInfo := TIBOQuery.Create(Application);
qryStationInfo := TIBOQuery.Create(Application);

with qrySiteInfo do
begin
// prepare the query to get each site name
Active := False;
SQL.Clear;
SQL.Add('SELECT "SiteID" from "Sites"');

// run the query, open the DataSet
Active := True; Open; First;

// loop to process each site record
while not Eof do
begin
// get the SiteID
sSiteID := FieldByName( 'SiteID' ).AsString;

with qryStationInfo do
begin
// prepare query to get number of stations at current site
Active := False;
SQL.Clear;
SQL.Add('SELECT Count(*)');
SQL.Add(' FROM "StationInfo"');
SQL.Add(' WHERE "SiteID" = :SiteID AND "ActivateStation" = 1');
SQL.Add(' INTO :NumStations');

ParamByName('SiteID').AsString := sSiteID;

// run the query, open the DataSet
Active := True; Open; First;

But I get an SQL error (Invalid token INTO line 4, char 2) when I go to
execute the ParamByName statement below the SQL.Add statements

I have also tried alternate SQL syntax as shown below. But they all
produce the same error. Ideally I want to get the count for the number
of records that match the SQL statement I am having problems with.

// alternate #1
SQL.Add('SELECT Count(*)');
SQL.Add(' FROM "StationInfo"');
SQL.Add(' WHERE "SiteID" = :SiteID AND "ActivateStation" = 1');
SQL.Add(' INTO "NumStations"');

// alternate #2
SQL.Add('SELECT Count(*) INTO :NumStations');
SQL.Add(' FROM "StationInfo"');
SQL.Add(' WHERE "SiteID" = :SiteID AND "ActivateStation" = 1');

// alternate #3
SQL.Add('SELECT Count(*) INTO "NumStations"');
SQL.Add(' FROM "StationInfo"');
SQL.Add(' WHERE "SiteID" = :SiteID AND "ActivateStation" = 1');

Can somebody help me with this?

Thank you,

Eric

Eric Tishler
Software Architect
Resolute Partners, LLC
Phone: 203.271.1122
Fax: 203.271.1460
etishler@...



[Non-text portions of this message have been removed]



________________________________________________________________________
___
IB Objects - direct, complete, custom connectivity to Firebird or
InterBase
without the need for BDE, ODBC or any other layer.
________________________________________________________________________
___
http://www.ibobjects.com - your IBO community resource for Tech Info
papers,
keyword-searchable FAQ, community code contributions and more !

Yahoo! Groups Links





---
Incoming mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.648 / Virus Database: 415 - Release Date: 31/03/2004


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.648 / Virus Database: 415 - Release Date: 31/03/2004