In a message dated 98-07-14 15:08:38 EDT, you write:
The SELECT window should just say “COUNT(*)”. “TABLE1” should go in the
List of Tables (click the Tables… button).
Beware: this will only work if all of the objects selected in the query are from Table1. If you select objects from Table1 and Table2, then this COUNT object will actually return the number of rows resulting from the join between Table1 and Table2.
Erich Hurst
Compaq Computer Corporation
Hi, Erich! It’s been a while since we’ve heard from you… you’ve been missed.
Your comments about the dangers of a COUNT object are right on. I ahve a couple of additional ideas about the use of COUNT(*) as an object…
You can use the COUNT(*) as the selection without specifying a table. That will, as you say, count the number of rows returned by the combination of tables in the query. The advantage is that you count rows from any combination of tables, where if you specify a table you are automatically including that table in the SQL.
If you leave out the table clause then the object will not parse, but will be fully functional as long as you use the object with at least one other table- based object.
If you need to count something specific from a particular table, then you can use the DISTINCT keyword as a part of the count. Imagine a table with Employees (EMP) and a table with Trips (TRIP). Assume a one - many relationship between EMP and TRIP. If you wanted to count Employees - and get the right answer - even if you used a trip object in the query, then create the Employee Count object as:
COUNT (DISTINCT EMP.EMPLOYEE_ID)
That will ensure that the number of employees is correct. If the original user (quoted below) is using a database with a ROWID feature then you don’t need to use a primary key; use:
COUNT (DISTINCT EMP.ROWID)
…instead. Oracle has this feature, and I believe that later versions of Sybase do as well. Depending on the database engine, this could be the most efficient method of counting… even better than a primary key.
Regards,
Dave Rathbun
Integra Solutions
www.islink.com See you in Orlando in '98!
Hey Folks…
I’m creating an object that will allow the count of all rows in a table. In the definition tab of the edit properties of object1, I have in the select window: count(*) from table1. When I try to parse it, I get an error message of THE USE OF A RESERVED WORD “FROM” FOLLOWING “” IS NOT VALID. Is there another way to derive the total number of rows from a table? Thanks…
Michael Dempsey
Manager, Data Base Administration
University of North Carolina Hospitals
Listserv Archives (BOB member since 2002-06-25)