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]