| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re:RE : Displaying Title When No Data Returned
Many of you replied with the same (or at least similar) solution.
Unfortunately, it does not give me the desired results. This tells me that I
probably was not clear enough on my request.
Here is what I want.
If the query gives back data, then I want the title (which has a page number on it) to display normally, (with the appropriate page numbers, etc.). If the query does not give back data, then I want the title to display (with page 1).
The results I get with the solution many of you gave is correct ONLY if there is NO DATA. However, if there IS DATA, then the title will display once with NO DATA and then again with the data.
AND for the person who says "just turn the title off before you do the real query", then I will only get the title on the first page of the report, which is not acceptable to our users.
However, I believe I have come up with a solution to the problem. The solution is:
1) Define a "bogus" column with the NOPRINT option 2) Issue the statement BREAK ON REPORT 3) Issue the statement COMPUTE COUNT OF bogus ON REPORT 4) Add the "bogus" column to your report
For example, if I do the following:
ttitle center 'Sample Title' -
right 'PAGE: ' format 999 sql.pno skip
column bogus noprint
break on report
compute count of bogus on report
If I issue my query with the "bogus" column, WITH DATA, I get: SQL> select NULL "bogus", dummy from dual where 1 = 1;
Sample Title PAGE: 1
D
If I issue my query with the "bogus" column, WITHOUT DATA, I get: SQL> select NULL "bogus", dummy from dual where 1 = 2;
Sample Title PAGE: 1
D
Not elegant, but it seems to work. Anyone have a better way?
Wayne
____________________Reply Separator____________________Subject: RE : Displaying Title When No Data Returned Author: <ORACLE-L_at_fatcity.com>
Wayne,
This works :
ttitle sample title
column dummy noprint
select * from dual;
select * from user_tables
where table_name = 'XXXX'
/
-- Regards, Stephane Faroult email: sfaroult_at_oriolecorp.com Oriole Corporation Voice: +44 (0) 7050-696-269 Fax: +44 (0) 7050-696-449 Performance Tools & Free Scripts ------------------------------------------------------------------ http://www.oriolecorp.com, designed by Oracle DBAs for Oracle DBAs ------------------------------------------------------------------Received on Fri Sep 15 2000 - 13:35:32 CDT
>
> I am using the SQLPLUS TTITLE statement to display a number of standard audit
> reports. The problem is, when there are no rows returned, the report title is
> not displaying.
>
> Does anyone have a technique for making sure that a SQLPLUS report title is
> always displayed, even when there is no data?
>
> Wayne
>
> --
> Author: Wayne S Bellefeuill
> INET: wbellefe_at_allina.com
> -- Author: Stephane Faroult INET: sfaroult_at_oriolecorp.com Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may
 |
 |