I'm posting this for a colleague.
The code below has been snipped to show only the relevant part.
Following that is a snipped part of the output.
His problem is incorrect totals; precise description follows the output.


Clear        Breaks
clear        computes

column file_name       format A25            heading 'Dataset NAME'
column DS_Size_MB      format 999,999        heading 'DATASET |SIZE(MB)'
column Tablespace_Name format A10            heading 'TS Name'
column Tot_Used_KB     format 99,999,999     heading 'STORAGE |USED(KB)'
column TS_Size_MB      format 999,999        heading 'TABLESPACE|SIZE(MB) '
column Tot_Free_KB     format 99,999,999     heading 'STORAGE |FREE(KB)'
column Tot_Chunks      format 999,999        heading 'FREE  |EXTENTS'
column Largest_chunk   format 9,999,999,999  heading 'Largest Free|Chunk(KB)  '
column Objects         format 99,999         heading 'Total |Objects'
column Pct_Free        format 999.99         heading 'FREE |PCT  '

compute sum LABEL 'TOTAL SUM' of DS_Size_MB   on report
compute sum LABEL 'TOTAL SUM' of TS_Size_MB   on report
compute sum LABEL 'TOTAL SUM' of Tot_Used_KB  on report
compute sum LABEL 'TOTAL SUM' of Tot_Free_KB  on report
compute sum LABEL 'TOTAL SUM' of Tot_Chunks   on report
compute sum LABEL 'TOTAL SUM' of Objects      on report
compute avg LABEL 'TOTAL AVERAGE' of Pct_Free on report

BREAK ON report
      ON Tablespace_Name
      ON TS_Size_MB
      ON Tot_Used_KB
      ON Tot_Free_KB
      ON Tot_Chunks
      ON Largest_chunk
      ON Objects
      ON Pct_Free

select file_name
      ,DS_Size_MB
      ,Tablespace_Name
      ,TS_Size_MB
      ,Tot_Used_KB
      ,Tot_Free_KB
      ,Tot_Chunks
      ,Largest_chunk
      ,Objects
      ,100*Tot_Free_KB/(TS_Size_MB*1024) AS Pct_Free
  from
(select Tablespace_Name        DF_TS_NAME
       ,SUM(bytes)/(1024*1024) TS_Size_MB
   from DBA_DATA_FILES 
  group by Tablespace_Name), 
(select Tablespace_Name        DF_TS_NAME1
       ,file_name              FILE_NAME
       ,SUM(bytes)/(1024*1024) DS_Size_MB
       ,COUNT(file_name)       datasets
   from DBA_DATA_FILES 
  group by Tablespace_Name, file_name), 
(select Tablespace_Name        FR_TS_NAME
       ,MAX(Blocks)*4       AS Largest_chunk
       ,COUNT(Blocks)       AS Tot_Chunks
       ,SUM(Bytes)/1024     AS Tot_Free_KB
  from DBA_FREE_SPACE
 group by Tablespace_Name),
(select tablespace_name
       ,SUM(bytes)/1024        Tot_Used_KB
       ,COUNT(segment_name)    Objects
   from DBA_SEGMENTS
  group by tablespace_name)
where Tablespace_Name = FR_TS_NAME
  and Tablespace_Name = DF_TS_NAME
  and Tablespace_Name = DF_TS_NAME1
order by tablespace_name, file_name;

                        DATASET            TABLESPACE    STORAGE     STORAGE
Dataset NAME           SIZE(MB) TS Name     SIZE(MB)     USED(KB)    FREE(KB)
______________________ ________ __________ __________ ___________ ___________
/hrp/hpr/idxxyz01.dbf        50 IDXXYZ01           50      12,044      39,152
/hrp/hpr/psindex1.dbf       850 PSINDEX1          850     665,600     204,796
/hrp/hpr/psindex2.dbf       550 PSINDEX2          550     435,200     127,996
/hrp/hpr/psindex3.dbf       350 PSINDEX3          350     276,480      81,916
/hrp/hpr/psindex4.dbf       400 PSINDEX4          400     245,760     163,836
/hrp/hpr/psindex5.dbf     1,400 PSINDEX5        1,400   1,114,120     319,476
/hrp/hpr/psindex6.dbf       450 PSINDEX6          450     337,920     122,876
/hrp/hpr/psindex7.dbf       200 PSINDEX7          200     141,180      63,616
/hrp/hpr/psindex8.dbf       200 PSINDEX8          200     133,120      71,676
/hrp/hpr/psindex9.dbf       250 PSINDEX9          250     163,840      92,156
/hrp/hpr/psrbs01.dbf      2,000 PSRBS           4,000     153,600   3,942,392
/hrp/hpr/psrbs02.dbf      2,000
/hrp/hpr/pstemp01.dbf     2,000 PSTEMP          2,000      10,320   2,037,676
/hrp/hpr/psxyz001.dbf       200 PSXYZ001          200      41,916     162,880
/hrp/hpr/psxyzaud.dbf       125 PSXYZAUD          125      40,960      87,036
                       -------- ********** ---------- ----------- -----------
TOTAL AVERAGE
TOTAL SUM                nn,nnn                nn,nnn   n,nnn,nnn  nn,nnn,nnn

What this report does is to retrieve all filenames for a tablespace, and
related statistics. Due to the break setting, he's getting the partial blank
row on tablespace "PSRBS", as that has two filenames associated with it. Each
filename for that tablespace has identical figures. But because he's <sum>ming
values, he's getting twice the values that "PSRBS" really has.

I've looked at the code and figured out a possible way around this, but it
requires an extensive rewrite of his code, and he hasn't gotten that to work
yet, not having the time to spend on it.

He was wondering if there is a simpler way to fix the situation.

Appreciative of any possible solutions,
Roy

Roy Zimmer----->OIT----->Library Stuff & Other things----->
Western Michigan University----->Kalamazoo, Michigan USA
localsystem=P3  ICBM=(%Fatal:GPS error 51)  RF=KB8UBA  AF="Hey you!" QRM!
zimmer@w... you go, there you are!