RE: convert empty table to a message

Hi
Helen
 
Well,
to keep it really really really simple ...
<SPAN

class=621505908-28062001> 
spool
...
<SPAN

class=621505908-28062001>SELECT <SPAN
class=621505908-28062001>DECODE(COUNT(*), 0, 'Hello there.  Today's batch run contains no reports,<FONT color=#0000ff face=Arial size=2> 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 ?')
<SPAN

class=621505908-28062001>FROM    VW.C_EXPORT_CYCLE, C_REPORT_ID, V$DATABASEWHERE   C_REPORT_ID.REPORT_ID = C_EXPORT_CYCLE.REPORT_ID;<FONT color=#0000ff face=Arial size=2>
<SPAN

class=621505908-28062001>SELECT 
RPAD(RTRIM(LTRIM(V$DATABASE.NAME))||'_'||  C_REPORT_ID.REP_FILE_NAME, 30), NAME, ADDRESS, FLOOR, ROOMFROM   
VW.C_EXPORT_CYCLE, C_REPORT_ID, V$DATABASEWHERE   C_REPORT_ID.REPORT_ID = C_EXPORT_CYCLE.REPORT_ID; spool
off

  <FONT face=Tahoma
  size=2>-----Original Message-----From: Helen rwulfjeq   [mailto:lannyue_at_yahoo.com]Sent: Wednesday, 27 June 2001   23:50To: Multiple recipients of list ORACLE-LSubject:   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, ROOMFROM      VW.C_EXPORT_CYCLE, C_REPORT_ID, V$DATABASEWHERE     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!<A
  href="http://personal.mail.yahoo.com/?.refer=tagline">http://personal.mail.yahoo.com/ Received on Thu Jun 28 2001 - 06:04:56 CDT