Home » SQL & PL/SQL » Client Tools » Generating Header and Footer information in a Report (Oracel PL/SQL 9i Microsoft 2K3)
Generating Header and Footer information in a Report [message #431809] Thu, 19 November 2009 11:12 Go to next message
dnfrantum
Messages: 11
Registered: November 2009
Location: California
Junior Member
I am trying to create a report out of a query that I wrote, but I am not having the success I was hoping for. Essentially, the header should include a 'BOF' on line one, the system date and time on line two, a record count on line three, and 'EOF' on the last line. I have not been successful in getting any of these to print correctly and I don't even know how to get the record count. I suspect it is a COMPUTE and BREAK command, but I don't know how to effectively use them. I put 'BOF' and 'EOF' in both REPHEADER and REPFOOTER and TTITLE and BTITLE clauses and didn't get the output that I desire. Here is my code below:

SET FEEDBACK OFF
sET PAGESIZE 60
SET NEWPAGE 0
SET ECHO OFF
SET TRIMSPOOL ON
SET HEADING OFF
SPOOL C:\DF_KAPLAN_TEST_a.txt;
COLUMN current_date NEW_VALUE report_date
SELECT TO_CHAR(SYSDATE,'YYYYMMDD') current_date FROM DUAL;
TTITLE LEFT 'BOF'-
TTITEL LEFT report_date
BTITLE LEFT 'EOF'
SELECT
T0.OBJECT_NAME||'|'||T0.Title||'|'||CASE WHEN (T0.R_VERSION >= 1.0) THEN T0.R_VERSION ELSE '' END
||'|'||T0.R_Object_Type||'|'||T0.R_EffectiveDate
||'|'||T0.R_EffectiveDate
FROM 
QM_Document_Base T0
WHERE
T0.R_LIFECYCLESTATE IN (1229,1231,1232) AND T0.R_ISSYSTEM_COPY = 0 AND T0.I_LATEST_FLAG = 1 and T0.I_Is_Deleted = 0 

AND T0.R_Object_Type IN ('qu_policy_doctype','qu_sop_doctype','qu_workinstruction_doctype') ORDER BY 

T0.R_Object_Type, T0.Object_Name ;
SPOOL OFF;

Any help would be greatly appreciated.
Donald

[Updated on: Thu, 19 November 2009 11:24]

Report message to a moderator

Re: Generating Header and Footer information in a Report [message #431816 is a reply to message #431809] Thu, 19 November 2009 11:27 Go to previous messageGo to next message
Michel Cadot
Messages: 63808
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As we have not the objects you have we can't "see" what you have, so show us.
And show us what you want with an example.

Before please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals.

Regards
Michel
Re: Generating Header and Footer information in a Report [message #431822 is a reply to message #431816] Thu, 19 November 2009 11:31 Go to previous messageGo to next message
dnfrantum
Messages: 11
Registered: November 2009
Location: California
Junior Member
Please see the attached file. I am able to get the detail without issue. However the header and footer information in the correct format, e.g. no line feeds or duplicates is elluding me.
Re: Generating Header and Footer information in a Report [message #431831 is a reply to message #431822] Thu, 19 November 2009 11:44 Go to previous messageGo to next message
Michel Cadot
Messages: 63808
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I can't download files, put a simple example with 10 lines, it is far sufficient to understand what you have and what you want.

By the way "TTITEL" is not a SQL*Plus command.

Regards
Michel
Re: Generating Header and Footer information in a Report [message #431834 is a reply to message #431831] Thu, 19 November 2009 11:50 Go to previous messageGo to next message
dnfrantum
Messages: 11
Registered: November 2009
Location: California
Junior Member
It should have read TTITLE, but here is a an example of what I am trying to achieve:

BOF
02-26-2009 14:19
633
"EOP105"|"Internal"|"Engineering Variance"|"6"|"0"|"CICS"|"SOP"|"Rev. F"|"General"|"01/02/2009"|"01/02/2009"|""|"1"|""|""|""|"1"|""|"0"|""|""|""|""|""|""|""|""|""|""|""|"14"|"dd"|""|""|""|""|"1"|"\\lfdcontrol\working\PDF_Active\EOP105F.PDF"
"EOP203"|"Internal"|"Engineering and Quality Test Data"|"4"|"0"|"CICS"|"SOP"|"Rev. D"|"General"|"07/26/2006"|"07/26/2006"|""|"1"|""|""|""|"1"|""|"0"|""|""|""|""|""|""|""|""|""|""|""|"14"|"dd"|""|""|""|""|"1"|"\\lfdcontrol\working\PDF_Active\EOP203D.PDF"
"EOP209"|"Internal"|"Machine Shop Work Order Request"|"2"|"0"|"CICS"|"SOP"|"Rev. B"|"General"|"10/08/2007"|"10/08/2007"|""|"1"|""|""|""|"1"|""|"0"|""|""|""|""|""|""|""|""|""|""|""|"14"|"dd"|""|""|""|""|"1"|"\\lfdcontrol\working\PDF_Active\EOP209B.PDF"
"FORM120"|"Internal"|"Document Change Form"|"17"|"0"|"CICS"|"SOP"|"Rev. U"|"General"|"01/15/2009"|"01/15/2009"|""|"1"|""|""|""|"1"|""|"0"|""|""|""|""|""|""|""|""|""|""|""|"14"|"dd"|""|""|""|""|"1"|"\\lfdcontrol\working\PDF_Active\FORM120U.PDF"
"FORM121"|"Internal"|"Label, 30-day Hold for Latex"|"2"|"0"|"CICS"|"SOP"|"Rev. B"|"General"|"11/09/2007"|"11/09/2007"|""|"1"|""|""|""|"1"|""|"0"|""|""|""|""|""|""|""|""|""|""|""|"14"|"dd"|""|""|""|""|"1"|"\\lfdcontrol\working\PDF_Active\FORM121B.PDF"
"FORM123"|"Internal"|"510(k) Change - Main Flowchart"|"2"|"0"|"CICS"|"SOP"|"Rev. B"|"General"|"05/11/2000"|"05/11/2000"|""|"1"|""|""|""|"1"|""|"0"|""|""|""|""|""|""|""|""|""|""|""|"14"|"dd"|""|""|""|""|"1"|"\\lfdcontrol\working\PDF_Active\FORM123B.PDF"
"FORM124"|"Internal"|"510(k) Change - Flowchart A - Labeling Change"|"2"|"0"|"CICS"|"SOP"|"Rev. B"|"General"|"05/11/2000"|"05/11/2000"|""|"1"|""|""|""|"1"|""|"0"|""|""|""|""|""|""|""|""|""|""|""|"14"|"dd"|""|""|""|""|"1"|"\\lfdcontrol\working\PDF_Active\FORM124B.PDF"
"FORM125"|"Internal"|"510(k) Change - Flowchart B - Technology or Performance Change"|"2"|"0"|"CICS"|"SOP"|"Rev. B"|"General"|"05/11/2000"|"05/11/2000"|""|"1"|""|""|""|"1"|""|"0"|""|""|""|""|""|""|""|""|""|""|""|"14"|"dd"|""|""|""|""|"1"|"\\lfdcontrol\working\PDF_Active\FORM125B.PDF"
"FORM126"|"Internal"|"510(k) Change - Flowchart C - Materials Change"|"2"|"0"|"CICS"|"SOP"|"Rev. B"|"General"|"05/11/2000"|"05/11/2000"|""|"1"|""|""|""|"1"|""|"0"|""|""|""|""|""|""|""|""|""|""|""|"14"|"dd"|""|""|""|""|"1"|"\\lfdcontrol\working\PDF_Active\FORM126B.PDF"
"FORM134"|"Internal"|"English to Spanish Translations Form"|"4"|"0"|"CICS"|"SOP"|"Rev. D"|"General"|"02/26/2008"|"02/26/2008"|""|"1"|""|""|""|"1"|""|"0"|""|""|""|""|""|""|""|""|""|""|""|"14"|"dd"|""|""|""|""|"1"|"\\lfdcontrol\working\PDF_Active\FORM134D.PDF"
"FORM145"|"Internal"|"Labeling Specification Form"|"7"|"0"|"CICS"|"SOP"|"Rev. G"|"General"|"06/13/2008"|"06/13/2008"|""|"1"|""|""|""|"1"|""|"0"|""|""|""|""|""|""|""|""|""|""|""|"14"|"dd"|""|""|""|""|"1"|"\\lfdcontrol\working\PDF_Active\FORM145G.PDF"
EOF
Re: Generating Header and Footer information in a Report [message #431835 is a reply to message #431809] Thu, 19 November 2009 11:52 Go to previous messageGo to next message
dnfrantum
Messages: 11
Registered: November 2009
Location: California
Junior Member
That was only a snippet, as there are not 633 records present in this example. I didn't want to load all 633 records, but this is the format that I am trying to achieve.

Thanks again,
Donald
Re: Generating Header and Footer information in a Report [message #431840 is a reply to message #431835] Thu, 19 November 2009 12:05 Go to previous messageGo to next message
Michel Cadot
Messages: 63808
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If the only part that is missing is count number, you can't have it at the beginning of each page.
COMPUTE and BREAK print it at the end of the report.

You can do it in SQL but only for the first page:
SQL> select decode(grouping(v),1,to_char(count(*)),v) val
  2  from (select empno||','||ename||','||sal v
  3        from emp)
  4  group by rollup(v)
  5  order by grouping(v) desc
  6  /
VAL
-----------------------------------------------------------
14
7521,WARD,1250
7566,JONES,2975
7654,MARTIN,1250
7698,BLAKE,2850
7782,CLARK,2450
7788,SCOTT,3000
7839,KING,5000
7844,TURNER,1500
7876,ADAMS,1100
7900,JAMES,950
7902,FORD,3000
7934,MILLER,1300
7369,SMITH,800
7499,ALLEN,1600

15 rows selected.

Regards
Michel
Re: Generating Header and Footer information in a Report [message #431854 is a reply to message #431840] Thu, 19 November 2009 12:32 Go to previous messageGo to next message
dnfrantum
Messages: 11
Registered: November 2009
Location: California
Junior Member
That was definitely helpful for the count, but this was the output of the rest of the file. Notice the first two lines...how do I stop the date from printing at the top, as well as, eliminate the two control characters?

11-19-2009 01:37
BOF
11-19-2009 01:37
56
POL-GN-00001|Policy on Policies and Procedural Documents|1.0|qu_policy_doctype|2
2-FEB-09|22-FEB-09

...purposely truncated to eliminate too much detail...

SOP-IT-00001|Computerized System LifeCycle Management|1.0|qu_sop_doctype|22-FEB-
09|22-FEB-09

EOF


Again, here is the code for the report:
SET FEEDBACK OFF
sET PAGESIZE 60
SET NEWPAGE 0
SET ECHO OFF
SET TRIMSPOOL ON
SET HEADING OFF
SPOOL C:\DF_KAPLAN_TEST_a.txt;
COLUMN current_date NEW_VALUE report_date
SELECT TO_CHAR(SYSDATE,'mm-dd-yyyy hh:SS') current_date FROM DUAL;
REPH LEFT 'BOF'SKIP 1 -
report_date
REPF LEFT 'EOF'
select decode(grouping(v),1,to_char(count(*)),v) val
FROM(SELECT
T0.OBJECT_NAME||'|'||T0.Title||'|'||CASE WHEN (T0.R_VERSION >= 1.0) THEN T0.R_VERSION ELSE '' END
||'|'||T0.R_Object_Type||'|'||T0.R_EffectiveDate
||'|'||T0.R_EffectiveDate v
FROM 
QM_Document_Base T0
WHERE
T0.R_LIFECYCLESTATE IN (1229,1231,1232) AND T0.R_ISSYSTEM_COPY = 0 AND T0.I_LATEST_FLAG = 1 and T0.I_Is_Deleted = 0 

AND T0.R_Object_Type IN ('qu_policy_doctype','qu_sop_doctype','qu_workinstruction_doctype') ORDER BY 

T0.R_Object_Type, T0.Object_Name)
group by rollup(v)
order by grouping(v) desc;
SPOOL OFF;


Thanks again,
Donald
Re: Generating Header and Footer information in a Report [message #431857 is a reply to message #431854] Thu, 19 November 2009 12:37 Go to previous messageGo to next message
dnfrantum
Messages: 11
Registered: November 2009
Location: California
Junior Member
I actually got the date to stop printing on line 1 with a NOPRINT option in the COLUMN statement. I am still left with the two control characters though. Any thoughts on where these are originating?
Re: Generating Header and Footer information in a Report [message #431862 is a reply to message #431857] Thu, 19 November 2009 12:46 Go to previous messageGo to next message
dnfrantum
Messages: 11
Registered: November 2009
Location: California
Junior Member
Okay, got rid of the control characters, but I am still left with a leading line...I don't understand where this is coming from. Here is the output and the code once more.


BOF
11-19-2009 01:44
56
POL-GN-00001|Policy on Policies and Procedural Documents|1.0|qu_policy_doctype|2
2-FEB-09|22-FEB-09
EOF


Code
SET FEEDBACK OFF
SET PAGESIZE 59
SET NEWPAGE NONE
SET ECHO OFF
SET TRIMSPOOL ON
SET HEADING OFF
SPOOL C:\DF_KAPLAN_TEST_a.txt;
BREAK ON REPORT
COLUMN current_date NOPRINT NEW_VALUE report_date
SELECT TO_CHAR(SYSDATE,'mm-dd-yyyy hh:SS') current_date FROM DUAL;
BREAK ON REPORT
REPH LEFT 'BOF'SKIP 1 -
report_date
REPF LEFT 'EOF'
select decode(grouping(v),1,to_char(count(*)),v) val
FROM(SELECT
T0.OBJECT_NAME||'|'||T0.Title||'|'||CASE WHEN (T0.R_VERSION >= 1.0) THEN T0.R_VERSION ELSE '' END
||'|'||T0.R_Object_Type||'|'||T0.R_EffectiveDate
||'|'||T0.R_EffectiveDate v
FROM 
QM_Document_Base T0
WHERE
T0.R_LIFECYCLESTATE IN (1229,1231,1232) AND T0.R_ISSYSTEM_COPY = 0 AND T0.I_LATEST_FLAG = 1 and T0.I_Is_Deleted = 0 

AND T0.R_Object_Type IN ('qu_policy_doctype','qu_sop_doctype','qu_workinstruction_doctype') ORDER BY 

T0.R_Object_Type, T0.Object_Name)
group by rollup(v)
order by grouping(v) desc;
SPOOL OFF;

Re: Generating Header and Footer information in a Report [message #431864 is a reply to message #431862] Thu, 19 November 2009 12:54 Go to previous messageGo to next message
Michel Cadot
Messages: 63808
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The line comes from "SELECT TO_CHAR(SYSDATE,'mm-dd-yyyy hh:SS') current_date FROM DUAL;" put it before SPOOL.

Regards
Michel
icon14.gif  Re: Generating Header and Footer information in a Report [message #431885 is a reply to message #431864] Thu, 19 November 2009 16:30 Go to previous message
dnfrantum
Messages: 11
Registered: November 2009
Location: California
Junior Member
Everything works...Thanks a lot for all of your help Michel.
Previous Topic: TOAD error for deletion
Next Topic: Accesing a Oracle database (UNIX) from Z/OS (Cobol-DB2)
Goto Forum:
  


Current Time: Wed Sep 28 22:53:27 CDT 2016

Total time taken to generate the page: 0.07156 seconds