Home » SQL & PL/SQL » SQL & PL/SQL » Report Formatting
Report Formatting [message #208979] Tue, 12 December 2006 16:46 Go to next message
yerics
Messages: 89
Registered: August 2006
Member
I want to display the column names after every break on a column.
for display clarity purpose.
for eg,

break on department_id skip page, gives me the column names on every change of department_id on a new page, but when I type
break on department_id skip 5, I dont get the column names after every change of department_id. It only displays after every page.

Regards,
Prasanna

Re: Report Formatting [message #208990 is a reply to message #208979] Tue, 12 December 2006 21:09 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Create a non-printing break-column which is a concatenation of your other break columns. Include it as the first column in the BREAK command. Then whenever any of the break columns changes, the first one (the concatenation) also changes, which causes all of them to be printed again.

column sort_key noprint
break on sort_key on table_name on data_type
select table_name || chr(1) || data_type AS sort_key
, TABLE_NAME
, DATA_TYPE
, COLUMN_NAME
from user_tab_columns
where rownum <= 100
order by 2,3
/


Ross Leishman
Re: Report Formatting [message #209160 is a reply to message #208990] Wed, 13 December 2006 09:26 Go to previous messageGo to next message
yerics
Messages: 89
Registered: August 2006
Member
Please look into the following output of the sample query.

show pages
pagesize 14

col sort_key noprint

select username || chr(1) || logname as sort_key, username, timstamp 
from seosdata 
where rownum < 10
order by username,timstamp

Output
-----------------------------------
USERNAME   TIMSTAMP
---------- ---------
ice4       26-OCT-06
ice4       26-OCT-06
ice4       26-OCT-06
root       26-OCT-06
root       26-OCT-06
root       26-OCT-06
root       26-OCT-06
root       26-OCT-06
root       26-OCT-06


break on username skip 5
/
USERNAME   TIMSTAMP
---------- ---------
ice4       26-OCT-06
           26-OCT-06
           26-OCT-06





root       26-OCT-06
           26-OCT-06
           26-OCT-06

USERNAME   TIMSTAMP
---------- ---------
root       26-OCT-06
           26-OCT-06
           26-OCT-06

break on sort_key on username skip 5
USERNAME   TIMSTAMP
---------- ---------
ice4       26-OCT-06
           26-OCT-06
           26-OCT-06





root       26-OCT-06
           26-OCT-06
           26-OCT-06

USERNAME   TIMSTAMP
---------- ---------
root       26-OCT-06





root       26-OCT-06
           26-OCT-06



However the required outout should be in the format..


USERNAME   TIMSTAMP
---------- ---------
ice4       26-OCT-06
           26-OCT-06
           26-OCT-06




USERNAME   TIMSTAMP
---------- ---------
root       26-OCT-06
           26-OCT-06


USERNAME   TIMSTAMP
---------- ---------
           26-OCT-06
	

root       26-OCT-06
           26-OCT-06
           26-OCT-06


Once the username changes it should print the column names.

Pl help.

Moderator: Added CODE tags.

[Updated on: Wed, 13 December 2006 19:33] by Moderator

Report message to a moderator

Re: Report Formatting [message #209220 is a reply to message #209160] Wed, 13 December 2006 19:37 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Why are you concatenating LOGNAME to the sort_key when you neither sort by it, not break on it?

I do not believe you can stop the key columns reprinting on change of page. You can only make the page very large (SET PAGESIZE 50000) or suppress column headings (SET PAGESIZE 0).

Ross Leishman
Previous Topic: Figuring out weekly counts
Next Topic: Displaying work hours for a pay period
Goto Forum:
  


Current Time: Wed Dec 07 20:14:10 CST 2016

Total time taken to generate the page: 0.05862 seconds