Hi everybody - COUNT QUERY PL - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Hi everybody - COUNT QUERY PL

Author

Topic

nssjari
Starting Member

46 Posts

Posted - 2005-11-12 : 01:17:30

I have a query as below:

---

select distinct
csd1.csd_orig + csd1.csd_subj + csd1.csd_type + csd1.csd_numb + csd1.csd_revi as "Document Reference No.",
csd1.csd_labl + ' ' as "Description",
csd1.csd_issu as "Docu Dt",
csd1.csd_altr as "Alternate Number",
csd2.csd_altr as "Transmittal Reference No.",
csd2.csd_issu as "TIssu Dt",
trd1.trd_recd as "TRecd Dt",
apr1.apr_reqd as "RReqd Dt",
case when apr1.apr_stat is null then 'Unknown / Pending / No Reply' else apc1.apc_libe end as "Document Status",

csd3.csd_altr as "Reply Reference No.",
csd3.csd_issu as "RIssu Dt",
trd2.trd_recd as "RRecd Dt"

from
E0437csd csd1,
E0437tra tra1,
E0437csd csd2,
E0437trd trd1,
E0437apr apr1,
E0437apc apc1,

E0437tra tra2,
E0437csd csd3,
E0437trd trd2

where
csd1.csd_orig = 'BS' and
csd1.csd_subj like '%WN1%' and
csd1.csd_type = 'D' and
tra1.tra_drgn = csd1.csd_orig + csd1.csd_subj + csd1.csd_type + csd1.csd_numb + csd1.csd_revi and
tra1.tra_part = 'PARSO' and
tra1.tra_type = 'A' and
csd2.csd_orig + csd2.csd_subj + csd2.csd_type + csd2.csd_numb + csd2.csd_revi = tra1.tra_trno and
trd1.trd_trno = tra1.tra_trno and
trd1.trd_cc = '0' and
trd1.trd_part = 'PARSO' and
apr1.apr_docu = csd1.csd_orig + csd1.csd_subj + csd1.csd_type + csd1.csd_numb + csd1.csd_revi and
(apr1.apr_stat = apc1.apc_code or apr1.apr_stat = '') and

tra2.tra_drgn = csd1.csd_orig + csd1.csd_subj + csd1.csd_type + csd1.csd_numb + csd1.csd_revi and
tra2.tra_part = csd1.csd_ownr and
csd3.csd_orig + csd3.csd_subj + csd3.csd_type + csd3.csd_numb + csd3.csd_revi = tra2.tra_trno and
trd2.trd_trno = tra2.tra_trno

order by
csd1.csd_orig + csd1.csd_subj + csd1.csd_type + csd1.csd_numb + csd1.csd_revi

---

I need to count the resulting records of the query.
How do i have to do this, could you please tell me ...
I am new to SQl and I tried but could not solve it ...

Thank You :)

Jari
Computer Engg

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-11-12 : 07:33:29

One way is to:
select @@rowcount
as the next statement after your query.

Be One with the Optimizer
TG

Kristen
Test

22859 Posts

Posted - 2005-11-12 : 08:50:22

And possibly another is to first pre-select the PKs for the matching rows into a temporary table, then COUNT(*) them [and perform any other processing], then make the SELECT statement to retrieve the actual row data from the tables, JOINing to your temporary table.

This route allows:

a) The COUNT(*) to be returned before the actual data's resultset
b) We do this sometimes so that where only one row is matched we return the "record card data" for the matched row, otherwise we return the "summary data" for the matching rows for the application to display a dill-down summary list.

Kristen

nssjari
Starting Member

46 Posts

Posted - 2005-11-14 : 02:01:17

Thanks a lot to TG & Kristen your kind reply ...

Life is beautiful ... When you smile ...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources