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  |
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  |
 |
Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...
|
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 |
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 |
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 |
|
|
|
Goto Forum:
Current Time: Tue Dec 31 04:51:41 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
