Solved

Use Record Count

Posted on 2007-04-11
4
616 Views
Last Modified: 2008-01-09
Hi: Can any one please tell me how to i use the recordcount in that query
select clt_ref_no as CFG_ACCOUNT,
(case  
         when r.status_code in ('400','404','407') then 'OVBK'
        when r.status_code in ('210','420','430','470','630','475') then 'OVUN'
        when r.status_code in ('435','438') then 'OVRC'
        when r.status_code in ('450') then 'OVDC'
        when r.status_code in ('487','485') then 'OVFA'
        when r.status_code = '491' then 'OCAC'
        when r.status_code = '510' then 'OSIF'
        when r.status_code='520' then 'OPIF'
        else 'CHECK STATUS'
       END) as REASON,
(SELECT LONGSTR FROM DBTRUDF U WHERE R.DEBTOR_ID=U.DEBTOR_ID AND LABEL='BANKRUPTCYCHAPTER') AS BANKRUPTCYCHAPTER,
(SELECT LONGSTR FROM DBTRUDF U WHERE R.DEBTOR_ID=U.DEBTOR_ID AND LABEL='CASENUMBER') AS CASENUM,
CONVERT(CHAR(10),(SELECT DATE1 FROM DBTRUDF U WHERE R.DEBTOR_ID=U.DEBTOR_ID AND LABEL='FILEDATE'),101) AS BKTFILEDATE,
CONVERT(CHAR(10),(SELECT DATE1 FROM DBTRUDF U WHERE R.DEBTOR_ID=U.DEBTOR_ID AND LABEL='DECEASEDDATE'),101) AS DATEOFDEATH,
    STATUS_DESCR AS COMMENTS,
    substring(DEBT_DESCR,(LOCATE(DEBT_DESCR,':',1)+1), 30) AS ORIGINALACCOUNT
FROM DEBT_VIEW S JOIN DBTR R ON S.DEBT_ID=R.DEBTOR_ID
WHERE CLT_ID LIKE 'IDTC%' AND R.STATUS_CODE>='400'  AND R.STATUS_DATE> '04-11-07'

Thanks.
0
Comment
Question by:mustish1
  • 2
4 Comments
 
LVL 19

Expert Comment

by:grant300
ID: 18890967
I am not sure what you are asking.  Is this in a stored proc and you want to know how many rows were returned?

Immediately after DML statement, the global variable @@ROWCOUNT will tell you how many rows were returned/affected by the operation.

Regards,
Bill
0
 

Author Comment

by:mustish1
ID: 18891122
No its not in a stored procedure. If you please tell me how to i use the count(*) in the query which gives me only a total number of row count in the query?
Thanks.
0
 
LVL 19

Accepted Solution

by:
grant300 earned 500 total points
ID: 18891670
COUNT(*), like the other aggregate functions (MIN, MAX, AVG, etc) only works properly in the context of a query with a GROUP BY clause.

Since you are returning individual rows, the COUNT(*) aggregate does not give you what you want.  Even if you added the GROUP BY in such a way it did not affect your query otherwise, you would simply get "1" for the COUNT(*) on each row.

Depending on what you are using for client software, you could put the COUNT(*) in a COMPUTE clause.  Unfortunately, COMPUTE is a Sybase-only extension to SQL that never gained wide popularity or support so very few tools know what to do with it.

Your best bet is to put the whole thing in a SQL block and return the value of @@ROWCOUNT as a second result set.  For example....

BEGIN
 SELECT clt_ref_no as CFG_ACCOUNT,
     o
     o
  WHERE CLT_ID LIKE 'IDTC%' AND R.STATUS_CODE>='400'  AND R.STATUS_DATE> '04-11-07'

  SELECT @@ROWCOUNT as RowCount
END

Regards,
Bill

0
 
LVL 29

Expert Comment

by:leonstryker
ID: 18892433
If you just want to test the number of rows this query will return you could use this:

SELECT COUNT(*)
FROM DEBT_VIEW S JOIN DBTR R ON S.DEBT_ID=R.DEBTOR_ID
WHERE CLT_ID LIKE 'IDTC%' AND R.STATUS_CODE>='400'  AND R.STATUS_DATE> '04-11-07'

But this would have to be run independent of your original query. You could run them one afer the other by seting the count to a variable, but in effect you are executing the query twice.

Leon
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
How do we check sybase license in ASE 1 2,484
Sybase 15.7 database backup 8 593
Clean up a mailbox 5 146
Why did SAP buy Sybase? 3 143
Owning a franchise can be the dream of a lifetime. It provides a chance for economic growth. You can be as successful as you want.  To make your franchise successful, you need to market it successfully. Here are six of the best marketing strategies …
Moving applications to the cloud or switching services to cloud-based ones, is a stressful job.  Here's how you can make it easier.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now