How do I use the results of count(*)=0

85 pts.
Tags:
SELECT statement
SQL
I am using SQL to code a select statement. If the result of the statement = count(*)=0, I want to put a message out on a report.

Answer Wiki

Thanks. We'll let you know when a new response is added.

More information is needed here.
How you assign the result of a query to a variable, or how you put a message on a report, depends on the programs/platform you are using.

I hope this answers your question.
The platform is LINX. I am writing in SQL. Each time I encounter an error I am writing to an error Log table. At the end of the processing, I selecting everything from that table where the error_date is equal to the sysdate. What I wanted was when there are NO errors for that run, instead of a blank report, Iwanted to write ‘** NO ERRORS FOUND **.
I don’t believe I am assigning a result of the query to a variable. In fact, that’s 1 of the things I’ve been trying to do & then query the variable so that when it’s 0 I can put out the message. Everything I’ve tried has come back with an error..

–rem creates an error report/file of those errors just identified
spool c:\student\refund.errors.from.isis.csv
DEFINE _EDITOR= pico
SET heading off
SET feedback off
SET echo off

SELECT ERROR_ID, ERROR_PIDM, ERROR_DATE, ERROR_REASON, ERROR_USER_ID
FROM error_log
WHERE ERROR_DATE LIKE TRUNC(SYSDATE);
/
commit;
spool off

——–

Thanks for clarifying this.

I think you could use another query to count the errors.

Something like this:
<pre>
– display a message when no errors are found, or null when errors exist
SELECT decode(count(*),0,’** NO ERRORS FOUND **’,null)
FROM error_log
WHERE ERROR_DATE LIKE TRUNC(SYSDATE);

– and then the query that shows the actual errors
SELECT ERROR_ID, ERROR_PIDM, ERROR_DATE, ERROR_REASON, ERROR_USER_ID
FROM error_log
WHERE ERROR_DATE LIKE TRUNC(SYSDATE);</pre>

Hope this helps.

Discuss This Question: 2  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Sherryborden
    I'd like to thank you for the help. It's exactly what I wanted. Sherry
    85 pointsBadges:
    report
  • carlosdl
    I'm glad it worked. Best regards,
    69,920 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following