RE: convert empty table to a message

[Helen rwulfjeq]

Thanks, That's a good idea.

 

  Greg Solomon <[EMAIL PROTECTED]> wrote:

Hi Helen

 

Well, to keep it really really really simple ...

 

spool ...

SELECT DECODE(COUNT(*), 0, 'Hello there.  Today's batch run contains no reports, so that makes your morning easy, doesn't it.  If you have nothing else to do, please feel free to drop by for a coffee around elevenish, it would be great to catch up.  By the way, did you know that nine out of ten snowmen die in heat related incidents ?')

FROM    VW.C_EXPORT_CYCLE, C_REPORT_ID, V$DATABASE
WHERE   C_REPORT_ID.REPORT_ID = C_EXPORT_CYCLE.REPORT_ID;

SELECT  RPAD(RTRIM(LTRIM(V$DATABASE.NAME))||'_'||  C_REPORT_ID.REP_FILE_NAME, 30),
 NAME,
 ADDRESS,
 FLOOR,
 ROOM
FROM    VW.C_EXPORT_CYCLE, C_REPORT_ID, V$DATABASE
WHERE   C_REPORT_ID.REPORT_ID = C_EXPORT_CYCLE.REPORT_ID;

spool off

-----Original Message-----
From: Helen rwulfjeq [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, 27 June 2001 23:50
To: Multiple recipients of list ORACLE-L
Subject: convert empty table to a message

Hello,

I have a file to contain info about the reports detailed info (stored in a temp table in the DB) we created every night. Such as: report ID, report_name...  However, sometimes there is no report generated overnight. In this case, the temp table would be empty. But this file will still spool out anyway for user to review the information about the report request.

My question is: When the table is empty, is there a way that I can convert the empty lines in the file like "no report generated".  I tried DECODE, but doesnot work because there is no row return from the table.

spool ...
SELECT  RPAD(RTRIM(LTRIM(V$DATABASE.NAME))||'_'||
  DECODE(C_REPORT_ID.REP_FILE_NAME, NULL, 'NO REPORT CREATED', ' ', 'NO REPORT CREATED'), 30),
 DECODE (C_REPORT_ID.REPORT_ID, NULL, 'NO REPORT CREATED', ' ', 'NO REPORT CREATED'),
 NAME,
 ADDRESS,
 FLOOR,
 ROOM
FROM    VW.C_EXPORT_CYCLE, C_REPORT_ID, V$DATABASE
WHERE   C_REPORT_ID.REPORT_ID = C_EXPORT_CYCLE.REPORT_ID;
spool off

Do you have any suggestions?

Thanks in advance

Helen

---

Do You Yahoo!?
Get personalized email addresses from Yahoo! Mail - only $35 a year!
http://personal.mail.yahoo.com/

---

Do You Yahoo!?
Get personalized email addresses from Yahoo! Mail - only $35 a year!
http://personal.mail.yahoo.com/