Home » SQL & PL/SQL » SQL & PL/SQL » page heading in addition to column headings (SQL*Plus: Release 10.2.0.3.0 //Oracle9i Release 9.2.0.4.0 - Production)
page heading in addition to column headings [message #364355] Mon, 08 December 2008 02:51 Go to next message
ejbd
Messages: 6
Registered: December 2008
Location: Philly
Junior Member
I've tried all of the searches I can think of, so If this is already answered please point the way. (I did read something about making 2 spooled tables then putting them together, but this is NOT self explanitory.

I have a report the is basically in 4 sections. I want the column headings (4 per section) to remain AND would like to a a section heading for each section.

any ideas?
Re: page heading in addition to column headings [message #364356 is a reply to message #364355] Mon, 08 December 2008 02:59 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
In SQL*Plus you can use the TTITLE command to put a heading at the top of each report page.
Re: page heading in addition to column headings [message #364357 is a reply to message #364355] Mon, 08 December 2008 02:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
TTITLE?

Regards
Michel
Re: page heading in addition to column headings [message #364359 is a reply to message #364356] Mon, 08 December 2008 03:09 Go to previous messageGo to next message
ejbd
Messages: 6
Registered: December 2008
Location: Philly
Junior Member
I've got a ttitle on the report.

additionally I'd like a section heading in between that and the column headings.

Is that possible?
Re: page heading in addition to column headings [message #364360 is a reply to message #364359] Mon, 08 December 2008 03:11 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
I think we need to see an example of what you want. You can always run a select statement to get what you want, perhaps using new_value. You can also spool to separate files, then concatenate the files from the operating system afterwards.
Re: page heading in addition to column headings [message #364362 is a reply to message #364359] Mon, 08 December 2008 03:13 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
Here is an example of what I think you are asking for:

http://download.oracle.com/docs/cd/B28359_01/server.111/b31189/ch6.htm#sthref789
Re: page heading in addition to column headings [message #364369 is a reply to message #364362] Mon, 08 December 2008 03:28 Go to previous messageGo to next message
ejbd
Messages: 6
Registered: December 2008
Location: Philly
Junior Member
I'll give it a shot, but it looks like it separates vertically rather than horizontally...

I'm looking for something more like:
(ttitle) Philly tours for January

Water Tours Museum Tours Country Tours

day times operator day times operator day times operator (col t)
M 11a-4p Al MW 10a-12:30 W 1p-6 Ellen
T 10a-3p Jess THF 12noon-2:30 T 11a-4 George

the middle ones are what I want to create
Re: page heading in addition to column headings [message #364370 is a reply to message #364369] Mon, 08 December 2008 03:30 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide, especially "How to format your post?" section.

Post a test case: create table and insert statements along with the result you want with these data so we can reproduce it.

Align the columns in result.
Use the "Preview Message" button to verify.

Regards
Michel
Re: page heading in addition to column headings [message #364371 is a reply to message #364360] Mon, 08 December 2008 03:32 Go to previous messageGo to next message
ejbd
Messages: 6
Registered: December 2008
Location: Philly
Junior Member
see below for example - it actually looked lined up from when I was typing....

can you be more specific about the using new value?
And the concatenated spool files. Can I do that in a script or does it have to be done manually?
Re: page heading in addition to column headings [message #364374 is a reply to message #364369] Mon, 08 December 2008 03:44 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
Perhaps you can make the intermediate headings part of the column headings, as demonstrated below.

SCOTT@orcl_11g> TTITLE "Philly tours for January"
SCOTT@orcl_11g> COLUMN col1 FORMAT A5 HEADING 'Water||day'
SCOTT@orcl_11g> COLUMN col2 HEADING 'Tours||operator'
SCOTT@orcl_11g> COLUMN col3 FORMAT A7 HEADING 'Museum||day'
SCOTT@orcl_11g> COLUMN col4 HEADING 'Tours||operator'
SCOTT@orcl_11g> SELECT TO_CHAR (hiredate, 'Dy') col1,
  2  	    ename col2,
  3  	    TO_CHAR (hiredate, 'Dy') col3,
  4  	    ename col4
  5  FROM   emp
  6  /

Mon Dec 08                                                             page    1
                            Philly tours for January

Water Tours      Museum  Tours

day   operator   day     operator
----- ---------- ------- ----------
Wed   SMITH      Wed     SMITH
Fri   ALLEN      Fri     ALLEN
Sun   WARD       Sun     WARD
Thu   JONES      Thu     JONES
Mon   MARTIN     Mon     MARTIN
Fri   BLAKE      Fri     BLAKE
Tue   CLARK      Tue     CLARK
Thu   SCOTT      Thu     SCOTT
Tue   KING       Tue     KING
Tue   TURNER     Tue     TURNER
Wed   ADAMS      Wed     ADAMS
Thu   JAMES      Thu     JAMES
Thu   FORD       Thu     FORD
Sat   MILLER     Sat     MILLER

14 rows selected.

SCOTT@orcl_11g> 

Re: page heading in addition to column headings [message #364375 is a reply to message #364371] Mon, 08 December 2008 03:46 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
see below for example - it actually looked lined up from when I was typing....

Read the forum guide to know how to format your post.

Regards
Michel
Re: page heading in addition to column headings [message #364379 is a reply to message #364370] Mon, 08 December 2008 03:55 Go to previous messageGo to next message
ejbd
Messages: 6
Registered: December 2008
Location: Philly
Junior Member
Here is the script I've been using. Column headings and ttitle were fine. It's trying to add in the TOUR names column that's giving me difficulty.

I DO APPRECIATE EVERYONE'S HELP!!!

set pagesize 80
set linesize 200
prompt*****
prompt Main Script for tour selection
prompt*****
ACCEPT TNAME1 PROMPT "ENTER COURSE1:";
ACCEPT TNAME2 PROMPT "ENTER COURSE2:";
ACCEPT TNAME3 PROMPT "ENTER COURSE3:";
ACCEPT TNAME4 PROMPT "ENTER COURSE4:";
SELECT COURSE
FROM FINALSElections
WHERE TOUR LIKE UPPER('%&TNAME1%')
or TOUR LIKE UPPER('%&TNAME2%')
or TOUR LIKE UPPER('%&TNAME3%')
or TOUR LIKE UPPER('%&TNAME4%');
--BEGIN VIEW
drop view finalTOUR;
CREATE VIEW FINALTOUR(s1,d1,st1,et1,s2,d2,st2,et2,s3,d3,st3,et3,s4,d4,st4,et4) AS
SELECT A.SECTION, A.DAYS, A.START_TIME, A.END_TIME,
B.SECTION, B.DAYS, B.START_TIME, B.END_TIME,
c.SECTION,c.DAYS, c.START_TIME, c.END_TIME,
d.SECTION, d.DAYS, d.START_TIME, d.END_TIME
FROM FINALSELECTIONS A, FINALSELECTIONS B, finalselections c, finalselections d
WHERE A.COURSE=UPPER('&TNAME1') AND B.COURSE=UPPER('&TNAME2') and c.course=UPPER('&TNAME3') and d.course=UPPER('&TNAME4')
AND ((A.DAYNUMBER + B.DAYNUMBER not LIKE '%2%') OR B.MSTART > A.MEND OR B.MEND < A.MSTART)
AND ((A.DAYNUMBER + C.DAYNUMBER not LIKE '%2%') OR C.MSTART > A.MEND OR C.MEND < A.MSTART)
AND ((A.DAYNUMBER + D.DAYNUMBER not LIKE '%2%') OR D.MStART > A.MEND OR D.MEND < A.MSTART)
and ((B.DAYNUMBER + C.DAYNUMBER not LIKE '%2%') OR C.MSTART > B.MEND OR C.MEND < B.MSTART)
AND ((B.DAYNUMBER + D.DAYNUMBER not LIKE '%2%') OR D.MSTART > B.MEND OR D.MEND < B.MSTART)
AND ((C.DAYNUMBER + D.DAYNUMBER not LIKE '%2%') OR D.MSTART > C.MEND OR D.MEND < C.MSTART)
AND A.TOUR != B.TOUR
AND A.TOUR != C.TOUR
AND A.TOUR != D.TOUR
AND B.TOUR != C.TOUR
AND B.TOUR != D.TOUR
AND C.TOUR != D.TOUR;
--TEST HEADINGS
clear columns
set linesize 250
set pagesize 70
ttitle 'PROPOSED TOUR SCHEDULES - SPRING 2009'
COLUMN TOUR HEADING '' FORMAT A8
COLUMN DESCRIPTION HEADING '' FORMAT A22
COLUMN TOUR HEADING '' FORMAT A8
COLUMN DESCRIPTION HEADING '' FORMAT A22
COLUMN TOUR HEADING '' FORMAT A8
COLUMN DESCRIPTION HEADING '' FORMAT A22
COLUMN TOUR HEADING '' FORMAT A8
COLUMN DESCRIPTION HEADING '' FORMAT A22;
SELECT A.TOURID, A.DESCRIPTION,
B.TOURID, B.DESCRIPTION,
C.TOURID, C.DESCRIPTION,
D.TOURID, D.DESCRIPTION
FROM TOURSCHD A, TOURSCHD B, TOURSCHD C, TOURSCHD D, TOURSCHD x, TOURSCHD y
WHERE x.TOUR = y.TOUR
AND A.TOURid = UPPER('%&TNAME1%')
AND B.TOURid = UPPER('%&TNAME2%')
AND C.TOURid = UPPER('%&TNAME3%')
AND D.TOURid = UPPER('%&TNAME4%');
--says no rows selected
--ttitle off
--clear columns (SECTION COLUMN IS USED FOR OPERATOR)
column s1 heading 'SEC' format a4
column d1 heading 'DAYS' format a5
column st1 heading 'START |TIME' format a7
column et1 heading 'END|TIME' format a7
column s2 heading 'SEC' format a4
column d2 heading 'DAYS' format a5
column st2 heading 'START |TIME' format a7
column et2 heading 'END|TIME' format a7
column s3 heading 'SEC' format a4
column d3 heading 'DAYS' format a5
column st3 heading 'START |TIME' format a7
column et3 heading 'END|TIME' format a7
column s4 heading 'SEC' format a4
column d4 heading 'DAYS' format a5
column st4 heading 'START |TIME' format a7
column et4 heading 'END|TIME' format a7
/
select * from finaltour
order by s1,s2,s3,s4;
Re: page heading in addition to column headings [message #364383 is a reply to message #364379] Mon, 08 December 2008 04:00 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I DO APPRECIATE EVERYONE'S HELP!!!

I DO APPRECIATE YOU READ AND FOLLOW GUIDELINES!!!

Regards
Michel
Re: page heading in addition to column headings [message #364385 is a reply to message #364379] Mon, 08 December 2008 04:04 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
So, modify the column headings in the manner that I previously demonstrated:

column s1 heading 'WATER||SEC' format a5
column d1 heading 'TOURS||DAYS' format a5
column st1 heading 'START |TIME' format a7
column et1 heading 'END|TIME' format a7
column s2 heading 'MUSEUM||SEC' format a6
column d2 heading 'TOURS||DAYS' format a5
column st2 heading 'START |TIME' format a7
column et2 heading 'END|TIME' format a7
... and so on
Re: page heading in addition to column headings [message #364389 is a reply to message #364383] Mon, 08 December 2008 04:36 Go to previous messageGo to next message
ejbd
Messages: 6
Registered: December 2008
Location: Philly
Junior Member
Michel, I'm working on it (reading the guidelines). Give me a bit of time - the posts here don't line up the way they do in some other forum...

Barabara, thank for the idea of including them over(as part of) column headings. I certainly will give that a try
Re: page heading in addition to column headings [message #364390 is a reply to message #364389] Mon, 08 December 2008 04:38 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Michel, I'm working on it (reading the guidelines). Give me a bit of time -

Each one does it as he wants, for myself I first read the guidelines then I post. I call this respect for the forum community.
More when someone points me to my error, I carefully read a second and third time before going on. Above all when a way to check my post is given.

Regards
Michel

[Updated on: Mon, 08 December 2008 04:40]

Report message to a moderator

Previous Topic: how to get the list of tables having referential integrity connection
Next Topic: disable procedure
Goto Forum:
  


Current Time: Sat Dec 10 07:23:19 CST 2016

Total time taken to generate the page: 0.04357 seconds