![]() | ![]() Products ![]() ![]() ![]() ![]() |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 7 of 7 total |
![]() |
Wed, Jun 25 2014 5:25 PM | Permanent Link |
Matt Gregory | What's the recommended way to get the number of records? So far I have:
query->SQL->Clear(); query->SQL->Add("SELECT COUNT(*) FROM table"); query->Active = true; int count = query->??? Thanks! |
Wed, Jun 25 2014 9:25 PM | Permanent Link |
Jeff Cook Aspect Systems Ltd | In Delphi:-
qry.SQL := 'SELECT AnyField FROM Table"; qry.Open; count := qry.RecordCount; qry.Close; OR qry.SQL := 'SELECT COUNT(*) FROM table"; qryOpen: count := qry.Fields[0].AsInteger; qry.Close; -- Jeff Cook Aspect Systems Ltd www.aspect.co.nz <Matt Gregory> wrote in message news:684FC070-6CE3-48CC-9C2A-03BAC115C72B@news.elevatesoft.com... > What's the recommended way to get the number of records? So far I have: > > query->SQL->Clear(); > query->SQL->Add("SELECT COUNT(*) FROM table"); > query->Active = true; > int count = query->??? > > Thanks! > |
Thu, Jun 26 2014 4:01 AM | Permanent Link |
Matthew Jones | Jeff Cook wrote:
> SELECT COUNT(*) FROM table Me, I usually use a single field, in the hope that it reduces the amount of data that must be looked at (and assuming that NULL is not relevant), and then give it a name so I can access it in the usual way. Thus: SELECT COUNT(MyRecordID) AS MyRowCount FROM table (I never actually use "My..." except in illustrations. In real tables, I use a sort of hungarian notation. Every table is two words, like "CustomerAccount" and every field in that table is caFirstName or caCustomerID. This ensures that my SQL is always right and things like joins are on "WHERE (caCustomerID = coCustomerID)" so I don't accidentally join on "CustomerID = CustomerID" and get the wrong table. But maybe that's just me...) -- Matthew Jones |
Thu, Jun 26 2014 8:30 AM | Permanent Link |
Matt Gregory | "Jeff Cook" wrote:
In Delphi:- qry.SQL := 'SELECT AnyField FROM Table"; qry.Open; count := qry.RecordCount; qry.Close; OR qry.SQL := 'SELECT COUNT(*) FROM table"; qryOpen: count := qry.Fields[0].AsInteger; qry.Close; I would like to use that second method, but the line (in C++): qry->Fields[0].AsInteger gives me an error: E2316 'AsInteger' is not a member of 'TFields' |
Thu, Jun 26 2014 8:38 AM | Permanent Link |
Matt Gregory | >"Matthew Jones" wrote:
> >Jeff Cook wrote: > >> SELECT COUNT(*) FROM table > >Me, I usually use a single field, in the hope that it reduces the >amount of data that must be looked at (and assuming that NULL is not >relevant), and then give it a name so I can access it in the usual way. Usually databases are optimized for COUNT(*) on a table. If you call COUNT() on a particular field and it's nullable, then it has to scan through all the rows and count only the non-null ones. >Thus: > >SELECT COUNT(MyRecordID) AS MyRowCount FROM table > >(I never actually use "My..." except in illustrations. In real tables, >I use a sort of hungarian notation. Every table is two words, like >"CustomerAccount" and every field in that table is caFirstName or >caCustomerID. This ensures that my SQL is always right and things like >joins are on "WHERE (caCustomerID = coCustomerID)" so I don't >accidentally join on "CustomerID = CustomerID" and get the wrong table. >But maybe that's just me...) I like that convention! |
Thu, Jun 26 2014 8:47 AM | Permanent Link |
Matt Gregory | I got it! You have to say:
qry->Fields[0][0]->AsInteger I guess the first index is for the row and the second is for the column. |
Thu, Jun 26 2014 8:51 AM | Permanent Link |
Matthew Jones | Matt Gregory wrote:
> Usually databases are optimized for COUNT(*) on a table. If you call > COUNT() on a particular field and it's nullable, then it has to scan > through all the rows and count only the non-null ones. Useful to know. Thanks. -- Matthew Jones |
This web page was last updated on Wednesday, August 14, 2024 at 07:31 PM | Privacy Policy![]() © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? ![]() |