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/
