|
Subject:
|
Rowcount from SQL query
|
|
Posted By:
|
delwork
|
Post Date:
|
1/6/2006 1:08:42 PM
|
I need to know the count of how many rows a query is going to return
without actully bringing back all the data to my pc. I know I can use
select count(*) to find how many rows are in a table, but I need to
know how many rows are going to be returned in a complicated query
that has joined multiple tables without actually running the query and
returning the total results because it will be around one million and
many columns. I realize I have to run it to get the number of
rows it will return but I don't want all the columns and rows to come
back as a result. Just the count. For example a query might be:
(select one, two, three, four, five <-- How many rows from this ?
from table1, table2, table3,
where etc, etc, etc.)
I want the count of how many rows will be returned from the query, not a single table. I don't have sqlserver enterprise edition to use the analyzer, I just thought there might be a way of including this whole query between parenthises with some way to bring back just the count. Any ideas?
|
|
Reply By:
|
SqlMenace
|
Reply Date:
|
1/6/2006 1:31:06 PM
|
select count(*)
from table1, table2, table3,
where etc, etc, etc.)
example
select count(*) from employee e join address a on e.id =a.id
where zipcode = 10288
“I sense many useless updates in you... Useless updates lead to fragmentation... Fragmentation leads to downtime...Downtime leads to suffering..Fragmentation is the path to the darkside.. DBCC INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with you" -- http://sqlservercode.blogspot.com/
|
|
Reply By:
|
delwork
|
Reply Date:
|
1/6/2006 1:50:59 PM
|
I get an error when I try that. Then it doesn't like the "." thats used for the table qualifier. I get ther error "-104, ERROR: ILLEGAL SYMBOL ".". SOME SYMBOLS THAT MIGHT BE LEGAL ARE: , FROM INTO (0.07 secs)"
My query looks like this:
SELECT count (*)
dbc.VVVT_TBRSLTS_CODE, dbc1.AUTH_CLNT_IDNO,
dbc1.AUTH_PREVSTOP_DATE, dbc2.ACTV_CLNT_IDNO,
dbc2.ACTV_PRVDTYPE_CODE, dbc2.ACTV_BILLSTAT_CODE,
dbc3.PROF_STAE_CODE, dbc3.PROF_ZIP_CODE, dbc3.PROF_PHON_NUMB,
dbc3.PROF_CNTY_CODE
FROM SCHCVVVT.dbcYVAUTH01 dbc1,
SCHCVVVT.dbcYVvVVT01 dbc,
SCHCVVVT.dbcYVACTV01 dbc2,
SCHCVVVT.dbcYVPROF01 dbc3
WHERE dbc1.AUTH_CLNT_IDNO=dbc.VVVT_CLNT_IDNO
AND dbc2.ACTV_CLNT_IDNO=dbc.VVVT_CLNT_IDNO
AND dbc2.ACTV_PRVD_IDNO=dbc3.PROF_IDNO
|
|
Reply By:
|
gbianchi
|
Reply Date:
|
1/6/2006 1:57:20 PM
|
hi there..
try this...
SELECT count (*)
FROM dbcYVAUTH01 dbc1,
dbcYVvVVT01 dbc,
dbcYVACTV01 dbc2,
dbcYVPROF01 dbc3
WHERE dbc1.AUTH_CLNT_IDNO=dbc.VVVT_CLNT_IDNO
AND dbc2.ACTV_CLNT_IDNO=dbc.VVVT_CLNT_IDNO
AND dbc2.ACTV_PRVD_IDNO=dbc3.PROF_IDNO
HTH
Gonzalo
|
|
Reply By:
|
SqlMenace
|
Reply Date:
|
1/6/2006 1:59:46 PM
|
SELECT count (*)
FROM SCHCVVVT.dbcYVAUTH01 dbc1,
SCHCVVVT.dbcYVvVVT01 dbc,
SCHCVVVT.dbcYVACTV01 dbc2,
SCHCVVVT.dbcYVPROF01 dbc3
WHERE dbc1.AUTH_CLNT_IDNO=dbc.VVVT_CLNT_IDNO
AND dbc2.ACTV_CLNT_IDNO=dbc.VVVT_CLNT_IDNO
AND dbc2.ACTV_PRVD_IDNO=dbc3.PROF_IDNO
“I sense many useless updates in you... Useless updates lead to fragmentation... Fragmentation leads to downtime...Downtime leads to suffering..Fragmentation is the path to the darkside.. DBCC INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with you" -- http://sqlservercode.blogspot.com/
|
|
Reply By:
|
delwork
|
Reply Date:
|
1/6/2006 3:08:11 PM
|
That works. Thanks a lot.
|
