Ron Rogers wrote:
>
> Stephane ,
> Thanks for the reply.
> I tried using a join function and I got the sum(data) I wanted but it listed the
>date twice, Once for each table select. In your reply you mentioned a percentage
>calculation problem.??? I think the sums are wrong because they get summed for each
>occurance of an enter in the table.
> What do you mean by an in-line view? I tried creating views to gather the data
>needed but have problems selecting a particular retnbr , saledate range, and grouping
>by saledate across two tables.
> Thanks,
> Ron
>
> Ron,
> Looks to me like the classical percentage computation problem. Sums
> are wrong because applied to the result of the join. Compute your sums
> in an in-line view in the FROM clause.
> --
Have you ever tried to list how full your tablespaces are ? In
percentage ? This is what I meant. Basically to do this you need to sum
up the blocks on dba_data_files (to see how much has been allocated) and
on say dba_segments (to see how much is used). Double sum, and if you
join and try to compute a percentage, you get wrong numbers. This is how
it is properly done :
select x.tablespace_name, nvl(round(100 * sum(s.blocks) / x.blocks, 2),
0) PCT_FULL
from dba_segments s,
(select tablespace_name, sum(blocks) blocks <-- This is an
in-line view
from dba_data_files
group by tablespace_name) x
where x.tablespace_name = s.tablespace_name (+)
group by x.tablespace_name, x.blocks
I may have the column names wrong (and possibly the view names :-)) but
it's the idea. I think that in spirit your problem is very close.
--
Regards,
Stephane Faroult
Oriole Ltd
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Stephane Faroult
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
