Home » Other » Client Tools » Break and Compute command usage to modify the output (Oracle,11g,Windows 2003 server)
Break and Compute command usage to modify the output [message #479102] Thu, 14 October 2010 10:16 Go to next message
aviana
Messages: 106
Registered: July 2007
Senior Member
Hi Can I ask help on getting the following script modified by using the break on and compute commands please?.(or any other method if that works..)
I tried BRK and COMPUTE commands myself after reading the documentation but its not working
(You can see how I used these in my script below)

The output of my script is correct but What I want is the information to be displayed on a different way:
Original script for the report:
set serveroutput on size 1000000
set pages 10000
set lines 1000
set arraysize 1
set trimspool on
set heading off
 
BREAK ON TH.THORFARE_NAME
COMPUTE COUNT OF b.sequence_no on TH.THORFARE_NAME
 
spool c:\sqls\CLAR_OFFICE.csv
 
Prompt  CLAR information in sequence for this office
 
select 	('"Region","Post Town","Delivery Office","Route Name","Thoroughfare","Locality","SEQUENCE"') from dual;
select	'"'||r.name			||'"'||','||	
	'"'||p.name			||'"'||','||
	'"'||do.name			||'"'||','||
	'"'||ro.name			||'"'||','||
	'"'||decode(th.thorfare_name,'OSIUNKNOWN',NULL,th.thorfare_name)||'"'||','||
	'"'||l.name		        ||'"'||','||
	'"'||b.sequence_no		||'"'
from    t_regions r,t_post_towns p,t_delivery_offices do, t_routes ro, t_counties c,t_head_offices ho,
    t_buildings b,   t_thoroughfares th,t_localities l
where   th.thorfare_id = b.thorfare_id
and    nvl(b.invalid,'N')='N'
and    b.route_id=ro.route_id(+)
and    b.locality_id =l.locality_id(+)
and    ro.delivery_office_id=do.delivery_office_id(+)
and    do.post_town_id = p.post_town_id(+)
and    p.ho_id=ho.ho_id(+)
and     ho.county_id = c.county_id(+)
and     c.region_id = r.region_id(+)
and    r.name='NAAS'
and do.name='MAYNOOTH'
and ro.route_id=1715
group by r.name,p.name,do.name,ro.name,th.thorfare_name,l.name,b.sequence_no
ORDER BY b.sequence_no;
 
spool off
 
exit;



The output is:
Quote:

CLAR information in sequence for this office

Region Post Town Delivery Office Route Name Thoroughfare Locality SEQUENCE


NAAS NAAS MAYNOOTH MAYNOOTH R036 STRAFFAN ROAD MAYNOOTH 2
NAAS NAAS MAYNOOTH MAYNOOTH R036 STRAFFAN ROAD MAYNOOTH 30
NAAS NAAS MAYNOOTH MAYNOOTH R036 STRAFFAN ROAD MAYNOOTH 59
NAAS NAAS MAYNOOTH MAYNOOTH R036 STRAFFAN ROAD MAYNOOTH 78
NAAS NAAS MAYNOOTH MAYNOOTH R036 STRAFFAN ROAD MAYNOOTH 96
NAAS NAAS MAYNOOTH MAYNOOTH R036 STRAFFAN ROAD MAYNOOTH 105
NAAS NAAS MAYNOOTH MAYNOOTH R036 STRAFFAN ROAD MAYNOOTH 106
NAAS NAAS MAYNOOTH MAYNOOTH R036 STRAFFAN ROAD MAYNOOTH 109
NAAS NAAS MAYNOOTH MAYNOOTH R036 STRAFFAN ROAD MAYNOOTH 111
NAAS NAAS MAYNOOTH MAYNOOTH R036 OLD RAILPARK 116
NAAS NAAS MAYNOOTH MAYNOOTH R036 OLD RAILPARK 117
NAAS NAAS MAYNOOTH MAYNOOTH R036 OLD RAILPARK 118
NAAS NAAS MAYNOOTH MAYNOOTH R036 OLD RAILPARK 119
NAAS NAAS MAYNOOTH MAYNOOTH R036 OLD RAILPARK 120
NAAS NAAS MAYNOOTH MAYNOOTH R036 OLD RAILPARK 121
NAAS NAAS MAYNOOTH MAYNOOTH R036 OLD RAILPARK 122
NAAS NAAS MAYNOOTH MAYNOOTH R036 OLD RAILPARK 123
NAAS NAAS MAYNOOTH MAYNOOTH R036 STRAFFAN ROAD MAYNOOTH 124
NAAS NAAS MAYNOOTH MAYNOOTH R036 OLD RAILPARK 125
NAAS NAAS MAYNOOTH MAYNOOTH R036 OLD RAILPARK 126
NAAS NAAS MAYNOOTH MAYNOOTH R036 OLD RAILPARK 127
NAAS NAAS MAYNOOTH MAYNOOTH R036 OLD RAILPARK 128
NAAS NAAS MAYNOOTH MAYNOOTH R036 SILKEN VALE MAYNOOTH 129
NAAS NAAS MAYNOOTH MAYNOOTH R036 STRAFFAN ROAD MAYNOOTH 130
NAAS NAAS MAYNOOTH MAYNOOTH R036 MAIN STREET MAYNOOTH 132
NAAS NAAS MAYNOOTH MAYNOOTH R036 KILCOCK ROAD MAYNOOTH 134
NAAS NAAS MAYNOOTH MAYNOOTH R036 DUNBOYNE ROAD MAYNOOTH 135
NAAS NAAS MAYNOOTH MAYNOOTH R036 DUNBOYNE ROAD MAYNOOTH 136
NAAS NAAS MAYNOOTH MAYNOOTH R036 DUNBOYNE ROAD MAYNOOTH 137
NAAS NAAS MAYNOOTH MAYNOOTH R036 DUNBOYNE ROAD MAYNOOTH 138
NAAS NAAS MAYNOOTH MAYNOOTH R036 CROWNCOURT 139
NAAS NAAS MAYNOOTH MAYNOOTH R036 CROWNCOURT 140
NAAS NAAS MAYNOOTH MAYNOOTH R036 CROWNCOURT 141
NAAS NAAS MAYNOOTH MAYNOOTH R036 CROWNCOURT 142
NAAS NAAS MAYNOOTH MAYNOOTH R036 BLACKLION HALTING SITE 143
NAAS NAAS MAYNOOTH MAYNOOTH R036 BLACKLION HALTING SITE 144
NAAS NAAS MAYNOOTH MAYNOOTH R036 BLACKLION HALTING SITE 145
NAAS NAAS MAYNOOTH MAYNOOTH R036 BLACKLION HALTING SITE 146
NAAS NAAS MAYNOOTH MAYNOOTH R036 BLACKLION HALTING SITE 147
NAAS NAAS MAYNOOTH MAYNOOTH R036 BLACKLION HALTING SITE 148
NAAS NAAS MAYNOOTH MAYNOOTH R036 BLACKLION HALTING SITE 149
NAAS NAAS MAYNOOTH MAYNOOTH R036 BLACKLION HALTING SITE 150
NAAS NAAS MAYNOOTH MAYNOOTH R036 BLACKLION HALTING SITE 151
NAAS NAAS MAYNOOTH MAYNOOTH R036 BLACKLION HALTING SITE 152
NAAS NAAS MAYNOOTH MAYNOOTH R036 BLACKLION HALTING SITE 153
NAAS NAAS MAYNOOTH MAYNOOTH R036 BLACKLION 154
NAAS NAAS MAYNOOTH MAYNOOTH R036 BLACKLION 155
NAAS NAAS MAYNOOTH MAYNOOTH R036 DUBLIN ROAD MAYNOOTH 156
NAAS NAAS MAYNOOTH MAYNOOTH R036 DUBLIN ROAD MAYNOOTH 160
NAAS NAAS MAYNOOTH MAYNOOTH R036 DUBLIN ROAD MAYNOOTH 161
NAAS NAAS MAYNOOTH MAYNOOTH R036 DUBLIN ROAD MAYNOOTH 163
NAAS NAAS MAYNOOTH MAYNOOTH R036 DUBLIN ROAD MAYNOOTH 164

52 rows selected.



What I want is, in the output is, DO NOT REPEAT DATA THE THOROUGHFARE and LOCALITY columns.
i.e until whenver they changes value.. and get the **** of the number of rows for that and display
For example, in the above output set,
the output should be:(I am manually counting and displaying this here:)
Quote:

Region Post Town Delivery Office Route Name Thoroughfare Locality SEQUENCE COUNT


NAAS NAAS MAYNOOTH MAYNOOTH R036 STRAFFAN ROAD MAYNOOTH 9
NAAS NAAS MAYNOOTH MAYNOOTH R036 OLD RAILPARK 8
NAAS NAAS MAYNOOTH MAYNOOTH R036 STRAFFAN ROAD MAYNOOTH 1
NAAS NAAS MAYNOOTH MAYNOOTH R036 OLD RAILPARK 4
NAAS NAAS MAYNOOTH MAYNOOTH R036 SILKEN VALE MAYNOOTH 1
NAAS NAAS MAYNOOTH MAYNOOTH R036 STRAFFAN ROAD MAYNOOTH 1
NAAS NAAS MAYNOOTH MAYNOOTH R036 MAIN STREET MAYNOOTH 1
NAAS NAAS MAYNOOTH MAYNOOTH R036 KILCOCK ROAD MAYNOOTH 1
NAAS NAAS MAYNOOTH MAYNOOTH R036 DUNBOYNE ROAD MAYNOOTH 4
NAAS NAAS MAYNOOTH MAYNOOTH R036 CROWNCOURT 4
NAAS NAAS MAYNOOTH MAYNOOTH R036 BLACKLION HALTING SITE 11
NAAS NAAS MAYNOOTH MAYNOOTH R036 BLACKLION 2
NAAS NAAS MAYNOOTH MAYNOOTH R036 DUBLIN ROAD MAYNOOTH 5


The output should be exactly as above as the requirement is this should be in this particular order and how many is the count in each thoroughfare or locality, as shown in the final output, above. PLS Help!!

thanks a million.

[Updated on: Thu, 14 October 2010 10:29]

Report message to a moderator

Re: Break and Compute command usage to modify the output [message #479121 is a reply to message #479102] Thu, 14 October 2010 11:23 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I tried BRK and COMPUTE commands myself after reading the documentation but its not working

This is meaningless for us.

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.

Post a working Test case: create table and insert statements along with the result you want with these data then we will be able work with your table and data.

Regards
Michel

[Updated on: Thu, 14 October 2010 11:24]

Report message to a moderator

Re: Break and Compute command usage to modify the output [message #479166 is a reply to message #479121] Thu, 14 October 2010 16:36 Go to previous messageGo to next message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
BREAK and COMPUTE are not based on column names, they are based on the ALIASes used in the SELECT statement.

Your SELECT is just projecting one big concatenated column; you don't actually SELECT sequence_no or THORFARE_NAME.

Try:

COLUMN sequence_no NOPRINT
COLUMN THORFARE_NAME NOPRINT
BREAK ON THORFARE_NAME 
COMPUTE COUNT OF sequence_no ON THORFARE_NAME

SELECT sequence_no, THORFARE_NAME, <big concatenated column> ...


Ross Leishman
Re: Break and Compute command usage to modify the output [message #479253 is a reply to message #479166] Fri, 15 October 2010 10:08 Go to previous message
aviana
Messages: 106
Registered: July 2007
Senior Member
Hi Ross,thanks for replying, that was helpful.
But I need the output in a CSV file and need to do concatenated columns to get in that format. The following way worked for me perfectly in a text file output but when I do it in the CSV format, the break and compute commands doesnt work because as you say, i am not directly selecting those clumns...any ideas??
What works for the .txt format output is below:

set serveroutput on size 1000000
set pages 10000
set lines 1000
set arraysize 1
set trimspool on
set heading off
set feedback off
set echo off


BREAK ON THORFARE_NAME SKIP1 ON LOCALITY_NAME SKIP1 NODUP
COMPUTE COUNT OF sequence_no ON THORFARE_NAME


define DELIVERY_OFFICE_ID = &DELIVERY_OFFICE_id
spool c:\sqls\CLAR_OFFICE.txt
Prompt  CLAR information in sequence for this office

select 	('"Region","Post Town","Delivery Office","Route Name","Thoroughfare","Locality","SEQUENCE"') from dual;

select	r.name,p.name,do.name,ro.name,decode(th.thorfare_name,'OSIUNKNOWN',NULL,th.thorfare_name) THORFARE_NAME,
	l.name LOCALITY_NAME,sequence_no	
from    t_regions r,t_post_towns p,t_delivery_offices do, t_routes ro, t_counties c,t_head_offices ho,
    t_buildings b,   t_thoroughfares th,t_localities l
where   th.thorfare_id = b.thorfare_id
and    nvl(b.invalid,'N')='N'
and    b.route_id=ro.route_id(+)
and    b.locality_id =l.locality_id(+)
and    ro.delivery_office_id=do.delivery_office_id(+)
and    do.post_town_id = p.post_town_id(+)
and    p.ho_id=ho.ho_id(+)
and     ho.county_id = c.county_id(+)
and     c.region_id = r.region_id(+)
and    r.name='NAAS'
and    do.DELIVERY_OFFICE_id= &&DELIVERY_OFFICE_id
and ro.route_id=1715
group by r.name,p.name,do.name,ro.name,th.thorfare_name,l.name,b.sequence_no
ORDER BY b.sequence_no;

spool off

UNDEFINE DELIVERY_OFFICE_ID
CLEAR BREAKS
CLEAR COMPUTES

exit;

[Updated on: Fri, 15 October 2010 10:10]

Report message to a moderator

Previous Topic: Oracle 9i
Next Topic: Toad:export and import, value difference for big numbers
Goto Forum:
  


Current Time: Thu Mar 28 10:12:10 CDT 2024